Date Range test

  • Thread starter Thread starter Farman Khan via AccessMonster.com
  • Start date 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.
 
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
 
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"
 
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

(e-mail address removed)
 
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"
 
Try:

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

HTH
Van T. Dinh
MVP (Access)
 
Thanx Alex White u were right, i applied american date format and it worked
.. thanx
 
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.
 
"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
 
Back
Top