Discuss "Query Parameter" in the Microsoft Access forum.

Ben answered this on the other side of the world, but my PC locks up when I try to access the thread. Ben suggested that I add Nz(tblTermsNDA.ndaSpec,"") AS Specialist. I amended the query and put "MT" as the response for the null field. The expected results of a record with the field ndaSpec as Null does not appear in the query results with MT as the specialist. Thoughts on this??



Queries - Parameter Setting in Query

--------------------------------------------------------------------------------

In a query I have developed, there is a field for Specialists. The end user has the option of selecting the particular specialist that they would like reports for by selecting the first four letters of the specialists last name and *. If the EU wishes to obtain the report for all specialists, he enters* and the enter key. The query operates as defined unless a specialists name is absent. In that case, the record is omitted from the report. I would like those records to appear when the EU selects all.

Here is the SQL for the query:
SELECT tblTermsNDA.ndaID AS ID, tblTermsNDA.ndaClient AS Client, tblTermsNDA.ndaReqDte AS RequestDate, tblTermsNDA.ndaSpec AS Specialist,
FROM tblTermsNDA
HAVING (((tblTermsNDA.ndaSpec) Like [Enter * for all or first four letters of last name and *])


What am I missing here to make it all inclusive.

You didn't say what it DID return, but I'm going to guess that the field is spaces rather than actually NULL. Try
just to make sure.

__________________
Wolffy
------------------------
Opinions expressed are my own and do not necessity reflect those of any sane person. Any code provided is intended to be an example and is provided AS IS. Rework for your specific environment may be required. Void where prohibited by law. Not valid in California. Your mileage may vary.

Thanks Wolffy. To answer your question, it did not return any results for the record which had a blank (null) Spec Field. I am pretty sure that the field was not spaces because I went to the record on the form and highlighted the control and then pressed the delete key and not the space bar, in order to test it. I will add your line of code to the sql statement and let you know what happens.


Alan

Another question regarding this. If I add the suggested code to the SQL statement as noted by Wolffy, what happens if the field is not Null. It looks like the amended code would only select from the table if the Spec field was null.

Alan

Wasn't suggesting that you change you query -- that one I supplied is a one off just to confirm the field is indeed null rather than spaces or an empty string -- a sanity check.

My mis understanding. In the interim, I have solved the issue. I took Ben's suggestion and added an Nz function to the SQL with a value if null as "MT". I further added an "Or" criteria for the Spec field which said "Is Null"
Voila!!! Results as anticipated. All Null Spec fields were returned with MT in it.
The fields can now be corrected by the Specialists.

Thanks for your time.

Alan