By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,308 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

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

P: 78
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:
Expand|Select|Wrap|Line Numbers
  1. Me.Text200 = DCount("[Call Log]", "[LIVEQuery]")
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. =Format(Date(),"Long Date") & " " & Format(Time(),"Long Time")
  2.  
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!
Jan 13 '10 #1

✓ answered by nico5038

You ran into an "old" Access quirk, being the fact that special characters in fieldnames aren't handled correctly.
Access sees the "/" in your [Date/Time Added] field as an operand and NOT as part of the name :-(
Change the fieldname in tblName into [DateTimeAdded] (query changes automatically too) and remove the code from the OnCurrent event.
Next add to the controlsource of field text199:
Expand|Select|Wrap|Line Numbers
  1. =DCount("[Call Log]","[LIVEQuery]","format([Date/TimeAdded],'yyyymmdd')=format(Date(),'yyyymmdd')")
  2.  
and all will work.

My advise is to start with naming fields only with alphabetical characters and use capitalization to make them readable. NEVER use / - + or other special characters in a name !
Also make sure that a name isn't a "reserved word" like "Date" as that's also a function. (http://support.microsoft.com/kb/286335)

Nic;o)

Share this Question
Share on Google+
29 Replies


nico5038
Expert 2.5K+
P: 3,072
You can add a WHERE clause to the Dcount, try:
Expand|Select|Wrap|Line Numbers
  1. Me.Text200 = DCount("[Call Log]", "[LIVEQuery]","[yourdatefield]=Date")
  2.  
Nic;o)
Jan 13 '10 #2

P: 78
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!
Jan 14 '10 #3

P: 78
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.
Jan 14 '10 #4

MMcCarthy
Expert Mod 10K+
P: 14,534
Try this ...

Expand|Select|Wrap|Line Numbers
  1. Me.Text191 = DCount("[Call Log]", "[LIVEQuery]", "[Text82]=Format(Now(),'Long Date')")
PS: Please use code tags when posting code.
Jan 14 '10 #5

P: 78
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.
Jan 14 '10 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
In the call log LiveQuery is the text field called Text82 it seems a strange name for a field
Jan 14 '10 #7

P: 78
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
Jan 14 '10 #8

nico5038
Expert 2.5K+
P: 3,072
You need to use the content of the Text82 field and compare that with the Datefield in the LIVEQuery like:
Expand|Select|Wrap|Line Numbers
  1. DCount("[Call Log]", "[LIVEQuery]", "[YourDatefield]=#" & Me.Text82 & "#")
  2.  
This assumes your datefield in the query holds no time, else an additional format() function is needed.

Nic;o)
Jan 14 '10 #9

Megalog
Expert 100+
P: 378
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]").
Jan 14 '10 #10

P: 78
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).
Jan 14 '10 #11

MMcCarthy
Expert Mod 10K+
P: 14,534
OK so the DCount function should be as follows:
Expand|Select|Wrap|Line Numbers
  1. DCount("[Call Log]", "[LIVEQuery]", "[Date/Time Added]=#" & Me.Text82 & "#") 
  2.  
Jan 14 '10 #12

nico5038
Expert 2.5K+
P: 3,072
Ok then the Dcount should be:
Expand|Select|Wrap|Line Numbers
  1. DCount("[Call Log]", "[LIVEQuery]", "[YourDatefield] between #" & Me.Text82 & "00:00 # and #" & Me.Text82 & " 23:59#")
  2. or
  3. DCount("[Call Log]", "[LIVEQuery]", "Format([YourDatefield],'yyyymmdd') = '" & Me.Text82 &"'")
  4.  
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)
Jan 14 '10 #13

P: 78
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
Jan 14 '10 #14

P: 78
Sorry Nico, we overlapped......must have been typing at the same time. I'll give this new code a try!
I appreciate your time!
Jan 14 '10 #15

nico5038
Expert 2.5K+
P: 3,072
From the error I read that the date field on your form has an awkward "format".
Better to change the format into "general long", thus giving a format without the named day of the week...

Nic;o)
Jan 14 '10 #16

P: 78
Nico, we were typing at the same time last round and you hit enter first. The error message I wrote above pertained to:
Expand|Select|Wrap|Line Numbers
  1. DCount("[Call Log]", "[LIVEQuery]", "[Date/Time Added]=#" & Me.Text82 & "#")
Not the two new codes you gave me.

Here's what happened with the last two you listed:

