order query please help

[fido77777]

i have a query that looks for orders in many cases

first i use this statement in order date column criteria

between [enter start date] and [enter end date]

this allow user to find orders between any date range that he want

but there is more cases:

1-if he didn't enter the start date he should get all orders till the date he choose in the end date field

2-if he he didn't enter the end date he should get all orders after the date he choose in the start date field

3-if he didn't put both start and end dates he should get all order for all dates

any solution to solve all these cases will be great
but please if any one can get it done by updating my statement which i put in query criteria will be more than great

i use northwind as sample database

thank you

 

[DCrake]

I assume you are using parameters in the query. What you will have to do is to remove these and have to fields on a form and get the user to populate these dates

Then using logic, such as

If Not IsNull(StartDate) And Not IsNull(EndDate) Then
  'Everything between the two dates
ElseIf IsNull(StartDate) And Not IsNull(EndDate) Then
  'Everything upto the end date
ElseIf Not IsNull(StartDate) And IsNull(EndDate) Then
  'Everything on or after the start date
ElseIf IsNull(StartDate) And IsNull(EndDate) Then
  'all records
End If

Then apply this criteria to your runquery command line

 

[fido77777]

thanks
but i wonder if i can solve it like some other case
for example:
in firstname field if i use a parameter query in criteria using the following

like [enter first name]&"*"

that means if i didn't put first name it will just get all the names for me

i want to do something like that in the order date
something like

between (like [enter start date]&"*") and (like [enter end date]&"*")

there is something worng or missing in the above line
and i don't know what !!
i wish you can help

 

[DCrake]

In your query use the folowing syntax

Between Nz([Start Date],#01/01/2006#) And Nz([End Date],Date())

Where #01/01/2006# is the earliest date in your table

 

[fido77777]

yes
now it works good
using Nz is a smart way to handle the parameter the user leave empty
thank you