The table is formatted in a short date
Not really. Dates are carried as floating point decimal fields and have no
internal formatting, Any formatting is done when the data are retrieved, not
when it is stored.
Input Masks only control how the user is allowed to enter values, not how
the values are stored for dates. For text fields, you have that option;
however.
Formats only control how the value is to be displayed when retrieved.
Again, it has no effect on how the value is stored.
the input mask is 99/99/0000;0;
This means the user can only put in month, day, and year. There is no
facility to enter time.
the combo0 is imported on a macro to a caption
This part I don't understand.
But since you have times in the fields, you need to allow for that when you
are filtering. That is because when you do the comparisons, it considers the
entire value so that if you say <=#5/20/2008# That implies 05:20:2008
00:00:00 AM
If the value in the field is 05:20:2008 00:00:01 AM it will be excluded,
because it is greater than the compare value. For this reason, you should
only use time value in a date field when the time is important; otherwise, to
compare by date only, you need to exclude the time value. You can do that
using the DateValue() function. It returns only the date part.
So to get what you want, assuming your combo is 5/19/2008 and you want the
data form 5/12/2008 through 5/18/2008
BETWEEN DateValue(DateAdd("ww",-1,[Forms]![Print Weekly Reports]![Combo0]))
AND DateValue(DateAdd("d",-1,[Forms]![Print Weekly Reports]![Combo0]))
--
Dave Hargis, Microsoft Access MVP
lovespar said:
Yes, both the combo0 and date field in the table have date and time.
The table is formatted in a short date, but the input mask is 99/99/0000;0;
And the combo0 is imported on a macro to a caption. the expression is Now()
:
What about the date field in the table? Does it have time or not?
--
Dave Hargis, Microsoft Access MVP
:
Yes, I want to go from the 12th through the 18th of May on this particular
report.
The Combo0 field does have a time field. When I tried to make it a general
date it didn't pull any data at all.
I have been flipping this formula back and forth from -8 to -7 for several
monthes now to pick up Mondays date.
:
So Combo0 has a Monday date and you want to extract data that starts on the
previous Monday and goes through Sunday. For example:
Combo0 = 5/19/2008
And you want data from 5/12 through 5/18?
Another question, do your date fields have time in them? When trying to
filter dates with times in the date fields, it can give you incorrect results.
Let me know, and I can show you the correct formula for your situation.
--
Dave Hargis, Microsoft Access MVP
:
That didn't work. The dates I am trying to pick up are Monday through Sunday.
For some reason I keep getting either Sunday through Sunday (8days) or
Tuesday through Sunday(6days) and Monday won't come in to the query. The
Combo0 is also a Monday date, but it is the date I want to go back from???
:
Just change your formula a bit.
([Forms]![Print Weekly Reports]![Combo0])-7 And <=([Forms]![Print Weekly Reports]![Combo0])
--
Dave Hargis, Microsoft Access MVP
:
I have a query that is extracting from a table by date using a date as a
beginning point and then working backwards. It is supposed to pull a weeks
worth of data which would be 84 data points, And it usually does. However now
it is pulling 96 data points if I make the equation (date -8) or 72 data
points if I make the equation (date-7) which is 8 days or 6 days. It will not
pull 7 days worth of data! This is the equation I have tried in various
forms. Combo0 would be Mondays date.
=([Forms]![Print Weekly Reports]![Combo0])-8 And <=([Forms]![Print Weekly Reports]![Combo0])
I have also tried
=([Forms]![Print Weekly Reports]![Combo0])-8 And <=([Forms]![Print Weekly Reports]![Combo0])-1
and
=([Forms]![Print Weekly Reports]![Combo0])-7 And <=([Forms]![Print Weekly Reports]![Combo0])
How can I get 7 days of Data? The table is correct
Thanks in advance.
