Problem using a calculated date field in parameter query

[toveyj]

Hi

I have a SQL table with a Datetime format field in it (create_date). I have linked to this table in Access and created a query which includes a field that is the date only part of this field. I want the date only as the output which is then exported to EXCEL. It uses this formula to convert it as some records are NULL.

Created_dt: IIf(Not IsDate([create_date]),Null,Int([create_date]))

I then use this query (with the Created_dt field in it) in another query with a parameter to select the end of the date range that I want to select.

WHERE dbo.Created_dt Between #4/1/2015# And [End Date];

When the parameter for [End Date] is filled in the query only returns records for each month where the day is <= the day entered in the parameter. So for instance if I type in 06/08/2016 I will only get records for every month from 1st to 6th of the month. None of the rows where the Created_dt is 7th-31st for any month are selected. However, if I remove the parameter and put in an actual end date of 06/08/2016 then all the records from 7th-31st for other months are returned!

Any ideas how I can retain the parameter and overcome this problem? I am guessing it may be something to do with using 'Int' to convert the dates so is there a better way to convert them that won't cause this problem? I can't change the format of the data on SQL server as it isn't controlled by me.

 

[Trevor G]

In the parameter try typing the date in full rather than in 2 digit format.

 

[toveyj]

Thanks for taking the time to reply. I am typing it in full as above i.e "06/08/2016"

 

[Trevor G]

As suggested try using full date, 06 August 2016 based on your example.

 

[toveyj]

Interestingly that only returns records for each month up to 5th rather than 6th of each month. So slightly worse!

 

[Trevor G]

Is the date in US format?

 

[toveyj]

Not sure what you are asking there. It is originally from a SQL table so stored in the format
yyyy-mm-dd in that table. If I manually put the date into the query in the design grid as
dd/mm/yyyy it automatically transforms that to US format in the SQL and that returns the
correct results. No idea what Access is doing to what I put in as a parameter though when
it translates that into SQL.