Prev: Syntax for If then Conditional Statement
Next: Make table query combining records of two tables into a third tabl
From: Georgie on 27 Jun 2008 10:57
(sorry, I posted this previously in the wrong spot)

Good Morning, I searched around and couldn't find another thread with this
particular issue so here goes...
I have a database where customers get logged in when they come into reception.

I have name, phone#, visit#, what they came in for, and an ADATE to log the
exact date and time they came in. I have a query that counts the number of
customers from 3:30pm one day to 3:30pm the next (or whatever day I choose).
I tested this extensively while I was building it and all worked perfectly
but now that I actually have it in use I found a problem. Yesterday it
worked fine but I ran it last night to view customers that came in after 3:
30pm and it included one customer from 9:42:03am. No one else from the the
rest of the day, just that one.

Qry statement:
SELECT qryMain.[phone#], qryMain.name, qryMain.notes, qryMain.[visit#],
qryMain.adate, qryMain.reception
FROM qryMain
WHERE (((qrymain.adate)>DateValue([StartDate])+TimeSerial(15,30,0) And
(qrymain.adate)<DateValue([EndDate])+TimeSerial(15,30,0)));

Anyone have any idea what could cause that or how to fix? I don't want it to
keep happening and continually skew my numbers. Greatly appreciate any
help/ideas.
Thank You

From: KARL DEWEY on 27 Jun 2008 11:47
Try this --
WHERE DateValue(DateAdd("n", -930,qrymain.adate)) = [Enter date - 3/22/2008];

--
KARL DEWEY
Build a little - Test a little


"Georgie" wrote:

> (sorry, I posted this previously in the wrong spot)
>
> Good Morning, I searched around and couldn't find another thread with this
> particular issue so here goes...
> I have a database where customers get logged in when they come into reception.
>
> I have name, phone#, visit#, what they came in for, and an ADATE to log the
> exact date and time they came in. I have a query that counts the number of
> customers from 3:30pm one day to 3:30pm the next (or whatever day I choose).
> I tested this extensively while I was building it and all worked perfectly
> but now that I actually have it in use I found a problem. Yesterday it
> worked fine but I ran it last night to view customers that came in after 3:
> 30pm and it included one customer from 9:42:03am. No one else from the the
> rest of the day, just that one.
>
> Qry statement:
> SELECT qryMain.[phone#], qryMain.name, qryMain.notes, qryMain.[visit#],
> qryMain.adate, qryMain.reception
> FROM qryMain
> WHERE (((qrymain.adate)>DateValue([StartDate])+TimeSerial(15,30,0) And
> (qrymain.adate)<DateValue([EndDate])+TimeSerial(15,30,0)));
>
> Anyone have any idea what could cause that or how to fix? I don't want it to
> keep happening and continually skew my numbers. Greatly appreciate any
> help/ideas.
> Thank You
>
>
From: Bob Barrows [MVP] on 27 Jun 2008 12:36
Georgie wrote:
> (sorry, I posted this previously in the wrong spot)
>
> Good Morning, I searched around and couldn't find another thread
> with this particular issue so here goes...
> I have a database where customers get logged in when they come into
> reception.
>
> I have name, phone#, visit#, what they came in for, and an ADATE to
> log the exact date and time they came in. I have a query that counts
> the number of customers from 3:30pm one day to 3:30pm the next (or
> whatever day I choose). I tested this extensively while I was
> building it and all worked perfectly but now that I actually have it
> in use I found a problem. Yesterday it worked fine but I ran it last
> night to view customers that came in after 3: 30pm and it included
> one customer from 9:42:03am. No one else from the the rest of the
> day, just that one.
>
> Qry statement:
> SELECT qryMain.[phone#], qryMain.name, qryMain.notes,
> qryMain.[visit#], qryMain.adate, qryMain.reception
> FROM qryMain
> WHERE (((qrymain.adate)>DateValue([StartDate])+TimeSerial(15,30,0) And
> (qrymain.adate)<DateValue([EndDate])+TimeSerial(15,30,0)));
>
> Anyone have any idea what could cause that or how to fix? I don't
> want it to keep happening and continually skew my numbers. Greatly
> appreciate any help/ideas.
> Thank You

I'm not sure I understand the issue. Could you show us a few rows of sample
data (just the field(s) needed to illustrate the problem - no need for us to
see the whole table) so you can illustrate what rows should be included by
your query and what rows should be excluded?

--
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"


From: Georgie on 27 Jun 2008 12:36
I'm sorry, I'm really busy and trying to do this too.
Do I replace or add to my statement? I replaced it but got nothing. I added
it and got error.


KARL DEWEY wrote:
>Try this --
>WHERE DateValue(DateAdd("n", -930,qrymain.adate)) = [Enter date - 3/22/2008];
>
>> (sorry, I posted this previously in the wrong spot)
>>
>[quoted text clipped - 22 lines]
>> help/ideas.
>> Thank You

From: KARL DEWEY on 27 Jun 2008 14:10
It is a replacement for your WHERE statement.
Try it this way --
WHERE DateValue(DateAdd("n", -930,qrymain.adate)) = CVDate([Enter date -
3/22/2008]);

--
KARL DEWEY
Build a little - Test a little


"Georgie" wrote:

> I'm sorry, I'm really busy and trying to do this too.
> Do I replace or add to my statement? I replaced it but got nothing. I added
> it and got error.
>
>
> KARL DEWEY wrote:
> >Try this --
> >WHERE DateValue(DateAdd("n", -930,qrymain.adate)) = [Enter date - 3/22/2008];
> >
> >> (sorry, I posted this previously in the wrong spot)
> >>
> >[quoted text clipped - 22 lines]
> >> help/ideas.
> >> Thank You
>
>
 |  Next  |  Last
Pages: 1 2 3
Prev: Syntax for If then Conditional Statement
Next: Make table query combining records of two tables into a third tabl