Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jeepin95
Starting Member
2 Posts |
 Posted - 2007-06-10 : 18:29:54
|
| I've looked through the forums and the web and haven't been able to find a solution to my problem. I'm trying to move an access database over to SQL and am having some problems. I have a function that is used as the recordsource for an access table. With the following code everything works fine, I have a place on the form where they are able to specify name, city and/or barcode to search for specific records. These values are passed as parameters to the function via the recordsource. That all works correctly and it pulls the correct recordset except for when a field is null. For example, if they don't enter anything for their search criteria I would like it to return all records in the table, but it doesn't, it only returns the records where all 3 fields are NOT NULL. I understand why, I just don't know how to fix it. I can add "OR strCity IS NULL" and it pulls all of the records, but I don't want the "OR strCity IS NULL" as a part of the WHERE unless @strCity was left blank.RETURN ( SELECT dbo.tblSalesRecord.*FROM dbo.tblSalesRecordWHERE strName = COALESCE(@strName,strName) ANDstrCity = COALESCE(@strCity,strCity) ANDstrBarcode = COALESCE(@strBarcode, strBarcode)Any help would be appreciated, Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-11 : 08:50:05
|
| strName = COALESCE(@strName,strName)If strname is null and no search criteria then this becomes null = null which is false.You can't use an equality of null so the only option is value substitution (which I guess isn't possible) or(strName = @strName or @strName is null)and (strCity = @strCity or @strCity is null)and (strBarcode = @strBarcode or @strBarcode is null)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
jeepin95
Starting Member
2 Posts |
Posted - 2007-06-11 : 19:12:04
|
| Thank you for the quick solution.....at first I didn't understand why it worked, but now I think I do and it seems so simple I'm kicking myself for not thinking about it :-)Thanks again |
|
|
|
|
|
|
|
|
Parameters in where clause