SQL - How to return all records if parameter is blank

I want TMSWR.EMPREF = ? (Which is linked to a cell in Excel) parameter to be ignored if the cell is left blank. Is this possible?

Please Login or Register  to view this content.

Originally Posted by benoj2005

I want TMSWR.EMPREF = ? (Which is linked to a cell in Excel) parameter to be ignored if the cell is left blank. Is this possible?

Please Login or Register  to view this content.

I suggest putting that condition into a string, if it's blank it won't add anything to your string that holding the main SQL query, but if it's not blank it will add it to the end of your query, something like this

Please Login or Register  to view this content.

That makes sense, I've tried this:

Please Login or Register  to view this content.

but I get these two error messages, any ideas?

Errors.jpg

is your SQL query in a string in VBA ?

Oh no, it's in the connection properties command text. Using Microsoft query via ODBC. If that's the right terminology

what about creating two procedure, one for when you want the additional condition, and one without ?

Please Login or Register  to view this content.

I'm not that familiar with the addon reference library you're using

so you're just replacing your current sql query with the above IF statement, and creating the two new subs() somewhere else in the module

The code makes sense, apart from

Please Login or Register  to view this content.

needs to be

Please Login or Register  to view this content.

because ? is a parameter in an excel sheet, if they don't specify an EMPREF than it needs to return all EMPREF's.

Still getting the same errors using this:

connection properties.jpg


and code is here for reference

Please Login or Register  to view this content.

Surprised no one knows how to do this

Originally Posted by romperstomper

Try using something like:

AND ((TMSWR.EMPREF = ?) OR (' '=? & ' '))

and make sure the additional parameter is linked to the same cell as the prior one.

I like your thinking! So I've added that and linked both paramerter 3 and 4 to the same cell, but, now I get an error message about "string data, right truncation". See this screen shot > error.jpg

Please Login or Register  to view this content.

Does this work?

PHP Code: 

SELECT
    TMSWR.EMPREF,
    TMSWR.PROCDATE,
    TMSWR.REASON,
    OD.DEPARTMENT

FROM    Mfdata.Mfuser.OD OD,
        Mfdata.Mfuser.TMSWR TMSWR

WHERE 
    OD.EMPREF = TMSWR.EMPREF 
    AND ((OD.DEPARTMENT IN (05, 10, 11, 14)) AND (TMSWR.PROCDATE BETWEEN ? AND ?) AND (TMSWR.REASON > ''))
    AND ((TMSWR.EMPREF = COALESCE(?,TMSWR.EMPREF)) ) 


Thanks, but I've no idea if it will work in Excel

Also the performance might be a bit pants since the query isn't sargable, but I don't know an easier way of doing it

Originally Posted by Kyle123

Does this work?

PHP Code: 

SELECT
    TMSWR.EMPREF,
    TMSWR.PROCDATE,
    TMSWR.REASON,
    OD.DEPARTMENT

FROM    Mfdata.Mfuser.OD OD,
        Mfdata.Mfuser.TMSWR TMSWR

WHERE 
    OD.EMPREF = TMSWR.EMPREF 
    AND ((OD.DEPARTMENT IN (05, 10, 11, 14)) AND (TMSWR.PROCDATE BETWEEN ? AND ?) AND (TMSWR.REASON > ''))
    AND ((TMSWR.EMPREF = COALESCE(?,TMSWR.EMPREF)) ) 


No error messages is the good news, works if I specify a value in the linked cell to the parameter for this

Please Login or Register  to view this content.

, however, if no value is specified in the cell then no results are returned. It needs to ignore this parameter and return all results in this case. I feel like we're getting closer, but not quite there!

What happens if you type null into the cell?

Originally Posted by Kyle123

What happens if you type null into the cell?

Still returns no records.