VBA SQL Datevalue Problem

Hello, first post on this forum so apologies for any problems/errors.


i am using Access as a database and Excel as a frontend of types to process data and create a report. i'm currently using a user entered date for the dates. however, i would like to use a date created field instead. my issue is the date created is a date/time instead of just a date which is throwing my code off. here is the code:

varsql = "TRANSFORM  sum(iif([Lead].[" & tname & "]<>""Yes"",0,1))/count(*) AS FirstOfAttendance"
varsql2 = " SELECT Compo.Name"
varSQL3 = " FROM Lead   Right Outer Join  Compo ON [Lead].Consultant = Compo.Name"


varSQL4 = " WHERE ((Compo.[Team]) Like """ & team & """) "
varSQL5 = " GROUP BY Compo.Name ORDER BY Name"
varSQL6 = " PIVOT datevalue([Lead].[Date Created]) IN (""" & Range("C4").Value & """,""" & Range("D4").Value & """,""" & Range("E4").Value & """,""" & Range("F4").Value & """,""" & Range("G4").Value & """,""" & Range("H4").Value & """,""" & Range("I4").Value & """);"

C4:I4 are just dates based on a userform input.

This code looks to work on some sheets, not on others (resulting in a runtime of 3464). On other sheets it works perfectly and there are very few differences (only names are different).



i have no errors if i pivot [Lead].[Date] but i do if i pivot datevalue([Lead].[Date Created]) .

date is in format "mm/dd/yyyy". date created is in format "mm/dd/yyyy hh:mm:ss". if i try to pivot without datevalue on date created, i get no data.

any suggestions on how to get around this? the access database is populated from an excel sheet. i have this as a direct import from VBA so i don't need to open the spreadsheet. i'd like to keep it like this for time/resource reasons. i've tried to search this on multiple forums but coming up empty handed.


here is a snippet of the raw data from access:

Date	          Consultant	Team Manager	Lead1           Date Created      	Date Modified
9/26/2016	Agent Name	Manager	        Yes		9/26/2016 7:26:00 PM	09-26-2016 07:26 PM
9/26/2016	Agent Name	Manager		Yes		9/26/2016 7:20:00 PM	09-26-2016 07:20 PM
9/26/2016	Agent Name	Manager		Yes		9/26/2016 10:57:00 AM	09-26-2016 10:57 AM

Final output:

Agent Name	9/25/2016	9/26/2016	9/27/2016	9/28/2016	9/29/2016	9/30/2016	10/1/2016
Agent Name			100%			
Agent Name			95%

any help on this issue is greatly appreciated!

i tried INT(Lead.[Date Created]) but get a 3464 error, the same as if i used datevalue(Lead.[Date Created]).

i forgot to mention that when i use Lead.Date, everything works perfectly. if i use Lead.[Date Created] i get nothing but blanks.

Both Int(Lead.Date) and Lead.Date work perfectly.
Both DateValue(Lead.[Date Created]) and INT(Lead.[Date Created]) fail with runtime 3464
Lead.[Date Created] gives me blanks