between/and parameter help

[mhmaid]

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

 

[Ofer]

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.

 

[mhmaid]

i have tried this and got correct results:

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

 

[Ofer]

But that should always return all the records, even if you enter a filter

 

[Van T. Dinh]

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 ...

 

[mhmaid]

But that should always return all the records, even if you enter a filter

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

i have tried this and got correct results:

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

thank your for help
actually, i posted this before i see your reply, which solved the problem.
thanks again.

 

[Kimberly]

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

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)

i have tried this and got correct results:

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

 

[DaveAP]

Would this work?

"Between [Enter Start Date] And [Enter End Date] Or Null 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 ...

--
HTH
Van T. Dinh
MVP (Access)

i have tried this and got correct results:

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

 

[SwEdIsH_OfFiCe_UsEr]

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

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

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

 

[John Spencer MVP]

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

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

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

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