between/and parameter help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi all
i am trying to creat a qry with parameter for a specific period of "Travel"
Dates
like this
between[Enter Start Date]and[Enter End Date]
but i want it to show all records if i leave this blank
how can this be done" now i am getting no records if i leave blank
 
Try this

Where FieldName Between IIf([Enter Start Date] Is Null,#1/01/1800#,[Enter
Start Date]) And IIf([Enter End Date] Is Null,#1/01/2300#,[Enter End Date])

If no value was enter, either start date or end date, it will put a new date
range that will include all the dates.
That way the user can insert just a start date, or just an end date.
 
i have tried this and got correct results:

"Between [Enter Start Date] And [Enter End Date] Or Like "*"
 
In addition to what Ofer said, please note that "Like" is a STRING
comparison operator and you use is with data values.

While Access makes it possible to use operators with mis-typed values, your
database will likely to suffer inefficiencies for this sort of type-casting.
IMHO, this should be avoided ...
 
Ofer said:
But that should always return all the records, even if you enter a filter

--
\\// Live Long and Prosper \\//
BS"D


mhmaid said:
i have tried this and got correct results:

"Between [Enter Start Date] And [Enter End Date] Or Like "*"
Click to expand...
Click to expand...

thank your for help
actually, i posted this before i see your reply, which solved the problem.
thanks again.
 
Wow! Thank you for this post! It works! FYI for other computer illiterates
like myself, the formula is either: "between [enter start date] and [enter
end date]" without the "like "*"" part! Guess what... I am cute!
--
Cute Princess Kimberly


Van T. Dinh said:
In addition to what Ofer said, please note that "Like" is a STRING
comparison operator and you use is with data values.

While Access makes it possible to use operators with mis-typed values, your
database will likely to suffer inefficiencies for this sort of type-casting.
IMHO, this should be avoided ...

--
HTH
Van T. Dinh
MVP (Access)



mhmaid said:
i have tried this and got correct results:

"Between [Enter Start Date] And [Enter End Date] Or Like "*"
Click to expand...
Click to expand...
 
Would this work?

"Between [Enter Start Date] And [Enter End Date] Or Null Like "*"

Van T. Dinh said:
In addition to what Ofer said, please note that "Like" is a STRING
comparison operator and you use is with data values.

While Access makes it possible to use operators with mis-typed values, your
database will likely to suffer inefficiencies for this sort of type-casting.
IMHO, this should be avoided ...

--
HTH
Van T. Dinh
MVP (Access)



mhmaid said:
i have tried this and got correct results:

"Between [Enter Start Date] And [Enter End Date] Or Like "*"
Click to expand...
Click to expand...
 
It doesn't seem to work on Access 2000. Can someone please give me something
that will work for me?

Ofer said:
Try this

Where FieldName Between IIf([Enter Start Date] Is Null,#1/01/1800#,[Enter
Start Date]) And IIf([Enter End Date] Is Null,#1/01/2300#,[Enter End Date])

If no value was enter, either start date or end date, it will put a new date
range that will include all the dates.
That way the user can insert just a start date, or just an end date.

--
\\// Live Long and Prosper \\//
BS"D


mhmaid said:
hi all
i am trying to creat a qry with parameter for a specific period of "Travel"
Dates
like this
between[Enter Start Date]and[Enter End Date]
but i want it to show all records if i leave this blank
how can this be done" now i am getting no records if i leave blank
Click to expand...
Click to expand...
 
Try the following as criteria

FieldName Between NZ([Enter Start Date] Is Null,#1800-01-01#)
AND Nz([Enter End Date],#2300-12-31#)

If that doesn't work, then post back with an explanation of what you mean by
"doesn't work". You get the wrong results, you get a syntax error, you get
some other error, etc.

You might be experiencing problems based on your date format. See
International Dates in Access at:
http://allenbrowne.com/ser-36.html

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

SwEdIsH_OfFiCe_UsEr said:
It doesn't seem to work on Access 2000. Can someone please give me something
that will work for me?

Ofer said:
Try this

Where FieldName Between IIf([Enter Start Date] Is Null,#1/01/1800#,[Enter
Start Date]) And IIf([Enter End Date] Is Null,#1/01/2300#,[Enter End Date])

If no value was enter, either start date or end date, it will put a new date
range that will include all the dates.
That way the user can insert just a start date, or just an end date.

--
\\// Live Long and Prosper \\//
BS"D


mhmaid said:
hi all
i am trying to creat a qry with parameter for a specific period of "Travel"
Dates
like this
between[Enter Start Date]and[Enter End Date]
but i want it to show all records if i leave this blank
how can this be done" now i am getting no records if i leave blank
Click to expand...
Click to expand...
Click to expand...
 
Back
Top