Date with Function After It

[KurtW]

Please see below. The query is still not working correctly.

SELECT CDate(Format([EnterDate],"Short Date")) AS Expr1, Reminders.EnterUserId
FROM Reminders
GROUP BY CDate(Format([EnterDate],"Short Date")), Reminders.EnterUserId
HAVING (((CDate(Format([EnterDate],"Short Date"))) Between [enter begin date] And [Enter End Date]));

This method does not work. I assume that I am doing something wrong. The query is still pulling date from previous years even though I am asking for 2015 data only. It is also pulling some data that falls outside the date range. This is the first time that happen. For example, I am querying 1/1/2015 to 4/17/2015 and it is pulling some data from 10/22/2014. I assume the date values are still strings???

[CJ_London]

The query is still not working correctly.

what does this mean? The only thing I can see is your query is asking for three dates [EnterDate], [Enter begin Date] and [Enter end Date]

[KurtW]

Ok, Let me start over. I apologize I am not the best at writing these queries. I am trying to run a query based on the below record from a SQL database using Microsoft Access. The first column is user IDs named "EnterUserID". The second column is “EnterDate” a date with a time. When I look at the properties of the table in Microsoft Access it is a date/time.

My ultimate goal is to run a query that tells me how many times different users created a specific event. In this case, the specific event is a date/time. I am attempting to remove the time from the query, so that only a date is seen. Once I do that I simply need to count the user ids over a given time period.

I am attempting convert the date/time to just a date (mm/dd/yyyy)

Sample Data in the table

EnterUserID EnterDate
KCSCONV 2/16/2015 7:44:26 PM

Below is the query in Microsoft Access



The query successfully changes all values in the “EnterDate” column to the proper date. In this case the date becomes ‘2/16/2015’. The new column value is “NewDate”. I can confirm this if I remove ‘Between [enter begin date] and [Enter End Date] because the date now looks like the following:

EnterUserID NewDate
KCSCONV 2/16/2015

If I add the criteria Between [enter begin date] and [Enter End Date] and enter a date range of 1/1/15 and 4/15/15 I get the following values:

NewDate	EnterUserId
12/12/2011	USER1
12/14/2011	USER1
12/15/2011	USER1
12/16/2011	USER2
12/19/2011	USER3
12/20/2011	USER4
12/20/2011	USER5
12/21/2011	USER4

It is clearly pulling dates outside the 1/1/15 and 4/15/15 parameters and I do not know why.

Kurt

[June7]

[EnterDate] is field in table.

I don't see anything wrong with query. CDate should allow the value to be treated as a real date. The syntax works for me. In fact, works for me without CDate().

Provide db for analysis, follow instructions at bottom of my post.

[KurtW]

When I directly open the table I am querying from, I get #deleted for every value in the table. I was instructed to create a pass-through query to get around this problem. To see the table in Access, I created a query that is the following:

select * from tblCsEventReminders

Technically my query references the above query. Could that be causing the problem? Now that I think about it, I had a similar problem with another query I tried writing a few years back.

I just did a search on google an it appears that Access is viewing it as a string and not a date. I am looking for any way around the problem.

Kurt

[June7]

What is the actual data source - SQL? I have limited experience with SQL db and never had to use pass-through query. Only set links to SQL tables. So yes, that could be problem.

[KurtW]

Yes, the actual source is a SQL database.

[June7]

If CDate does not work, I have no idea how to deal with.

[CJ_London]

only other suggestion I have is to move the criteria to another column - so group by newdate as you are doing but have another column, with your newdate formula and the criteria but change the group by to 'where'

[orange]

You are running a SELECT query and yet you say

The query successfully changes all values in the “EnterDate” column to the proper date. In this case the date becomes ‘2/16/2015’. The new column value is “NewDate”.

When you say SQL -- that's a language with several variants depending on the underlying database management system. I think you are referring to M$oft SQL Server, but that isn't explicit in your post.

In plain English, what are you trying to accomplish?
What exactly have you tried, and what exactly was the result?

Have you tried creating some simpler queries to get a "good handle" on queries and parameters? And the gradually add complexity?