Using DateAdd and then setting criteria on the DateAdd values

[mackyrm]

Hi,

I am trying to query on a DateAdd column that I am creating in a query. It is returning data but the dates don't match with the criteria I am trying to pull out of the database. I am getting results on all 'product one' entries.

I am running this in a VBA statement to dynamically create the query but testing on the SQL looks like the information below:

SELECT Contact.ContactName, Product.ProductName, Product.StartDate, DateAdd("h",1,[Product].[StartDate]) as DateAddField

FROM Contact INNER JOIN Product
ON Contact.ContactID = Product.ContactID

WHERE (((DateAdd("h",1,[Product].[StartDate]))>=1/1/2016) AND (([Product].[Productname]) Like '*Product One*'));

Can anyone advise on what I am missing?

Thanks.

 

[mackyrm]

I would just add that I have tried this in Access (with access tables) with # around the dates and it works correctly, but I am actually connected to a SQL Server backend in my live database, and it doesn't work there. Adding # in the search criteria gives 0 results back.

 

[Minty]

If you are using this in VBA then you will need # around the dates, and your dates must be formatted as #mm/dd/yyyy# . SQL linked tables behave exactly the same way.

 

[mackyrm]

Thanks for the reply Minty. I can't even get this working in an access query at the moment connected to SQL Server.

The VBA takes into consideration the date formatting and has the # symbols around the dates

e.g.
mySQL = mySQL + vbCrLf & " (DateAdd(""h"",1,[product].[startdate]))>=#" & format(txtFrom, "mm/dd/yyyy") & "#)

 

[Minty]

Okay you'll need to use single quotes around the 'h'.

If you add a debug.print mySQL , you should see the exact string that is being attempted to run.

Edit - lose the vbcrlf as well - it's redundant in this use.

 

[mackyrm]

I'm getting a syntax error with single quotes.

 

[jdraw]

remove this + vbCrLf

then show us the result of Debug.print MySQL

 

[mackyrm]

Thanks Minty and Jdraw, here is the output from the debug mysql on the where statement

WHERE (
((Product.Name) Like '*Product*') AND ((DateAdd("h",1,[Product].[startdate]))>=#08/01/2016#) )


(date is greater than 1st August - referencing SQL Server dates and these are in the US date format)

 

[jdraw]

You are adding 1 hour to Startdate... Is that what you want?
What are you trying to do in plain English?

 

[mackyrm]

Apologies for the delay. What I am trying to do is create a query in Access, linked to an SQL server back end. The date is changed due to BST in SQL Server (I am unable to change how dates are stored in the original database). By one hour.

01/06/2016 in the original application using SQL Server as a back end reports in Access as 31/05/2016 23:00:00

I would like to use SQL Server data, using MS Access and dynamically create a query from a form that allows different dates to be entered. I have tried amending the date on a master query in Access - as an additional column with a one hour addition (taking it in to the next day), and querying on that, but I am unable to do it. In a query on a query, it doesn't recognise the calculated field and I cannot operate on it.

Some records that have been entered previously by another mechanism are correct dates but the addition of an hour to these would pose no issue on reporting.

 

[jdraw]

??? If you are at 1 June 2016, and move back ( subtract 1 hour) you would have 31 May 2016 at 23:00:00.

Need more details on table design and data types of fields involved.
With an example of what isn't working or requires entry by some other means.

I am not a SQL server user.

 

[mackyrm]

Thanks for the reply.

If you look at the first post, it provides an example of a contact > product relationship that I recreated in Access (to test in). One to many.

The fields are date fields.

 

[Minty]

The date is changed due to BST in SQL Server (I am unable to change how dates are stored in the original database). By one hour.

01/06/2016 in the original application using SQL Server as a back end reports in Access as 31/05/2016 23:00:00

Are you sure about this. If all your data is held in the SQL Server then your dates and times will be relative to it's time not yours.
If you Enter 01/01/2016 13:01 in your local Access database, it will store exactly that in your sql server.

 

[mackyrm]

Hi Minty,

Yes, the Access database is read only access. We are only reporting from it, not populating it.

The application that uses the SQL Server to populate it, accounts for this time differential. I do not have access to change that application.

 

[Minty]

Okay - I'm still perplexed by the need to change things, as the databases record of the date / time will always be correct for when / where the record was saved, unless it is in a different time zone, and the datetime is being set and recorded by the SQL Server in that time zone.

You will also have to deal with the fact that when the summer time / GMT switch happens it happens at different dates in different countries.

You definitely should be able to run a second query based on the first query with the calculation in it. Can you post the two queries you tried ?
Are the dates in SQL server stored as DateTime or Date fields?

 

[mackyrm]

Thanks Minty. It must be date and time I guess behind the interface for the field, if it recording it as such, but the form is asking only for the date. I suspect the system is defaulting the date time to [date] 00:00:00 (I can't put in the time). In the CRM it is visible as the correct date in reports and on forms. In Access, looking at the SQL Server behind CRM, in BST, it takes 00:00:00 back one hour to the repvios day. I can accommodate for that in the vba with a function. I can't seem to get the VBA shown in the first post working though.

Regarding query on query (another way of tackling it), I haven't articulated that issue particularly well, my error. I am able to build a query with a calculated field in the parent query, but I appear to not be able to calculate on the calculated field in the second query (child query) e.g. the SQL below for the child query shows the date as is, the StartDate1H as the calculated field with the one hour added in the original parent query. It doesn't work - I get the headings but no content.

SELECT qryBaseBookingModules.startdate, qryBaseBookingModules.StartDate1H
FROM qryBaseBookingModules
WHERE (((qryBaseBookingModules.StartDate1H)>#7/31/2016#));

Thanks again for looking at this.

 

[Minty]

I don't think you would be able to further calculate on a calculated field but you can apply criteria to it in the second query.

If there is no time portion of the date then yes it will default to 00:00:00. And if all the dates are stored that way, then there is no point in removing one hour from them as they will all move back to the previous day.

Could you post up some sample data possibly in a spreadsheet, with what you are trying to get to?

 

[mackyrm]

What I am trying to achieve is the addition of one hour Minty (SQL is taking them one hour back from 00:00:00 to 23:00:00 the previous day, in BST). I can make the 1 hour addition in a query and bring the date back to what it is supposed to be in reporting, but they are not searchable in the second query using criteria like >01/01/2016. I want to be able to search and filter on dates after the 1 hour has been added. Does that make sense?

 

[Minty]

Why not take an hour off your criteria rather than the data?