Query Parameter

[alansidman]

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.
I have tried changing the SQL for the Specialist to: nz(tblTermsNDA.ndaSpec,"") AS Specialist, but this does not seem to solve the problem either.

Thanks,
Alan

 

[Norie]

Alan

I don't quite follow, where would the specialist's name be absent from?

PS Why are you using HAVING instead of WHERE?

 

[alansidman]

The Listing of all records for all specialists excludes any record that does not have a specialist name (ie. Input error omitting to include in the record). Just because the field is empty, I still wish to see the record. I originally set up the query in the design mode and Access assigned the Having vs Where.

 

[alansidman]

I have put "MT" as the value_if_null part of the formula. I would expect that the record having a null Spec field would come back with MT in that field on the query. Am I missing something here?

Alan

 

[Norie]

Alan

Why don't you fill in the null fields with an appropriate value?

That could easily be accomplished using something like an update query.

 

[alansidman]

The fields are empty through oversight by the EU (The Specialists). If we don't get a report showing that they are empty, then we will not know to fill them. I am trying to keep this simple and allow the EU to do their own maintenance by running their automated queries and then acting on the results. Only the specialists know the proper name to put in the field. There are teams of specialists and it is not one fits all. Thanks for the suggestion.

Alan

 

[Norie]

Alan

So there's no way you could use a default value such as N/A or something?

 

[alansidman]

Norie;
Solved it. I used the Nz function in the SQL statement which created an expression in the Design view for Spec Field. I had it put "MT" in the value if null section of the formula. Additionally, in the Design view, I put the following in the Or Criteria --"Is Null". I received all the records and the ones that had a null for the Spec field came back with MT in that field for the query.

Thanks much for looking at this.

Alan