Date Query

G

Guest

I am having trouble retrieving all records between the first day and last day
of a month. I am using >=[StartDate]<=[EndDate] where [StartDate] and
[EndDate] are fields in a Table of all months with their first and last dates
listed. I have also tried 'Between [StartDate] And[EndDate]' Both of
these return results that do not include any records dated on the last day
of the month. I can't write code so can someone please give me a simple
answer. Some of the answers I see here in the discussion group I can't even
understand, let alone try and put into practice!

Thanks
mazbaz
 
J

John Spencer

It seems as if your date fields contain a time component. Since a date by
itself is midnight of the date (for example, 1/1/2006 00:00:00), any entry
in the field that has a time is automatically after that date.

You can fix this by adding one day to the end date in the criteria. You
could also modify the table so the EndDate is the beginning day of the next
month, but that would probably confuse most people.
=[StartDate] and < [EndDate] +1 or
=[StartDate] and < DateAdd("d",1,[EndDate])




mazbaz said:
I am having trouble retrieving all records between the first day and last
day
of a month. I am using >=[StartDate]<=[EndDate] where [StartDate] and
[EndDate] are fields in a Table of all months with their first and last
dates
listed. I have also tried 'Between [StartDate] And[EndDate]' Both of
these return results that do not include any records dated on the last
day
of the month. I can't write code so can someone please give me a simple
answer. Some of the answers I see here in the discussion group I can't
even
understand, let alone try and put into practice!

Thanks
mazbaz
 
G

Guest

What you're doing should be working. To return results between (and
including) two dates you can put either of these expressions into the
criteria row of the date field in the query:

Between [Start Date] And [End Date]

or
= [Start Date] And <=[End Date]

If neither of these work, I suspect the problem is elsewhere. Obtain the
[Start Date] And [End Date] values at the time the query runs (e.g. add them
to the query results) to make sure the computer is working with the dates you
think it is!

Good luck

David

mazbaz said:
I am having trouble retrieving all records between the first day and last day
of a month. I am using >=[StartDate]<=[EndDate] where [StartDate] and
[EndDate] are fields in a Table of all months with their first and last dates
listed. I have also tried 'Between [StartDate] And[EndDate]' Both of
these return results that do not include any records dated on the last day
of the month. I can't write code so can someone please give me a simple
answer. Some of the answers I see here in the discussion group I can't even
understand, let alone try and put into practice!

Thanks
mazbaz
 
C

ChuckGu

Date fields also include the time of day. A date of 05/31/2006 13:59:03
is not <= 05/31/2006. You have to use the expression ">= 05/01/2006 and
< 06/01/2006" to include the last day of May through 23:59:59 (11:59:59
pm).


*I am having trouble retrieving all records between the first day and
last day
of a month. I am using >=[StartDate]<=[EndDate] where [StartDate]
and
[EndDate] are fields in a Table of all months with their first and
last dates
listed. I have also tried 'Between [StartDate] And[EndDate]' Both
of
these return results that do not include any records dated on the
last day
of the month. I can't write code so can someone please give me a
simple
answer. Some of the answers I see here in the discussion group I
can't even
understand, let alone try and put into practice!

Thanks
mazbaz *
 
G

Guest

Thankyou very much I have spent days trying to sort this out. I just added
the +1 as you said and its perfect. I did try to check on the table design
and although it says its short date format when I go into the date field and
click in it, it does also bring up the time. The reation date is entered in
automatically when the form is filled in and I can't seem to find where to
alter this so I'll just go with the + 1 solution . Again thanks very much

mazbaz


John Spencer said:
It seems as if your date fields contain a time component. Since a date by
itself is midnight of the date (for example, 1/1/2006 00:00:00), any entry
in the field that has a time is automatically after that date.

You can fix this by adding one day to the end date in the criteria. You
could also modify the table so the EndDate is the beginning day of the next
month, but that would probably confuse most people.
=[StartDate] and < [EndDate] +1 or
=[StartDate] and < DateAdd("d",1,[EndDate])




mazbaz said:
I am having trouble retrieving all records between the first day and last
day
of a month. I am using >=[StartDate]<=[EndDate] where [StartDate] and
[EndDate] are fields in a Table of all months with their first and last
dates
listed. I have also tried 'Between [StartDate] And[EndDate]' Both of
these return results that do not include any records dated on the last
day
of the month. I can't write code so can someone please give me a simple
answer. Some of the answers I see here in the discussion group I can't
even
understand, let alone try and put into practice!

Thanks
mazbaz
 
J

John Spencer

If the date is being automatically inserted, it is probably in one of two
places.

In the table, check the field's default value. If it is Now() change it to
Date().

The other place to check is the entry form. It could have a control with a
default value of Now() instead of Date() or there could be some VBA code
that is setting the value to Now() when you want it to set the value to
Date().

If you decide to correct this, you will want to update your saved values to
just the date. You can do that with an update query. Set the Update to:
DateValue([YourTable].[YourDateField]


mazbaz said:
Thankyou very much I have spent days trying to sort this out. I just
added
the +1 as you said and its perfect. I did try to check on the table
design
and although it says its short date format when I go into the date field
and
click in it, it does also bring up the time. The reation date is entered
in
automatically when the form is filled in and I can't seem to find where
to
alter this so I'll just go with the + 1 solution . Again thanks very much

mazbaz


John Spencer said:
It seems as if your date fields contain a time component. Since a date
by
itself is midnight of the date (for example, 1/1/2006 00:00:00), any
entry
in the field that has a time is automatically after that date.

You can fix this by adding one day to the end date in the criteria. You
could also modify the table so the EndDate is the beginning day of the
next
month, but that would probably confuse most people.
=[StartDate] and < [EndDate] +1 or
=[StartDate] and < DateAdd("d",1,[EndDate])




mazbaz said:
I am having trouble retrieving all records between the first day and
last
day
of a month. I am using >=[StartDate]<=[EndDate] where [StartDate] and
[EndDate] are fields in a Table of all months with their first and last
dates
listed. I have also tried 'Between [StartDate] And[EndDate]' Both
of
these return results that do not include any records dated on the last
day
of the month. I can't write code so can someone please give me a
simple
answer. Some of the answers I see here in the discussion group I can't
even
understand, let alone try and put into practice!

Thanks
mazbaz
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top