How do I query for a date range?

[Kingster]

I have an access table called Orders. When an order is placed the date is automatically entered. I have it set to ShortDate. When I run a query on Orders for one date, it comes back with the date and the time. I don't care about the time. Yet, it always puts the time in. The field shows as Tue Apr 19 00:00:00 PDT 2005 even though I want just 4/19/2005 so I can query based on that format. Not the date format.

What I want is to be able to query the table for a "date range" using two online form boxes. I'm using Active Server Pages in VBScript. I seem to have to use a LIKE command to get a record to show. That's fine when I'm asking for one date, but how do I access a set of records using "two" dates, since I have to use the LIKE command to get any date. How can I have the date just be the date and not the time so I don't have to use the LIKE command.

How is this done?

Here's the code I have for one date:

<%
Dim rstest
Dim rstest_numRows

Set rstest = Server.CreateObject("ADODB.Recordset")
rstest.ActiveConnection = MM_Romantic_STRING
rstest.Source = "SELECT * FROM Orders WHERE OrderDate LIKE '%" + Replace(rstest__MMColParam, "'", "''") + "%'"
rstest.CursorType = 0
rstest.CursorLocation = 2
rstest.LockType = 1
rstest.Open()

rstest_numRows = 0
%>

Thanks in advance for any insight...

Kingster

 

[John Vinson]

I have an access table called Orders. When an order is placed the date is automatically entered. I have it set to ShortDate. When I run a query on Orders for one date, it comes back with the date and the time. I don't care about the time. Yet, it always puts the time in. The field shows as Tue Apr 19 00:00:00 PDT 2005 even though I want just 4/19/2005 so I can query based on that format. Not the date format.

An Access Date/Time field is stored internally as a Double Float
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. As such it ALWAYS has a time component, midnight if
it's entered as a pure date.

You can change the format of your display from its current ddd mmm dd
hh:nn:ss yyyy (or whatever it is) to something like Short Date or
m/d/yyyy. This doesn't change what's stored but it will display the
value as you wish.

What I want is to be able to query the table for a "date range" using two online form boxes. I'm using Active Server Pages in VBScript. I seem to have to use a LIKE command to get a record to show. That's fine when I'm asking for one date, but how do I access a set of records using "two" dates, since I have to use the LIKE command to get any date. How can I have the date just be the date and not the time so I don't have to use the LIKE command.

How is this done?

Not with LIKE but with BETWEEN:

Here's the code I have for one date:
<%
Dim rstest
Dim rstest_numRows

Set rstest = Server.CreateObject("ADODB.Recordset")
rstest.ActiveConnection = MM_Romantic_STRING
rstest.Source = "SELECT * FROM Orders WHERE OrderDate BETWEEN #" + rstest__MMColParam + "# AND #" + rstest__MMCol2Param & "#"

where MMColParam is the "from" date, in mm/dd/yyyy format (you might
need to explicitly use the Format() function to cast it as such) and
MMCol2Param is the "to" date.


John W. Vinson[MVP]

 

[John Vinson]

John,

I think this is over my head. Yikes...I get all kinds of mismatch errors. How much would you charge to write the proper code?

It's over mine too - Access I can do pretty well, but I've never
gotten into ASP coding. I'd suggest you repost the problem, mentioning
ASP in the subject of the message.

John W. Vinson[MVP]

 

[Kingster]

John...

I'm still interested in some Access questions. Maybe you can help. If
you're interested....contact me at (e-mail address removed)

Thanks,

King

 

[esrt]

wewe

-----Original Message-----
From: John Vinson [mailto:jvinson@STOP_SPAM.WysardOfInfo.com]
Posted At: Wednesday, April 20, 2005 8:13 AM
Posted To: microsoft.public.access
Conversation: How do I query for a date range?
Subject: Re: How do I query for a date range?

I have an access table called Orders. When an order is placed the date is automatically entered. I have it set to ShortDate. When I run a query on Orders for one date, it comes back with the date and the time. I don't care about the time. Yet, it always puts the time in. The field shows as Tue Apr 19 00:00:00 PDT 2005 even though I want just 4/19/2005 so I can query based on that format. Not the date format.

An Access Date/Time field is stored internally as a Double Float
number, a count of days and fractions of a day (times) since midnight,
December 30, 1899. As such it ALWAYS has a time component, midnight if
it's entered as a pure date.

You can change the format of your display from its current ddd mmm dd
hh:nn:ss yyyy (or whatever it is) to something like Short Date or
m/d/yyyy. This doesn't change what's stored but it will display the
value as you wish.

What I want is to be able to query the table for a "date range" using two online form boxes. I'm using Active Server Pages in VBScript. I seem to have to use a LIKE command to get a record to show. That's fine when I'm asking for one date, but how do I access a set of records using "two" dates, since I have to use the LIKE command to get any date. How can I have the date just be the date and not the time so I don't have to use the LIKE command.

How is this done?

Not with LIKE but with BETWEEN:

Here's the code I have for one date:
<%
Dim rstest
Dim rstest_numRows

Set rstest = Server.CreateObject("ADODB.Recordset")
rstest.ActiveConnection = MM_Romantic_STRING
rstest.Source = "SELECT * FROM Orders WHERE OrderDate BETWEEN #" + rstest__MMColParam + "# AND #" + rstest__MMCol2Param & "#"

where MMColParam is the "from" date, in mm/dd/yyyy format (you might
need to explicitly use the Format() function to cast it as such) and
MMCol2Param is the "to" date.