1st one came back with an error.
The 2nd one doesn't produce an error but the output textbox remains at 0.
Jan 14 '10 #17

NeoPa
Expert Mod 15k+
P: 31,186
I suggest that you don't worry about the format at all for isolating the date part of the comparison. Dates and Times are perfectly adequately handled within SQL (and as such in a Domain Aggregate function).
Expand|Select|Wrap|Line Numbers
  1. =DCount("*",[LIVEQuery],"DateValue([Date/Time Added])=#" & Format(CDate(Me.Text82),'m/d/yyyy') & "#")
You will need to ensure that Text82 represents a recognisable date of course (This is currently not so - From your post #14). You shouldn't need to Trim() the value if the format is done properly. You may like to check out Literal DateTimes and Their Delimiters (#) to see exactly how date literals should be formatted.
Jan 14 '10 #18

nico5038
Expert 2.5K+
P: 3,072
Can you attach to your comment (use Go Advanced button) a sample .mdb with just the "text82" form, the query and the needed tables so we can reconstruct what's happening?

Nico)
Jan 14 '10 #19

P: 78
Thanks NeoPa! Looking back at Post #5, I missed that link completely the first time through! I think I was so excited to try the code, I never looked down that far.

I'll check it now......(and I apologize for missing it before)
Jan 15 '10 #20

NeoPa
Expert Mod 15k+
P: 31,186
Technically (though I don't like it personally) posting a single line without the tags is acceptable. That's all you've done so far (as I checked) so I had to remove that comment hastily ;) Nevertheless, I'm pleased you've seen Mary's comment now. It can make life a lot easier.

How did you get on with the code posted anyway? There are various solutions in here so I expect you may be sorted now. If not then Nico's offering to check over a db for you if you post a cut-down one (See post #19).
Jan 15 '10 #21

P: 78
It won't let me upload my sample .mdb file:

"Valid file extensions: bmp doc gif jpe jpeg jpg pdf png psd txt zip"

It tells me it's an invalid file!
Jan 15 '10 #22

MMcCarthy
Expert Mod 10K+
P: 14,534
You need to zip the file up first and then attach it.
Jan 15 '10 #23

P: 78
Here it is. I made a copy and deleted all non essential data.

Text82 is at the bottom of the form and the output textbox is obvious (in the middle).

The code (which is back to the standard - count everything - format).

Thanks!!!!!
Attached Files
File Type: zip dbtest.zip (84.8 KB, 61 views)
Jan 15 '10 #24

nico5038
Expert 2.5K+
P: 3,072
You ran into an "old" Access quirk, being the fact that special characters in fieldnames aren't handled correctly.
Access sees the "/" in your [Date/Time Added] field as an operand and NOT as part of the name :-(
Change the fieldname in tblName into [DateTimeAdded] (query changes automatically too) and remove the code from the OnCurrent event.
Next add to the controlsource of field text199:
Expand|Select|Wrap|Line Numbers
  1. =DCount("[Call Log]","[LIVEQuery]","format([Date/TimeAdded],'yyyymmdd')=format(Date(),'yyyymmdd')")
  2.  
and all will work.

My advise is to start with naming fields only with alphabetical characters and use capitalization to make them readable. NEVER use / - + or other special characters in a name !
Also make sure that a name isn't a "reserved word" like "Date" as that's also a function. (http://support.microsoft.com/kb/286335)

Nic;o)
Jan 16 '10 #25

P: 78
Nico, thank you again very much! I appreciate you taking a look at my db and coming up with a solution!

Thank you to everyone who helped along the way! With all your expert help, I am learning so much!
Jan 18 '10 #26

nico5038
Expert 2.5K+
P: 3,072
Glad to see it's appreciated :-)

People learning 'the trade' is the best reward we can get for our efforts !

Nic;o)
Jan 18 '10 #27

P: 78
Hey Nico,

One last question on this one. If the following code only counts the data for the current "day", how would it change to look at the current "month"??

Expand|Select|Wrap|Line Numbers
  1. =DCount("[Reference #]","[qrySteve]","format([ChangedDate],'yyyymmdd')=format(Date(),'yyyymmdd')")
Thanks!
Feb 1 '10 #28

nico5038
Expert 2.5K+
P: 3,072
Use the "YYYYMM" as the format in the WHERE parameter.

Nic;o)
Feb 1 '10 #29

P: 78
Thank you! Thank you! Thank you!
Feb 2 '10 #30

Post your reply

Sign in to post your reply or Sign up for a free account.