How to Query data created only on the current day? (Access 2003)

[WannabePrgmr]

I am trying to display total numbers of data in a textbox, "Textbox200 " on a form "Master", but I only want to display current daily data.
I currently get the data from:

Me.Text200 = DCount("[Call Log]", "[LIVEQuery]")

which gets the total "Live" data from the "LIVEquery" , which is updated in the Form_Current.

I would like to change this to look at the current date and only query for data entered during that date.

I have a textboxx with the following in it:

=Format(Date(),"Long Date") & " " & Format(Time(),"Long Time")

Is there a way I can put in my query criteria under the "Date/Time Entered" column, which is a time stamp that gets put into each record, that checks the current date for the current day and filter the results to that day only?

I appreciate any help!

Thank you!

[nico5038]

You can add a WHERE clause to the Dcount, try:

Me.Text200 = DCount("[Call Log]", "[LIVEQuery]","[yourdatefield]=Date")

Nic;o)

[WannabePrgmr]

I tried that and I get an error that states "You cancelled the previous operation".
When I click Debug, it hi-lights that line.

Me.Text191 = DCount("[Call Log]", "[LIVEQuery]", "[Text82]=Date")

Text82 is the "=Format(Date() ,"Long Date") & " " & Format(Time()," Long Time")
" code.

I'm probably still not getting it......


I have a field in my table that is "Date/Time Added", which is a stamp of the date and time each record was submitted. Is there a way to include that filed in my "LIVEQuery" and sort by "Today's date"?

Thannks!

[WannabePrgmr]

I tried this in the criteria for the "Date/Time Added" filed in the query:
Like "12/23/2009*"
and it worked as far as bringing back only records from 12/23. Now I'm trying to tie that to the current date:
Like "[=Date()]*"
or something like that? I need the star for that field because it has the time as well.

[MMcCarthy]

Try this ...

Me.Text191 = DCount("[Call Log]", "[LIVEQuery]", "[Text82]=Format(Now(),'Long Date')")

PS: Please use code tags when posting code.

[WannabePrgmr]

Thanks for the reply! I tried it and nothing seems to happen. No errors, but the count box (Text191) stays at 0 no matter how many "Live" calls I produce (I tried a bunch this morning hoping it would only count those).

Could it be in the query side of things? "LiveQuery" only queries "Live" calls from the "Call Log" field. I tried including the "Date/Time Added" as one of the collections for the query with no criteria, but it didn't work either, so I took it out all together.

[MMcCarthy]

In the call log LiveQuery is the text field called Text82 it seems a strange name for a field

[WannabePrgmr]

No, that's the textbox on the form that has the date and time code in it.
The text filed for the LIVEQuery is called Call Log, which has either "Live, Audix, or Email in it. The query obviously filters on "Live" only.

I only referenced the Text82 because I need the query to filter on the current day, which is found in Text82.

Thanks

[nico5038]

You need to use the content of the Text82 field and compare that with the Datefield in the LIVEQuery like:

DCount("[Call Log]", "[LIVEQuery]", "[YourDatefield]=#" & Me.Text82 & "#")

This assumes your datefield in the query holds no time, else an additional format() function is needed.

Nic;o)

[Megalog]

What is the Date/Time field called in LiveQuery? How is it formatted? (text or Date/Time) This is needed before you can do the DCount on the query, as shown in nico's example in post #2 ("[yourdatefield]").

[WannabePrgmr]

First of all, both of you are awesome! Thanks for the help...I appreciate it!

The the field that is time stamping the date and time is called "Date/Time Added" and it is formatted as Date/Time (It is generated from the "now()" function). So it does hold the time as well (to answer Nico's question).

[MMcCarthy]

OK so the DCount function should be as follows:

DCount("[Call Log]", "[LIVEQuery]", "[Date/Time Added]=#" & Me.Text82 & "#")

[nico5038]

Ok then the Dcount should be:

DCount("[Call Log]", "[LIVEQuery]", "[YourDatefield] between #" & Me.Text82 & "00:00 # and #" & Me.Text82 & " 23:59#")
or
DCount("[Call Log]", "[LIVEQuery]", "Format([YourDatefield],'yyyymmdd') = '" & Me.Text82 &"'")

The second Dcount needs to hold the data in the yyyymmdd format without / or - as a separator, else it won't work as Access will process it as a string !

Nic;o)

[WannabePrgmr]

I tried it and this is the error that came up:

Syntax error in date in query expression '[Date/Time Added]=# Thursday, January 14, 2010 4:00:48 PM#'

Does this have to do with what Nico stated: "This assumes your datefield in the query holds no time, else an additional format() function is needed."

He had given the same code. The "Date/Time Added" field has a time as well. What additional format() function would I need?

Thanks

[WannabePrgmr]

Sorry Nico, we overlapped..... .must have been typing at the same time. I'll give this new code a try!
I appreciate your time!