parameter query to show all records

[tizwaz]

I want to do a parameter query with paramaters on several fields but if any or the parameters are left blank that all records for that field are returned. works fine on most fields using [enter whatever] or like * but have a date field between [enter start] and [enter end] and can't get that to work using same principle. Just gives all records even though paramaters are filtering other fields.

 

[dhookom]

You can try something that uses the "criteria'd" field name like:
Between Nz([Enter Start Date],[SaleDate]) AND Nz([Enter End Date], [SaleDate])

BTW: you should consider moving beyond the use of parameter queries. They are generally not user friendly, can't use combo boxes, don't check integrity of values, etc. References to controls on forms provides much more functionality.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[tizwaz]

Thanks that works on the date field but I'm now having trouble with all the other fields. Can't get [enter parameter] or like * to work anymore. What would I need to put on the other fields? I have 2 text fields, 1 number field which I want a between parameter on and 1 date field also with a between parameter on it.

I take your point about making a search form but I have quite a few of these type of queries to do on different tables and not much time so really looking for quickest solution. May have to go down that route if can't get this to work though

 

[PHV]

Have you tried something like this ?
Like Nz([enter parameter], "*")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[tizwaz]

Thanks again - that seems to work fine. Just one thing - the field ant_date_des has a lot of blank entries and I would like to pick them up but only if I didn't put dates in the parameter fields. I have added or is null which brings them up whether something entered in parameter field or not but is it possible to restrict it?

My SQL is as follows if this helps

SELECT [qry GOM not destroyed inc theme number].CONDITION, [qry GOM not destroyed inc theme number].[FILE NUMBER], [qry GOM not destroyed inc theme number].WHO, [qry GOM not destroyed inc theme number].[Theme No], [qry GOM not destroyed inc theme number].ANT_DATE_DES, [qry GOM not destroyed inc theme number].[PRESENT OWNER & DAT], [qry GOM not destroyed inc theme number].[LONG:FILE TITLE], [qry GOM not destroyed inc theme number].[SECTION/BRANCH], [qry GOM not destroyed inc theme number].[LOCATION OF FILE IF]
FROM [qry GOM not destroyed inc theme number]
WHERE ((([qry GOM not destroyed inc theme number].CONDITION) Like nz([enter in use or put away],"*")) AND (([qry GOM not destroyed inc theme number].WHO) Like nz([enter go code eg gomt],"*")) AND (([qry GOM not destroyed inc theme number].[Theme No]) Between nz([enter start of range of theme nos],[theme no]) And nz([enter end of range of theme nos],[theme no])) AND (([qry GOM not destroyed inc theme number].ANT_DATE_DES) Between nz([enter earliest destruction date],[ant_date_des]) And nz([enter latest destruction date],[ant_date_des]) Or ([qry GOM not destroyed inc theme number].ANT_DATE_DES) Is Null));