Query Date not Date and Time

G

Guest

Hello-
I'm REALLY new to Access.

I'm trying to set-up a query for this past week.
It looks like Between Date() and Date()-6
But, when I do this, I get nothing.

If I enter Between #10/27/2006# and #11/03/2006# I get data.
Or, if I enter >#10/31/2006# I get data.

Even Date() does not return anything. MS office online says "Note that the
date or time format that you use to specify the criteria do not need to match
the format in which the value is stred in the database."

I know there is stuff out there, but it has the date AND time stamp.
So, when I'm looking for date only, it gives me nothing.

I'm certain this is something simple I'm missing, but I am at a loss.

Please help!
Thanks in advance.
 
D

Douglas J. Steele

Are you simply putting

Between Date() and Date()-6

as the criteria in the grid as part of the graphical query builder? If so,
it should work.

Are you getting any error message? Does the Date function work for you? (To
determine if it's a function with Date(), open the Immediate window using
Ctrl-G, type ?Date() in the pane and hit Enter. Does the correct date get
returned?)
 
J

John Vinson

Hello-
I'm REALLY new to Access.

I'm trying to set-up a query for this past week.
It looks like Between Date() and Date()-6
But, when I do this, I get nothing.

If I enter Between #10/27/2006# and #11/03/2006# I get data.
Or, if I enter >#10/31/2006# I get data.

Even Date() does not return anything. MS office online says "Note that the
date or time format that you use to specify the criteria do not need to match
the format in which the value is stred in the database."

I know there is stuff out there, but it has the date AND time stamp.
So, when I'm looking for date only, it gives me nothing.

I'm certain this is something simple I'm missing, but I am at a loss.
Try:

= DateAdd("d", -6, Date()) AND < DateAdd("d", 1, Date())

The problem is that you have a time portion. The date/time value
#11/02/2006 14:05:20# is in fact NOT "BETWEEN Date() AND Date() - 6"
since Date() is - at the moment - #11/02/2006 00:00:00#, fourteen odd
hours outside your specified range.

John W. Vinson[MVP]
 
G

Guest

John, it's beautiful!
Thanks. I knew it had something to do with the time stamp, just couldn't get
the syntax. It works charmingly.

Now, since I am still new at this, is there a way to automate this job to
always run on Monday mornings at 6:00am? And save the query with mondays date?

I'm just thinking out loud.
Thanks again.
 
J

John Vinson

Now, since I am still new at this, is there a way to automate this job to
always run on Monday mornings at 6:00am? And save the query with mondays date?

Not within Access (unless you have Access open at all times and have a
Timer event on a form, also open at all times). You can use the
Windows Scheduler to launch Access on a schedule; you can even use an
optional command line argument to execute a specific macro (which can
in turn run code or run a query).

It's probably not necessary to "save the query with monday's date" -
the date can be a variable value, using Date() for today's date, and
(if need be) DateAdd and Weekday to calculate the most recent previous
Monday, or next Monday, or whatever.
I'm just thinking out loud.

I hear ya... <g>

John W. Vinson[MVP]
 

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

Top