Query returns nothing

[kateri4482]

What is wrong with this query?

SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date,
DateValue([Date]) AS NewDate
FROM TblDailyCodes
WHERE (((TblDailyCodes.Date) Is Not Null) AND
((TblDailyCodes.NewDate)=Date()));

I get no records - even though I know there are some there. If I remove the
AND part of the query I get records but I only want those records whose
[NewDate] is today. Thanks for your help.

 

[KARL DEWEY]

I several things --
There is no such field as TblDailyCodes.NewDate - NewDate is a product of
this query.
Try this --
SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date
FROM TblDailyCodes
WHERE DateValue(TblDailyCodes.Date) = Date();

Also you should not have a field named Date as it is a reserved word and may
cause other problems.

 

[Lord Kelvan]

your query is trying to return dates that are todays date are you sure
you have values that are against todays date and is

NewDate

a field in

TblDailyCodes

because if not then
TblDailyCodes.NewDate
will not work

also you cannot compare datevalue() to date()

you need to do this

Format([date],"dd/mm/yyyy")

or if you are american

Format([date],"mm/dd/yyyy")

so


SELECT Code, Classroom, format([date],"dd/mm/yyyy")
FROM TblDailyCodes
WHERE [Date] Is Not Null AND format([date],"dd/mm/yyyy") = date()

or

SELECT Code, Classroom, [date]
FROM TblDailyCodes
WHERE [Date] Is Not Null AND format([date],"dd/mm/yyyy") = date()

if you want to display the date and time

hope this helps

regards
Kelvan

 

[John Spencer]

MY GUESS is that tblDailyCodes.NewDate contains a date with a time. One
way that will happen is if you use Now() to assign a value to the field.

Try the following:

SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date,
DateValue([Date]) AS NewDate
FROM TblDailyCodes
WHERE TblDailyCodes.[Date]>=Date() AND tblDailyCodes.[Date] < Date() +1

That should get any records that exist for the current date. BY the
way, using DATE as a field name is a bad idea. It can lead to confusion
on whether you mean the field named date or the function named date.
Normally, I use some descriptor on field names like SaleDate, BirthDate,
ActionDate, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Ken Sheridan]

I wouldn't be too happy assuming a regional date setting. You could format
both parts of the operation:

WHERE FORMAT([Date],"yyyy-mm-dd")=FORMAT(DATE(),"yyyy-mm-dd")

but my preference would be to avoid formatting with:

WHERE [Date]>=DATE() AND [Date]<DATE()+1

or if you don't like simple date arithmetic:

WHERE [Date]>=DATE() AND [Date]<DATEADD("d",1,DATE())

Ken Sheridan
Stafford, England

 

[kateri4482]

This worked. Thank you! I was creating [NewDate] in order to strip out the
time because [Date] had the time in it. However, your query makes more sense
and I no longer need the [Newdate] field. I also took everyone's suggestion
who replied to this question and changed the [Date] field to something else.
Thanks everyone!

MY GUESS is that tblDailyCodes.NewDate contains a date with a time. One
way that will happen is if you use Now() to assign a value to the field.

Try the following:

SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date,
DateValue([Date]) AS NewDate
FROM TblDailyCodes
WHERE TblDailyCodes.[Date]>=Date() AND tblDailyCodes.[Date] < Date() +1

That should get any records that exist for the current date. BY the
way, using DATE as a field name is a bad idea. It can lead to confusion
on whether you mean the field named date or the function named date.
Normally, I use some descriptor on field names like SaleDate, BirthDate,
ActionDate, etc.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

What is wrong with this query?

SELECT TblDailyCodes.Code, TblDailyCodes.Classroom, TblDailyCodes.Date,
DateValue([Date]) AS NewDate
FROM TblDailyCodes
WHERE (((TblDailyCodes.Date) Is Not Null) AND
((TblDailyCodes.NewDate)=Date()));

I get no records - even though I know there are some there. If I remove the
AND part of the query I get records but I only want those records whose
[NewDate] is today. Thanks for your help.