Date Range test

  • Thread starter Farman Khan via AccessMonster.com
  • Start date
F

Farman Khan via AccessMonster.com

Hi.
two of my fields in the table are date type i.e. "from_date" and
"exp_date" i want to perform a date range test using another value from the
form to check if that new value lies between these two dates or not and
retrieve all the records from the table where it lies. I m using VBA.
 
F

Farman Khan via AccessMonster.com

further i would like to mention here the SQL statements that i m using
"SELECT * FROM reservation WHERE " & Me.txtfrom & " BETWEEN from_date AND
exp_date"

and this expression give me the following error

Syntax error in number in query expression '02.07.2005 BETWEEN from_date
and exp_date'

the field type are Date/Time for both. when i change the field types to
"TEXT data type" it does not give the error message but the range test
result is not accurate.
please guide
 
A

Alex Dybenko

Hi,
you have to insert date in #mm/dd/yyyy# format, as:

"SELECT * FROM reservation WHERE #" & format(Me.txtfrom,"mm\/dd\/yyyy") & "#
BETWEEN from_date AND exp_date"
 
F

Farman Khan via AccessMonster.com

Hi Alex
thanx for replying. but it simply didnt work too. i want to know m i
bound to use american DATE format? i m not using that format at all. here
is the format i m using "dd/mm/yyyy" which i have changed from the regional
setting of my computer. when i change the from_date field from Date/Time to
Text Data type, it works but the problem is that i can not make reservation
in the previous dates of what is saved in the database. if you dont mind
may ve have online conversation inorder to solve my prolem? please. waiting
for positive response

[email protected]
 
A

Alex White MCDBA MCSE

He is right, I use the dd/mm/yyyy format for everything but when is comes to
queries Alex is right to format for US style dates. But your select
statement is wrong

should be something like

'Select * from reservation Where from_date >= #" &
format(me.txtfrom,"mm\/dd\/yyyy") & "# and exp_date <= #" &
format(me.txtfrom,"mm\/dd\/yyyy") & "#

you will need to test the above as I have not tested it at all, just for
show.

your old code

"SELECT * FROM reservation WHERE #" & format(Me.txtfrom,"mm\/dd\/yyyy") & "#
BETWEEN from_date AND exp_date"
 
V

Van T. Dinh

Try:

"SELECT * FROM reservation WHERE CDate(" & Me.txtfrom & ") " & _
" BETWEEN [from_date] AND [exp_date]"

HTH
Van T. Dinh
MVP (Access)
 
F

Farman Khan via AccessMonster.com

Thanx Alex White u were right, i applied american date format and it worked
.. thanx
 
V

Van T. Dinh

Post what you used.

I assumed you are using this in code as the SQL you posted looks like you
constructed the SQL String in VBA code.

OTOH, Alex's method or similar is what I often use also.
 
T

Tim Ferguson

"SELECT * FROM reservation WHERE " & Me.txtfrom & " BETWEEN from_date
AND exp_date"
Click to expand...

A number of problems. Can you be sure that txtFrom will _always_ refer to
a DateTime field? If you have such a lot of DateTime fields in a table,
it suggests a probably Design Problem. This perhaps should be a different
query with the txtFrom moved into the WHERE clause...

Second, using BETWEEN does not always work as planned. Dates are stored
as real numbers not integers, so #2005-01-01 21:30# falls outside the
range BETWEEN something AND #2005-01-01#. Unless you can absolutely
guarantee that nowhere have you introduced any time value data, then you
are safer using proper floating point comparisons:

"...WHERE " & Format(FromDate,strJetDt) & " <= MyField " & _
" AND MyField < " & Format(ToDate+1, strJetDt)

Note that the second boundary goes up to but not including the following
midnight.
I want to know am I bound to use american DATE format?
Click to expand...

No: but you have a very limited choice, and DMY is not one of them. This
is so that Jet developers can just write code without having to worry
about regional settings. A date like #12/04/2000# will be interpreted as
December everywhere. Beware of this but though: a date like #20/04/2000#
should raise an error but doesn't.

Essentially, the choice is

strJetDt = "\#mm\/dd\/yyyy\#" ' USAian format

or

strJetDt = "\#yyyy\-mm\-dd\#" ' ISO 8601 standard

If, like me, you prefer to think of your code adhering to sensible
international standards, you can reliable use the second version. Note
that in either form, the separators can be critical.

Hope that helps


Tim F
 
Top