Getting 0 recordset in MS Access Query

[jt]

Hi,

I'm trying to do a sql query on a date column, like this

SELECT * FROM [PSUSA] WHERE Call_Date = #04/01/2004#

No errors in the statement, but 0 recordsets found. I have plenty dates with
this.

Also, my actual contents in Call_Date field has time as well. I just need to
query on dates not time.

Example how the data is in the field. "4/23/2004 10:59:15 AM"

Any ideas what I could be doing wrong. Us MS Access 2002 version.

Thanks,
jt

[Bob Barrows [MVP]]

jt wrote:

> Hi,
>
> I'm trying to do a sql query on a date column, like this
>
> SELECT * FROM [PSUSA] WHERE Call_Date = #04/01/2004#
>
> No errors in the statement, but 0 recordsets found. I have plenty
> dates with this.
>
> Also, my actual contents in Call_Date field has time as well. I just
> need to query on dates not time.
>
> Example how the data is in the field. "4/23/2004 10:59:15 AM"
>
> Any ideas what I could be doing wrong. Us MS Access 2002 version.
>
> Thanks,
> jt

WHERE Call_Date >= #2004/04/01# AND Call_Date < DATEADD("d",1,#2004/04/01#)

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[jt]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:uLB7uC4PEHA.3232@TK2MSFTNGP11.phx.gbl...

> jt wrote:

> > Hi,
> >
> > I'm trying to do a sql query on a date column, like this
> >
> > SELECT * FROM [PSUSA] WHERE Call_Date = #04/01/2004#
> >
> > No errors in the statement, but 0 recordsets found. I have plenty
> > dates with this.
> >
> > Also, my actual contents in Call_Date field has time as well. I just
> > need to query on dates not time.
> >
> > Example how the data is in the field. "4/23/2004 10:59:15 AM"
> >
> > Any ideas what I could be doing wrong. Us MS Access 2002 version.
> >
> > Thanks,
> > jt

>
> WHERE Call_Date >= #2004/04/01# AND Call_Date <

DATEADD("d",1,#2004/04/01#)

>
> Bob Barrows
>

Thanks! Worked great!

Now the problem is getting how to get a popup box to along the use enter the
dates as parameters, then the query to execute.

jt

[Bob Barrows [MVP]]

jt wrote:

>>
>> WHERE Call_Date >= #2004/04/01# AND Call_Date <
>> DATEADD("d",1,#2004/04/01#)
>>
>> Bob Barrows
>>

>
> Thanks! Worked great!
>
> Now the problem is getting how to get a popup box

? Why do you need a popup?
If you're convinced you need a popup, then you need to use client-side code
which is off-topic in an asp newsgroup. Look for a group with "dhtml" in its
name, or go to one of the .scripting groups: probably the jscript group.

> to along the use
> enter the dates as parameters, then the query to execute.
>

The user should only need to enter a single date.

If you are testing the query in Access (recommended), change the query to
this:

.... WHERE Call_Date >= [pDate] AND Call_Date < DATEADD("d",1,[pDate])

When you test it, Access will prompt you for the value to be used for the
pDate parameter. When you run it from asp, you will supply that value
programmatically.

When you are satisfied that the query works, save it in Access, giving it a
name such as qGetCallsByDate. In asp, do this:

<%
dim dDate, cn, rs
dDate = cDate(request.form("date"))
'create and open the connection object
set rs=server.createobject("adodb.recordset")
cn.qGetCallsByDate dDate, rs
'process the data in the recordset
%>

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[jt]

"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message
news:eU5QmU$PEHA.3660@tk2msftngp13.phx.gbl...

> jt wrote:

> >>
> >> WHERE Call_Date >= #2004/04/01# AND Call_Date <
> >> DATEADD("d",1,#2004/04/01#)
> >>
> >> Bob Barrows
> >>

> >
> > Thanks! Worked great!
> >
> > Now the problem is getting how to get a popup box

>
> ? Why do you need a popup?
> If you're convinced you need a popup, then you need to use client-side

code

> which is off-topic in an asp newsgroup. Look for a group with "dhtml" in

its

> name, or go to one of the .scripting groups: probably the jscript group.
>

> > to along the use
> > enter the dates as parameters, then the query to execute.
> >

> The user should only need to enter a single date.
>
> If you are testing the query in Access (recommended), change the query to
> this:
>
> ... WHERE Call_Date >= [pDate] AND Call_Date < DATEADD("d",1,[pDate])
>
> When you test it, Access will prompt you for the value to be used for the
> pDate parameter. When you run it from asp, you will supply that value
> programmatically.
>
> When you are satisfied that the query works, save it in Access, giving it

a

> name such as qGetCallsByDate. In asp, do this:
>
> <%
> dim dDate, cn, rs
> dDate = cDate(request.form("date"))
> 'create and open the connection object
> set rs=server.createobject("adodb.recordset")
> cn.qGetCallsByDate dDate, rs
> 'process the data in the recordset
> %>
>
> Bob Barrows
> --

That is exactly what I wish to do, to have it prompt the user to enter the
date.

Thanks for this lesson.

jt