Select query not allowing blank entry to display all departments

[torf66]

I have a database that has 600 records in it. The user
has not populated anything into the Department field. I need to write a select query to allow the user to enter a Department at the time of running the query. So I used in the Criteria Like [Enter] & "*". But when I run the select query and enter nothing at the prompt to 'Enter a Deptartment' none of the 600 records show. I need to have the 600 records show since the user did not enter a specific Department. What do I need to put in my Criteria to make this work? This Department field is data type is Text, Field Size is 25 long, not indexed, Unicode Compression is set to Yes, the lookup Display Control is Text Box. If you need more info on the field let me know.
Email me at ttorfin@dakcl.com with any suggestions, would be most appreciated.
Thanks,
Todd

 

[Tranman]

Todd,
Try Is Null in the criteria.

When you say "Like [Enter] & "*", you are asking the query to deliver up the rows that already contain the value you enter.

Good Luck,
Tranman

 

[torf66]

This is what I have in my Database

ID Location Dept
Rec 1 1M01 1ST FLOOR
Rec 2 1M02 1ST FLOOR 222
Rec 3 1M03 1ST FLOOR 444

When I run my Select query using the Like [Enter a Department] & "*" Or Is Null
I enter at the prompt for Department I enter a 4 then I get record 1 and 3. I only want record 3. If I enter nothing at the prompt I do get all records. I need to be able to
only get the record containing a 4 which would be Rec 3 only when I enter a 4 at the Department prompt. How can I get it so the prompt will display all records if nothing is entered at the prompt or just the ones that have 4 or 2 or whatever entry I key in? If I enter a 5 then from my example above I would get nothing.

 

[Golom]

I think that you need to switch out your WHERE conditions depending on the value entered by the user. Something like this

   WHERE IIF (Len(Trim([Enter a Department])) = 0, TRUE,
              [Dept] Like [Enter a Department] & "*" Or [Dept] Is Null)

 

[torf66]

Where would I enter your suggestion in my Select Query, how do I get this into the query?
WHERE IIF (Len(Trim([Enter a Department])) = 0, TRUE,
[Dept] Like [Enter a Department] & "*" Or [Dept] Is Null)