Query Time

[jlclark4]

I think I may have screwed up.

I am trying to run a query to show data between two dates. I know how to create a perimeter BUT my data is in MM/DD/YYYY HH:MM:SS AM/PM.



How can I query a full day?!

[mkallover]

Depending on the query that shouldn't matter. You're just trying to display records where the date falls between two dates right?

Can you paste the query?

[jzwp11]

If I understand what you are after, you could strip the time portion off by using the datevalue() function.

[jlclark4]

I am trying to automate reporting. Exporting to excel is now causing issues with Filter due to the size exceeding 10,000. All I want is to fitler the data and use totals to give me a count for a certain date or week or what ever the user wants to query for. In the actual table we still want to keep the time stamp. But in the Query it does not matter.

I can not paste the actual query/table but I will create a sample.

My end result is to have a form with several subform/queries in it that gives the user the counts they need for the date ran. <-- That part I think I have covered thanks to the help I got for the Time Sheet

[jlclark4]

I also want it to group by date and not by time. Who cares how many of that type were done by that particular time stamp. I just need it for the day.

Even when I tried running a parameter for 4/14/2011 00:01:00 AM to 4/14/2011 11:59:00 PM it wouldn't bring anything up...

[jzwp11]

How about this


SELECT datevalue(tblData.daterecieved), tblData.doctype, Count(tblData.doctype) AS CountOfdoctype
FROM tblData
GROUP BY datevalue( tblData.daterecieved), tblData.doctype;

[jlclark4]

I got a Data Type mismatch in criteria expression error...

[jzwp11]

Could you please provide the query that gave the error?

[jlclark4]

I think it has something to do with the table being a click event. When they click the text box on the form, it produces the NOW function and writes it to the table. All I did was change the tbl title to match my copy....

Do you think that could cause a issue? Let me mold the sample to match my hard copy. Give me a few minutes.

[jlclark4]

Ugh, It works in the sample. I cant send you my actual database. But thanks for the information! I will keep trying.

[jzwp11]

The table and field names must match your working database, so you will have to make those adjustments before the query will work.

[pbaldy]

Also make sure there are no Null values in that date field. I suspect they would cause an error. That may be why it works in the sample but not the actual db.