Jan 11 2011, 11:11 AM
Hi I have tried to research and play around with different ways to do this but to no avalil. i know it should be easy.
I have a query and a parameter form, the parameter is set to a colume called "status".
What I need the criteria to do is return all records that have a null value and omitt any data if if nothing is enterted into the parameter box. If "Tender" or any other critiera is selected then i need it to return those records and omitt the null values.
Please help as I have tried for afew hours now.
Jan 11 2011, 11:20 AM
Would something like this work?
... WHERE Status = Forms!FormName.ControlName OR IsNull(Status) = IsNull(Forms!FormName.ControlName)
Hope that helps...
Jan 11 2011, 11:36 AM
Thanks for getting back so quick. I tried to add that into the query but it dont seem work.
It changes this:
WHERE Status = Forms! ScheduleParameterForm.status OR IsNull(Status) = IsNull(ScheduleParameterForm.status)
"WHERE Status"="Forms! ScheduleParameterForm.status" Or IsNull("Status")=IsNull([ScheduleParameterForm].[status])
When I enter it into the query it then pops up a parameter box.
I did play around with but again to no avail.
Jan 11 2011, 11:38 AM
Not sure if you did it the right way. Did you?
1. Go to Design View of your query
2. Change to SQL view
3. Replace the WHERE condition with the one I posted
Hope that helps...
Jan 11 2011, 12:01 PM
sorr my mistake
just did it that way and although it shows null values when i select Tender it just adds them to the total records showing the null values and tenders and not just shows those records with tenders. It also still trys to find the control name with a pop up box.
Thanks again for your help.
Jan 11 2011, 12:18 PM
Can you post a small copy of your db with test data?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here