John W. Vinson[MVP]

 

[esrt]

wer
-----Original Message-----
From: Kingster [mailto:[email protected]]
Posted At: Wednesday, April 20, 2005 8:38 AM
Posted To: microsoft.public.access
Conversation: How do I query for a date range?
Subject: Re: How do I query for a date range?

This is a multi-part message in MIME format.

------=_NextPart_000_015F_01C5451B.76E21A10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

John,

I think this is over my head. Yikes...I get all kinds of mismatch =
errors. How much would you charge to write the proper code?

the website is www.romantic-adventures.com

Thanks...

King


I have an access table called Orders. When an order is placed the =
date is automatically entered. I have it set to ShortDate. When I run a =
query on Orders for one date, it comes back with the date and the time. =
I don't care about the time. Yet, it always puts the time in. The =
field shows as Tue Apr 19 00:00:00 PDT 2005 even though I want just =
4/19/2005 so I can query based on that format. Not the date format. =20

What I want is to be able to query the table for a "date range" using =
two online form boxes. I'm using Active Server Pages in VBScript. I =
seem to have to use a LIKE command to get a record to show. That's fine =
when I'm asking for one date, but how do I access a set of records using =
"two" dates, since I have to use the LIKE command to get any date. How =
can I have the date just be the date and not the time so I don't have to =
use the LIKE command.

How is this done?

Here's the code I have for one date:

<%
Dim rstest
Dim rstest_numRows

Set rstest =3D Server.CreateObject("ADODB.Recordset")
rstest.ActiveConnection =3D MM_Romantic_STRING
rstest.Source =3D "SELECT * FROM Orders WHERE OrderDate LIKE '%" + =
Replace(rstest__MMColParam, "'", "''") + "%'"
rstest.CursorType =3D 0
rstest.CursorLocation =3D 2
rstest.LockType =3D 1
rstest.Open()

rstest_numRows =3D 0
%>

Thanks in advance for any insight...

Kingster
------=_NextPart_000_015F_01C5451B.76E21A10
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 6.00.2900.2180" name=3DGENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=3D#ffffff>
<DIV><FONT face=3DArial size=3D2>John,</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>I think this is over my head.&nbsp; =
Yikes...I get=20
all kinds of mismatch errors.&nbsp; How much would you charge to write =
the=20
proper code? </FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>the website is <A=20
href=3D"http://www.romantic-adventures.com">www.romantic-adventures.com</=
A></FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>Thanks...</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2>King</FONT></DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<DIV><FONT face=3DArial size=3D2></FONT>&nbsp;</DIV>
<BLOCKQUOTE dir=3Dltr=20
style=3D"PADDING-RIGHT: 0px; PADDING-LEFT: 5px; MARGIN-LEFT: 5px; =
BORDER-LEFT: #000000 2px solid; MARGIN-RIGHT: 0px">
<DIV>"Kingster" &lt;<A=20
=
href=3D"mailto:[email protected]">[email protected]</A>&gt;=
wrote=20
in message <A=20
=
href=3D"@TK2MSFTNGP09.phx.gbl</A>...</DIV>
<DIV><FONT face=3DArial size=3D2>I have an access table called =
Orders.&nbsp; When=20
an order is placed the date is automatically entered.&nbsp; I have it =
set to=20
ShortDate. When I run a query on Orders for one date, it comes back =
with the=20
date and the time.&nbsp; I don't care about the time.&nbsp; Yet, it =
always=20
puts the time in.&nbsp; The&nbsp;field shows as&nbsp;Tue Apr 19 =
00:00:00 PDT=20
2005 even though I want just 4/19/2005 so I can query based on that =
format.=20
Not the date format.&nbsp; <BR><BR>What I want is to be able to query =
the=20
table for a "date range" using two online form boxes.&nbsp; I'm using =
Active=20
Server Pages in VBScript.&nbsp; I seem to have to use a LIKE command =
to get a=20
record to show.&nbsp; That's fine when I'm asking for one date, but =
how do I=20
access a set of records using "two" dates, since I have to use the =
LIKE=20
command to get any date.&nbsp; How can I have the date just be the =
date and=20
not the time so I don't have to use the LIKE =
command.</FONT></DIV><FONT=20
face=3DArial size=3D2>
<DIV><BR>How is this done?<BR><BR>Here's the code I have for one =
date:</DIV>
<DIV>&nbsp;</DIV>
<DIV>&lt;%<BR>Dim rstest<BR>Dim rstest_numRows</DIV>
<DIV>&nbsp;</DIV>
<DIV>Set rstest =3D=20
Server.CreateObject("ADODB.Recordset")<BR>rstest.ActiveConnection =3D=20
MM_Romantic_STRING<BR>rstest.Source =3D "SELECT *&nbsp; FROM =
Orders&nbsp; WHERE=20
OrderDate LIKE '%" + Replace(rstest__MMColParam, "'", "''") +=20
"%'"<BR>rstest.CursorType =3D 0<BR>rstest.CursorLocation =3D =
2<BR>rstest.LockType=20
=3D 1<BR>rstest.Open()</DIV>
<DIV>&nbsp;</DIV>
<DIV>rstest_numRows =3D 0<BR>%&gt;</DIV>
<DIV>&nbsp;</DIV>
<DIV>Thanks in advance for any insight...</DIV>
<DIV>&nbsp;</DIV>
<DIV>Kingster</FONT></DIV></BLOCKQUOTE></BODY></HTML>

------=_NextPart_000_015F_01C5451B.76E21A10--