Query Parameter

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
8,073
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Alan

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

PS Why are you using HAVING instead of WHERE?:eek:
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
Alan

So there's no way you could use a default value such as N/A or something?
 
Upvote 0
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
 
Last edited:
Upvote 0
Solution

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top