Some having time and others not having time shouldn't be an issue.
You're using the DateValue function: that'll strip time from all of the
values. It doesn't matter to the function whether or not the value had a
time before the function was applied.
Null values shouldn't be an issue either, but if it were, I certainly
wouldn't expect that error message.
Is that the entire query, or have you simplified it for posting purposes?
Sometimes I've found it's necessary to put parentheses around the clause
when using Between:
SELECT *
FROM [Distinct Waitlist Desired]
WHERE (DateValue([Date Worked]) Between
CDate([Begin Date:]) and CDate([End Date:]))
You could also try
SELECT *
FROM [Distinct Waitlist Desired]
WHERE DateValue([Date Worked]) >= CDate([Begin Date:])
AND DateValue([Date Worked]) <= CDate([End Date:]))
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Bunky said:
In examining my data, I see there are some entries with no time at all
and
some with times. I am betting this is the cause of my problem.
1. How do I update all entries to a have a time?
2. Is this or could this be my problem since not everything is being
pulled?
Any help is appreciated!
Thanks
:
That should work fine, but see whether this works any better:
SELECT *
FROM [Distinct Waitlist Desired]
WHERE DateValue([Date Worked]) Between
CDate([Begin Date:]) and CDate([End Date:])
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Ken,
SELECT *
FROM [Distinct Waitlist Desired]
WHERE DateValue([Date Worked]) Between
[Begin Date:] and [End Date:]; I entered 10/27/07 for the Begin
Date
and
12/03/07 for the End Date. I then received the message that states
the
expression is typed incorrectly or is too large to be evaluated.
Normally
when I get this message I have fat-fingered a date but this time I
have
not.
I have also tried 10/27/2007 and 12/03/2007 with the same result.
The
field
is defined as Date/Time with no format listed.
Ideas?
:
SELECT *
FROM TableName
WHERE DateValue([Date Entered]) Between
[Enter Begin Date:] and [Enter End Date:];
--
Ken Snell
<MS ACCESS MVP>
I have a table that has a 'Date Entered' field that is defined as
Date/time
in a General Date format. I want to pull data by just the
mm/dd/yyyy
and
leave the time out of the selection all together. In the Query,
I
entered
'Between [Enter Begin Date:] and [Enter End Date:] but this will
not
pull
any
data at all.
What do I need to do?