problem query with Date() 1

[tizwaz]

I have used the criteria Date() to get results for items logged today in a query in other databases with no problems but I am using a database now and cannot get this to work in a simple query. An error Undefined function 'date' in expression comes up. The field I am using it on is a date/time field called Date. I tried renaming the field date opened but it still won't work. Any ideas??

 

[psemianonymous]

Press CTRL-G to get your immediate window. Click on Tools->References and check to see if any of your references are "MISSING:".



--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[thargrav]

How are you using Date() in your query?

Tom

 

[tizwaz]

foolio12
I tried what you suggest but reference is greyed out.

Tom

Its just a very simple query designed to show anything with today's date. Under the field [date] I have put in the criteria row Date(). When I try and run the query that error message comes up

 

[thargrav]

Oh, Date() won't work there.

If you are trying to filter on date then you can do things like:
=Now()
<Now()+1
<Now()+1 and >Now()-6

If you are trying to automatically insert that date into the table then you can put =Now() in that column's defauilt value. If you want just the date without the time then put =DateValue(Now()) in the same field.

Hope this helps,
Tom

 

[tizwaz]

Tom
I tried that but it just brought up a blank record with today's date in the date field and autonumber in the autonumber field - all other fields are blank.

I got Date() to work on another database by using a virtually identical query. I don't want to insert today's date anywhere I want to pull up any records that have already got today's date in the date field.

 

[thargrav]

In that case you need to put =DateValue(Now()) in the Criteria part of the date field in the query. This is assuming that the date records contain only purely date information and not date time. If the records contain date time then you will need use something like this:

RealDate: DateValue([&quot;your date column title&quot;])

to convert the records into purely date. Then you can pull only today's records by placing =DateValue(Now()) in the Criteria part of the in the query.

FYI: Now() returns the date and time and putting =Now() into the Criteria part of the in the query will only return records that are equal to right now, down to the 1/100 second (from memory).

Tom

 

[tizwaz]

Thanks Tom - that works now - thanks for all the tips

 

[GingerR]

check this out

faq181-936

 

[votegop]

Tom's post is most helpful. I used his RealDate: DateValue([&quot;your date column title&quot;]) to find records by date where the field has datetime data in it.

My users need to find records between two dates, so I used the following in the criteria field:

Between (DateValue([Type beginning date:])) And (DateValue([Type ending date:]))

I hope this helps someone someday.