DATEPART "Q" Error

[freightguy]

Hi everyone. Need assistance with a query using DATEPART.

When I use the Query builder the query is as such:

SELECT tblShipment.expID, tblShipment.SRDate, tblShipment.ProjectNumber
FROM tblShipment
WHERE (((Year([srdate]))=2018) AND ((DatePart("q",[SRDate]))=3));

Above returns all records with a start date in 2018 and in Q3 - works perfectly.

When I convert that to VBA as follows I get a Expected End Statement Error during compile:

sqlQuery = "Select AVG([MBDBD]-[SRdATE]) as AvgShipDelTime from tblShipment WHERE (((Year([srdate]))=2018) AND ((DatePart("q",[SRDate]))=3))"

When I get the error it highlights the Q in the DATERPART. I tried QUARTER but that didn't work either. Does VBA use something else rather than Q for Quarter?

thank you

 

[The_Doc_Man]

The problem is quoting characters inside a quoted string.

Try using DatePart('q',[SRDate]...

 

[freightguy]

Awesome! Thank you.

 

[Galaxiom]

SELECT tblShipment.expID, tblShipment.SRDate, tblShipment.ProjectNumber
FROM tblShipment
WHERE (((Year([srdate]))=2018) AND ((DatePart("q",[SRDate]))=3));

That is a very inefficient query because it must retrieve every record and apply the functions before it can select. You won't notice at first but as the number of records increases the query become slower and slower.

Define the date range in the WHERE clause as

WHERE SRDate BETWEEN #7/1/2018# AND #9/30/2018#

and allow the engine to select using an index on the SRDate field. This is orders of magnitude faster.

Note you will have to adjust accordingly if SRDate has a Time component.

Also note that dynamic queries built in VBA are less efficient than parameterised queries.