DateValue in Query

[kateri4482]

I have a query that prompts the user for a date, but because the date field
itself is a "general date" which includes the time, every time they type in a
date (08/19/2008 for example), they get no results. How can I used the
DateValue function along with this parameter prompt? I need the field itself
to include the time as it is used for other queries, but I want the user to
just be able to type in the date and get results no matter what the time is.
I am not sure how to combine the parameter prompt with the DateValue
function. Thanks.

 

[Jerry Whittle]

Between [Enter the date] and [Enter the date] + 0.99999

The same parameter can be used twice but only prompted for once. The +
0.99999 takes the time up to one second before midnight on that day.

But be very sure to define the parameter as DateTime. Otherwise there could
be problems. The SQL statement should look something like this:

PARAMETERS [Enter the date] DateTime;
SELECT ASIF_BU.LAST_ANALYZED
FROM ASIF_BU
WHERE (((ASIF_BU.LAST_ANALYZED)
Between [Enter the date] And [Enter the date]+0.99999));

 

[Jeff Boyce]

An alternate to Jerry's solution would be to create a new field in your
query that returns the DateValue() part of the field, then use the
parameter(s) against that field. Your new field might look something like:

YourNewField: DateValue([YourDateTimeField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[kateri4482]

Thank you Jeff. Your suggestion is what I tried at first, but I keep getting
a "data type mismatch in the criteria expression". So I am not sure what I
am doing incorrect. The new date field (NewDate) and the one I using the
DateValue function against (Date) are both the same type. Here is my select
query:
SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date,
TblDailyCodes.NewDate
FROM TblDailyCodes
WHERE (((TblDailyCodes.NewDate)=DateValue([Date])));

Any suggestions?

An alternate to Jerry's solution would be to create a new field in your
query that returns the DateValue() part of the field, then use the
parameter(s) against that field. Your new field might look something like:

YourNewField: DateValue([YourDateTimeField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query that prompts the user for a date, but because the date field
itself is a "general date" which includes the time, every time they type
in a
date (08/19/2008 for example), they get no results. How can I used the
DateValue function along with this parameter prompt? I need the field
itself
to include the time as it is used for other queries, but I want the user
to
just be able to type in the date and get results no matter what the time
is.
I am not sure how to combine the parameter prompt with the DateValue
function. Thanks.

 

[Jeff Boyce]

I think you have it backwards. You need to know the DateValue of YOUR
date... the Date() function only returns a date value.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Thank you Jeff. Your suggestion is what I tried at first, but I keep
getting
a "data type mismatch in the criteria expression". So I am not sure what
I
am doing incorrect. The new date field (NewDate) and the one I using the
DateValue function against (Date) are both the same type. Here is my
select
query:
SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date,
TblDailyCodes.NewDate
FROM TblDailyCodes
WHERE (((TblDailyCodes.NewDate)=DateValue([Date])));

Any suggestions?

An alternate to Jerry's solution would be to create a new field in your
query that returns the DateValue() part of the field, then use the
parameter(s) against that field. Your new field might look something
like:

YourNewField: DateValue([YourDateTimeField])

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a query that prompts the user for a date, but because the date
field
itself is a "general date" which includes the time, every time they
type
in a
date (08/19/2008 for example), they get no results. How can I used the
DateValue function along with this parameter prompt? I need the field
itself
to include the time as it is used for other queries, but I want the
user
to
just be able to type in the date and get results no matter what the
time
is.
I am not sure how to combine the parameter prompt with the DateValue
function. Thanks.

 

[Lord Kelvan]

in you criteria have #[Please enter a date]#

for a date that includes time put # around it

that should work

so go back to your origional query and try that

Regards
Kelvan

 

[Lord Kelvan]

sorry forgot that only works if you manually enter the date what i do
because i have timestamps in my db is i use query paramaters. so in
the query paramaters i would type [please enter a date] and set it to
date/time

and then in the query i would enter the same [please enetr a date] in
the date field criteria and it seems to work fine