one or all

[jwf]

I have a query that asks the end user for an employee number IE: = [employee no] this works great and pulls up all the records for the employee number entered. But what I also want it to do is if no employee number is entered I want the query to pull up records for ALL the employee numbers. Help

I think my original statement was misleading. What I am trying to do is give the end user the option of using this report query to print the records from just one emplyee number IE =[Employee Number] where I have records for say 10 employee's (1 through 10) and the end user would enter a number 1 through 10 and the query will pull up the records for that employee number. or if the end user leaves the query requst for an employee number blank (just presses the enter key without entering a number) I want the query to return records for all the employee numbers 1 through 10. There by allowing the end user to print a report for just one employee or a report that includes all the employee's
Thanks again for your help

 

[Alansidman]

If you are using a parameter requesting the criteria, then when prompted, type:

Like *

 

[Brianwarnock]

The better method is to add a second criteria of Or [employee no] is null

Like involves a function call and thus is less efficient , also Like will not select null fields, I'm sure you would like to know if there are any.

Brian

 

[jwf]

Thanks for the replies. I think my original statement was misleading. What I am trying to do is give the end user the option of using this report query to print the records from just one emplyee number IE =[Employee Number] where I have records for say 10 employee's (1 through 10) and the end user would enter a number 1 through 10 and the query will pull up the records for that employee number. or if the end user leaves the query requst for an employee number blank (just presses the enter key without entering a number) I want the query to return records for all the employee numbers 1 through 10. There by allowing the end user to print a report for just one employee or a report that includes all the employee's
Thanks again for your help

 

[JHB]

Try this, change the table name "Eml" to your table name and the field name"IE" to your field name, (3 * change).

PARAMETERS [Employee Number] Short;
SELECT IE
FROM Eml
WHERE (((IIf(IsNumeric([Employee Number]),[IE]=[Employee Number],[IE] Is Not Null))<>False));

 

[Brianwarnock]

There was no misunderstanding, you might like to read a full explanation of what I was suggesting in this thread.

http://www.access-programmers.co.uk/forums/showthread.php?t=239118

Brian

 

[jwf]

My situation is a little different I think. My field called EmpNo is the key field in my table and so will never be null so I am not looking for records where the EmpNo field is null. In my query under the field EmpNo in the criteria I have [EmpNo]. When the query is run, it askes for an EmpNo, if I enter in a number the query returns the record for that number. This is great. It is the first option I want. However, if I dont enter a number the query returns back no records. Not great, not what I want. What I want is for the query to return back all the records in the table if I do not enter a number when prompted.

 

[JHB]

Is the below query not what you are asking for?

 

[Brianwarnock]

My situation is a little different I think. My field called EmpNo is the key field in my table and so will never be null so I am not looking for records where the EmpNo field is null. In my query under the field EmpNo in the criteria I have [EmpNo]. When the query is run, it askes for an EmpNo, if I enter in a number the query returns the record for that number. This is great. It is the first option I want. However, if I dont enter a number the query returns back no records. Not great, not what I want. What I want is for the query to return back all the records in the table if I do not enter a number when prompted.

You obviously did not follow the link.
I am not testing your field. F or null but the parameter, please read the link all the way through.

Brian

 

[jwf]

Thanks, I finally understand it. I was trying to make it way more complicated then it was. Thanks again.