Date criteria

[Steve]

HI,

I'm having a problem inserting the appropiate criteria in a query so that if
I want to create a query that would give me all the records for a certain
date.

My date field is of a date/time type but I can't seem to get the correct
criteria inserted when I go into design view.

Any thoughts.

Steve

 

[Tom van Stiphout]

On Thu, 13 Aug 2009 09:24:05 -0400, "Steve" <[email protected]>
wrote:

In the criteria line for your date field enter something like:
[Gimme a date:]
Then when you run the query it will popup a criteria form prompting
for a value. You enter a date and the query returns the corresponding
data.
This works best if you store dates without a time component. If you do
have a time component you can use:
Between [Gimme a date:] And DateAdd('d',1,[Gimme a date:])
(Access is smart enough not to prompt you twice.)

-Tom.
Microsoft Access MVP

 

[Klatuu]

It would be nice to know more specifics about what you have tried, any errors
you are getting, and what is incorrect about your results.

One helpful hint is to determine whether your data has a time component.
For example, if you have time included in the field and use only the date
portion to match on, you will get incorrect results.

 

[Douglas J. Steele]

A little more information, please.

Does the date/time field in question contain date and time, or does it only
contain date?

How do you want to specify the date: do you want to hard code it, be
prompted for it, or have the query refer to a date value in a control on an
open form?

 

[Klatuu]

With all due respect, Tom, I am philosophically opposed to parameter prompts
in an application. For starters, they cannot be validated programmatically
before they are used and an incorrect response can cause unmanagable errors
or even worse, incorrect results.

--
Dave Hargis, Microsoft Access MVP

In the criteria line for your date field enter something like:
[Gimme a date:]
Then when you run the query it will popup a criteria form prompting
for a value. You enter a date and the query returns the corresponding
data.
This works best if you store dates without a time component. If you do
have a time component you can use:
Between [Gimme a date:] And DateAdd('d',1,[Gimme a date:])
(Access is smart enough not to prompt you twice.)

-Tom.
Microsoft Access MVP

HI,

I'm having a problem inserting the appropiate criteria in a query so that if
I want to create a query that would give me all the records for a certain
date.

My date field is of a date/time type but I can't seem to get the correct
criteria inserted when I go into design view.

Any thoughts.

Steve

 

[Steve]

HI ,

Thanks for all the responses. I'll elaborate more on my problem.

First I do have the data type as Date/Time so that is probably my problem. I
really only want the date but when I click on the pull down menu I don't see
where there is only a Date datatype choice just a date/time choice.

So, if I could simply get it back to just a "date" datatype I think I would
be successful.

What I'm doing is inputting new data every day and want only to merge the
new data for that day so that's why I want to sort by date. I would like it
to prompt me for the date, that way I don't have to go in and update the
criteria every day.

For now it's a simple database, until I see if it will really meet my needs.

So, how do I make that date field a "date" datatype only?

Thank You All

Steve

 

[Steve]

Hi,

Opps, I didn't know if I mentioned that I'm running Access 2007.


Thanks
Steve

 

[Steve]

Hi Tom,

Since my last post, I tried your suggestion that you gave below and it
worked great except when I tried to merge. The query doesn't come in the
list of queries to choose from because of the need to input. (This is in
Publisher 2007)

So, how would I set the criteria in Access to have me update the date when I
need to instead of a popup window. Again, If I put in the exact date and
time for a particular record it will sort for that record and as I mentioned
in my other posts I don't see an option to just make the field a date field
not a date/time field.

Thanks
Steve

In the criteria line for your date field enter something like:
[Gimme a date:]
Then when you run the query it will popup a criteria form prompting
for a value. You enter a date and the query returns the corresponding
data.
This works best if you store dates without a time component. If you do
have a time component you can use:
Between [Gimme a date:] And DateAdd('d',1,[Gimme a date:])
(Access is smart enough not to prompt you twice.)

-Tom.
Microsoft Access MVP

HI,

I'm having a problem inserting the appropiate criteria in a query so that
if
I want to create a query that would give me all the records for a certain
date.

My date field is of a date/time type but I can't seem to get the correct
criteria inserted when I go into design view.

Any thoughts.

Steve

 

[Steve]

Hi Doug,

It does contain date/time but from my responses I mentiond that I didn't see
an option to only contain date from the pop-up window.

After some trying, I would need to enter the date value in the criteria
section of a form.

If I put in the exact date AND time, I get the result for that specific
record, but I would refer to only have to put in the dat and get all records
that match that criteria.

Is ther a way to change the field from a Date/time dield to strictly a date
field in Access 2007?

Thanks
Steve

 

[Douglas J. Steele]

If you're using a prompt, make the criteria

BETWEEN [What Date?] AND DateAdd("s", 86399, [What Date?])

If you're referring to a control on a form instead of a prompt, make the
criteria

BETWEEN Forms!NameOfForm!NameOfControl AND DateAdd("s", 86399,
Forms!NameOfForm!NameOfControl)

Note that in either case, it's usually a good idea to ensure that the
parameter is defined as Date/Time.