Selecting data by date

[Guest]

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?

 

[Ken Snell \(MVP\)]

SELECT *
FROM TableName
WHERE DateValue([Date Entered]) Between
[Enter Begin Date:] and [Enter End Date:];

 

[Guest]

Use a calculated field like this --
Date_No_Time: DateValue([Date Entered])

 

[Guest]

Thank you both for your ideas. Unfortunately, I negleted to tell the whole
story. In the table, I have 3 records that were entered on the same date.
When I just want to pull these three, I enter the same date for the beginning
date and the ending date. Nothing is pulled. When I try it using a 1 day
difference, I get the three. Why can I not just enter the same date for both
and get just that day's data?

Use a calculated field like this --
Date_No_Time: DateValue([Date Entered])

--
KARL DEWEY
Build a little - Test a little

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?

 

[Guest]

Lots of times DateTime data has time. Time is a decimal fraction of a day
and therefore a larger number than just the date which is as of midnight.
See Ken's response. He strips the time off of the data.
--
KARL DEWEY
Build a little - Test a little

Thank you both for your ideas. Unfortunately, I negleted to tell the whole
story. In the table, I have 3 records that were entered on the same date.
When I just want to pull these three, I enter the same date for the beginning
date and the ending date. Nothing is pulled. When I try it using a 1 day
difference, I get the three. Why can I not just enter the same date for both
and get just that day's data?

Use a calculated field like this --
Date_No_Time: DateValue([Date Entered])

--
KARL DEWEY
Build a little - Test a little

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?

 

[John W. Vinson]

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?

As an alternative, which will use any Index you've defined on [Date Entered],
you can use a criterion

= CDate([Enter Begin Date:]) AND < DateAdd("d", 1, CDate([Enter End Date:]))

The problem might be that you're missing data on the last day (the only day if
there's just one) - #10/16/2007 5:11:12pm# is NOT between #10/15/2007# and
#10/16/2007#, because the #10/16/2007# means midnight at the beginning of the
day.

John W. Vinson [MVP]

 

[Bunky]

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?

 

[Douglas J. Steele]

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?

 

[Bunky]

Still the same message.

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?

 

[Bunky]

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?

 

[Douglas J. Steele]

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!)

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?

 

[Bunky]

Doug,

That was the entire query; I was using this query as a test bed for
selection criteria on other queries I'm having problems with. By using a
combination of your information and Mr. Vinson's info I came up with this
that works.

Between [Begin Date:] And DateAdd("d",1,CDate([End Date:]))

Please let me know if this is not 'correct' but it does seem to be giving
the correct info.

Thanks

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!)

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?

 

[Douglas J. Steele]

Well, remember that I said that the original query looked correct too!

I have no idea why you were getting the error you were, but if your date
field also includes time, then using DateAdd like that is correct.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Doug,

That was the entire query; I was using this query as a test bed for
selection criteria on other queries I'm having problems with. By using a
combination of your information and Mr. Vinson's info I came up with this
that works.

Between [Begin Date:] And DateAdd("d",1,CDate([End Date:]))

Please let me know if this is not 'correct' but it does seem to be giving
the correct info.

Thanks

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!)

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?

 

[Ken Snell \(MVP\)]

Sometimtes, adding a PARAMETERS sentenece can clear up date interpretation
issues:

PARAMETERS [Begin Date:] DateTime, [End Date:] DateTime;
SELECT *
FROM [Distinct Waitlist Desired]
WHERE DateValue([Date Worked]) Between
[Begin Date:] and [End Date:];


--

Ken Snell
<MS ACCESS MVP>

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?