Time/Date problem

[Georgie]

(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

 

[KARL DEWEY]

Try this --
WHERE DateValue(DateAdd("n", -930,qrymain.adate)) = [Enter date - 3/22/2008];

 

[Georgie]

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.

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

 

[Bob Barrows [MVP]]

(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?

 

[KARL DEWEY]

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

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.

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

 

[Georgie]

I got it. Basically, for all customers the system generates an auto date and
time as soon as their info is entered in the database. The report should
(for example) for today should show only those customers that were entered
after 3:30pm yesterday and before 3:30pm today. But a customer showed up in
that report that was entered at 9am.

adate
6/26/2008 9:42:37 AM
6/26/2008 5:15:37 PM
6/26/2008 6:03:52 PM
6/27/2008 10:44:18 AM
6/27/2008 11:46:45 AM

There must have been a glich in the system that would not let this work
correctly or let me change the adate. I rebooted and it still showed but
this time I was able to delete and retype the time. After I did a Compact
and Repair everything was fine.

Thank you...

(sorry, I posted this previously in the wrong spot)

[quoted text clipped - 24 lines]

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?

 

[Bob Barrows [MVP]]

I got it. Basically, for all customers the system generates an auto
date and time as soon as their info is entered in the database. The
report should (for example) for today should show only those
customers that were entered after 3:30pm yesterday and before 3:30pm
today. But a customer showed up in that report that was entered at
9am.

adate
6/26/2008 9:42:37 AM
6/26/2008 5:15:37 PM
6/26/2008 6:03:52 PM
6/27/2008 10:44:18 AM
6/27/2008 11:46:45 AM

There must have been a glich in the system that would not let this
work correctly or let me change the adate. I rebooted and it still
showed but this time I was able to delete and retype the time. After
I did a Compact and Repair everything was fine.

I don't believe your problem is solved - can you honestly say that you are
comfortable with not knowing what happened?
Are [StartDate] and [EndDate] parameter prompts or fields in the table?

 

[Georgie]

No. You're right and I don't want it to happen again. Just didn't make much
sense that it included only the one AM record.

[StartDate] and [EndDate] are parameter prompts. (see below)

WHERE (((qrymain.adate)>DateValue([StartDate])+TimeSerial(15,30,0) And
(qrymain.adate)<DateValue([EndDate])+TimeSerial(15,30,0)));

I got it. Basically, for all customers the system generates an auto
date and time as soon as their info is entered in the database. The

[quoted text clipped - 14 lines]

showed but this time I was able to delete and retype the time. After
I did a Compact and Repair everything was fine.

I don't believe your problem is solved - can you honestly say that you are
comfortable with not knowing what happened?
Are [StartDate] and [EndDate] parameter prompts or fields in the table?

 

[Bob Barrows [MVP]]

I have no explanation except to suggest the prompts were not entered
correctly - does the report display what was entered?

No. You're right and I don't want it to happen again. Just didn't
make much sense that it included only the one AM record.

[StartDate] and [EndDate] are parameter prompts. (see below)

WHERE (((qrymain.adate)>DateValue([StartDate])+TimeSerial(15,30,0) And
(qrymain.adate)<DateValue([EndDate])+TimeSerial(15,30,0)));

I got it. Basically, for all customers the system generates an auto
date and time as soon as their info is entered in the database. The

[quoted text clipped - 14 lines]

showed but this time I was able to delete and retype the time.
After
I did a Compact and Repair everything was fine.

I don't believe your problem is solved - can you honestly say that
you are comfortable with not knowing what happened?
Are [StartDate] and [EndDate] parameter prompts or fields in the
table?

 

[Georgie via AccessMonster.com]

Could have been. No, it does not display entered dates.

I have no explanation except to suggest the prompts were not entered
correctly - does the report display what was entered?

No. You're right and I don't want it to happen again. Just didn't
make much sense that it included only the one AM record.

[quoted text clipped - 15 lines]

Are [StartDate] and [EndDate] parameter prompts or fields in the
table?

 

[Bob Barrows [MVP]]

Might be a good thing to add ...
;-)

Could have been. No, it does not display entered dates.

I have no explanation except to suggest the prompts were not entered
correctly - does the report display what was entered?

No. You're right and I don't want it to happen again. Just didn't
make much sense that it included only the one AM record.

[quoted text clipped - 15 lines]

Are [StartDate] and [EndDate] parameter prompts or fields in the
table?

 

[Georgie via AccessMonster.com]

Good point. Thank You.

Might be a good thing to add ...
;-)

Could have been. No, it does not display entered dates.

[quoted text clipped - 6 lines]

Are [StartDate] and [EndDate] parameter prompts or fields in the
table?