Hello,
I also used date comparison in Access, and expirienced the same. You have to
use date comparison in queries like this:
SELECT *
FROM Reference
WHERE Archive_Date > #CreateODBCDateTime(date1)# AND Archive_Date <
#CreateODBCDateTime(date2)#
That should work.
Regards,
Johan
--> Ben said this before:
You should use:
WHERE creationdate >= #CreateODBCDate(dateA)# AND creationdate <=
#CreateODBCDate(dateB)#. You should never use dates directly (in a WHERE
clause or any other part of a SQL statement) without using one of
CreateODBCDate() CreateODBCTime() or CreateODBCDateTime(). That's the only
safe way to use dates consistently.
Also, you can use the BETWEEN clause.
--- Ben
-----Original Message-----
From: Jason Glaun [mailto:[EMAIL PROTECTED]]
Sent: donderdag 27 juli 2000 10:21
To: [EMAIL PROTECTED]
Subject: Dates!!!!! (code)
This is a multi-part message in MIME format.
------=_NextPart_000_0026_01BFF7AB.F1534DE0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
If you have a chance could you just have a glance at this for me? =
thanks.
I am having trouble with something that I can't seem to get righ and was
wondering if you had any advice for me. It's a SQL problem. I'm =
trying
to get the dates from the database which lie between date_A and date_B =
but
it won't give me any!!!
here is my query..
Version 1
<CFQUERY name=3D"get_all_reference" datasource=3D"Jason_K-H_Archive">
SELECT *
FROM Reference
WHERE Archive_Date BETWEEN #variables.date_A# AND #variables.date_B#
</CFQUERY>
Version 2
<CFQUERY name=3D"get_all_reference" datasource=3D"Jason_K-H_Archive">
SELECT *
FROM Reference
WHERE Archive_Date > #variables.date_A# AND Archive_Date <
#variables.date_B#
</CFQUERY>
it just won't seem to work! I get 2 errors depending on '' around the
variables.
error 1(without #variable# )
[Microsoft][ODBC Microsoft Access 97 Driver]Division by zero (null)
error2 (with '#variable#' )
Data type mismatch in criteria expression
can't I do a greater or lessthan calculation on a date?
Thanks.
------=_NextPart_000_0026_01BFF7AB.F1534DE0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=3DContent-Type content=3D"text/html; =
charset=3Diso-8859-1">
<META content=3D"MSHTML 5.50.4134.600" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff><FONT face=3DArial size=3D2>
<DIV><BR><FONT face=3D"Times New Roman" size=3D3>If you have a chance =
could you just=20
have a glance at this for me? thanks.<BR>I am having trouble =
with=20
something that I can't seem to get righ and was<BR>wondering if you had =
any=20
advice for me. It's a SQL problem. I'm =
trying<BR>to get=20
the dates from the database which lie between date_A and date_B =
but<BR>it won't=20
give me any!!!<BR><BR>here is my query..<BR><BR>Version 1<BR><CFQUERY =
name=3D"get_all_reference"=20
datasource=3D"Jason_K-H_Archive"><BR>SELECT &nb=
sp;=20
*<BR>FROM Reference<BR>WHERE=20
Archive_Date BETWEEN #variables.date_A# AND=20
#variables.date_B#<BR></CFQUERY><BR><BR><BR>Version =
2<BR><CFQUERY=20
name=3D"get_all_reference"=20
datasource=3D"Jason_K-H_Archive"><BR>SELECT &nb=
sp;=20
*<BR>FROM Reference<BR>WHERE=20
Archive_Date > #variables.date_A# AND Archive_Date=20
<<BR>#variables.date_B#<BR></CFQUERY><BR><BR>it just won't seem =
to=20
work! I get 2 errors depending on '' around =
the<BR>variables.<BR><BR>error=20
1(without #variable# )<BR>[Microsoft][ODBC Microsoft Access 97=20
Driver]Division by zero (null)<BR><BR><BR>error2 (with =
'#variable#' )<BR>Data type mismatch in criteria=20
expression<BR><BR>can't I do a greater or lessthan calculation on a=20
date?</FONT><BR></DIV>
<DIV> </DIV>
<DIV>Thanks.</DIV></FONT></BODY></HTML>
------=_NextPart_000_0026_01BFF7AB.F1534DE0--
----------------------------------------------------------------------------
--
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or
send a message to [EMAIL PROTECTED] with 'unsubscribe' in
the body.
------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
