search by Date

[j0se]

I have a form displaying some jobs and dates. I want to code a button that filters all records to show me today's jobs only, but I'm having some trouble

' Apply filter to show only today's jobs
DoCmd.ApplyFilter , "Date = '" & Now()

That doesn't work because Now() also contains the time.

I tried:

DoCmd.ApplyFilter , "Date = '" & Date

but I don't think I have the inverted commas in the right place (As it wouln't let me do: =Date()

Any help is most welcome

 

[Mile-O]

DoCmd.ApplyFilter , "[Date] = #" & Date() & "#"

Date, however, is not advised as a field name (or any object name, for that matter) as it is a reserved word in Access.

 

[j0se]

bizarre

It doens't seem to work (I also changed fieldname DAte to Date_Created)

I can see that Date gives me 04/02/2004 (same value as I have in the db) and the Date_Created field in the table is set to short date, deafult value --> =Date()

Ive even tried the code behind a new button (in case it was some bizarre refresh prob or a control referencing an old object)

But still no cigar

Any ideas or workarounds?

PS thanks for your reply in the first place

 

[Mile-O]

Does your date field also contain times?

 

[j0se]

aye

This is the problem - the Date_Created field (I changed the name) is set to short date datatype, and its default is =Date() , but I see all that does is hide the time (if I click on the cell, I see the full date and time - as =Now() would produce) so no wonder I can't match it to a date

I've just been trying to isolate the time like this:

Left(Date_Created.Value, 10)

But I'm still not getting a match when I filter (even though the above outputs the date only)

 

[j0se]

ah

It works now

Perhaps I hadn't saved the changes to the table when I changed format

Thanks for spending time on this prob!!!

 

[Mile-O]

Try this as a criteria:

Between Date() And DateAdd("s", -1, Date() +1)

 

[j0se]

agh

not sure how to add that criteria withn brackets

I have a query qorking now, showing today's jobs, but what I really want is to click on a button and trigger DoCMD. Applyfilter (which I still can't get working) otherwise I'd have to change the form's recordsource to teh query (which works fine) but then when I click the other buttons (to do more DoCMD. Applyfilter ) I'd have to change the recordsource back again

 

[Mile-O]

DoCmd.ApplyFilter, "[Date_Created] Between #" & Date() & "# And #" & DateAdd("s", -1, Date() + 1) & "#"

 

[Mile-O]

This is a first for me - I've never used Form Filters.

 

[j0se]

agh

Neither have I

It still doesn't work. Bugger it - I'll swtich the form's recordsource and then check the recordsource each time I apply a filter

Thanks so much!