#  Other Applications & Softwares  > Access Tables & Databases >  >  SQL - How to return all records if parameter is blank

## 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.
```

----------


## ThirtyTwo

> 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.
```

----------


## benoj2005

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

----------


## ThirtyTwo

is your SQL query in a string in VBA ?

----------


## benoj2005

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

----------


## ThirtyTwo

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

----------


## ThirtyTwo

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

----------


## benoj2005

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.
```

----------


## benoj2005

Surprised no one knows how to do this  :Frown:

----------


## 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.

----------


## benoj2005

> 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.
```

----------


## romperstomper

No idea I'm afraid. I don't have a lot of experience with SQL Server. (it works with Access)

----------


## Kyle123

Does this work?



```

SELECT    TMSWR.EMPREF,    TMSWR.PROCDATE,    TMSWR.REASON,    OD.DEPARTMENTFROM    Mfdata.Mfuser.OD OD,        Mfdata.Mfuser.TMSWR TMSWRWHERE     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)) ) 


```

----------


## romperstomper

I like your thinking!  :Smilie:  (but can't rep you again yet)

----------


## Kyle123

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

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

----------


## romperstomper

Nor do I, but it's still a good thought.  :Smilie:

----------


## benoj2005

> Does this work?
> 
> 
> 
> ```
> 
> SELECT    TMSWR.EMPREF,    TMSWR.PROCDATE,    TMSWR.REASON,    OD.DEPARTMENTFROM    Mfdata.Mfuser.OD OD,        Mfdata.Mfuser.TMSWR TMSWRWHERE     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!

----------


## Kyle123

What happens if you type null into the cell?

----------


## benoj2005

> What happens if you type null into the cell?



Still returns no records.

----------

