Type Mismatch on VBA SQL - I think its the date?

S

shabbaranks

Registered User.
Local time
Today, 15:01
Joined
Oct 17, 2011
Messages
300
Hi guys, yep me again...

How do you show dates within SQL VBA? As Ive been struggling with the below code and cant for the life of me get it to work. Im sure its the date that's incorrect and Ive tried putting #'s in but still it errors with type mismatch. Could someone enlighten me please?

Code: 
strSQL = "SELECT TimesheetTable.ProjectRef, TimesheetTable.sUser, Sum(TimesheetTable.Hours) AS SumofHours, Month([task date]) AS [Month], Year([task date]) AS [Year]" & _
"FROM (UserNames_tbl INNER JOIN UserGroups_tbl ON UserNames_tbl.GroupID = UserGroups_tbl.GroupID) INNER JOIN (TimesheetTable INNER JOIN ProjectsTable ON TimesheetTable.ProjectRef = ProjectsTable.REFERENCE) ON UserNames_tbl.sUser = TimesheetTable.sUser" & _
"GROUP BY TimesheetTable.ProjectRef, TimesheetTable.sUser, Month([task date]), Year([task date]), Format([Task Date],'yyyymm', ProjectsTable.NAME2, UserNames_tbl.GroupID, UserNames_tbl.GroupID" & _
"HAVING (((TimesheetTable.ProjectRef) Not Like '" & CENG & "*') AND ((Month([task date]))=[Forms]![ChargeableHours]![MonthCombo]) AND ((Year([task date]))=[Forms]![ChargeableHours]![YearCombo]) AND ((UserNames_tbl.GroupID)<>7 And (UserNames_tbl.GroupID)<>8))" & _
"ORDER BY TimesheetTable.ProjectRef, TimesheetTable.sUser, Format([Task Date],'yyyymm');"
Me.RC_ListBox.RowSourceType = "Table/Query"
Me.RC_ListBox.ColumnCount = "5"
Me.RC_ListBox.ColumnWidth = "4cm;4cm;4cm;4cm;4cm"
Me.RC_ListBox.RowSource = strSQL

Thanks
 
When referencing form controls on VBA SQL, you need to concatenate them. You cannot use them as you can in a Query. General idea..
Code: 
[COLOR=Green]'For Numbers[/COLOR]
...WHERE someNumberField = [B][COLOR=Red]" &[/COLOR] Forms!YourFormName!YourControl [COLOR=Red]& "[/COLOR][/B] AND....
[COLOR=Green]'For String[/COLOR]
...WHERE someTextField = [COLOR=Red][B]""[/B][/COLOR][B]"[/B] [B]& Forms!YourFormName!YourControl &[/B] [B]"[/B][COLOR=Red][B]""[/B][/COLOR] AND....
[COLOR=Green]'Or[/COLOR]
...WHERE someTextField = [B][COLOR=Red]'[/COLOR]" & Forms!YourFormName!YourControl & "[COLOR=Red]'[/COLOR][/B] AND....
[COLOR=Green]'Or[/COLOR]
...WHERE someTextField = [B]" [/B][B]& [/B][B][COLOR=Red]Chr(34)[/COLOR] [COLOR=Red]&[/COLOR] Forms!YourFormName!YourControl [COLOR=Red]& Chr(34)[/COLOR] & "[/B] AND....
[COLOR=Green]'For Dates[/COLOR]
...WHERE someDateField = [B]" & [COLOR=Red]Format([/COLOR]Forms!YourFormName!YourControl, [COLOR=Red]"\#mm\/dd\/yyyy\#")[/COLOR] & "[/B] AND....
If the Form is the same as where the VBA SQL is run you can replace,
Code: 
...WHERE someNumberField = " & [COLOR=Red][B]Forms!YourFormName![/B][/COLOR]YourControl & " AND....
To
Code: 
...WHERE someNumberField = " & [COLOR=Red][B]Me.[/B][/COLOR]YourControl & " AND....
 
pr2-eugin said:
When referencing form controls on VBA SQL, you need to concatenate them. You cannot use them as you can in a Query. General idea..
Code: 
[COLOR=green]'For Numbers[/COLOR]
...WHERE someNumberField = [B][COLOR=red]" &[/COLOR] Forms!YourFormName!YourControl [COLOR=red]& "[/COLOR][/B] AND....
[COLOR=green]'For String[/COLOR]
...WHERE someTextField = [COLOR=red][B]""[/B][/COLOR][B]"[/B] [B]& Forms!YourFormName!YourControl &[/B] [B]"[/B][COLOR=red][B]""[/B][/COLOR] AND....
[COLOR=green]'Or[/COLOR]
...WHERE someTextField = [B][COLOR=red]'[/COLOR]" & Forms!YourFormName!YourControl & "[COLOR=red]'[/COLOR][/B] AND....
[COLOR=green]'Or[/COLOR]
...WHERE someTextField = [B]" [/B][B]& [/B][B][COLOR=red]Chr(34)[/COLOR] [COLOR=red]&[/COLOR] Forms!YourFormName!YourControl [COLOR=red]& Chr(34)[/COLOR] & "[/B] AND....
[COLOR=green]'For Dates[/COLOR]
...WHERE someDateField = [B]" & [COLOR=red]Format([/COLOR]Forms!YourFormName!YourControl, [COLOR=red]"\#mm\/dd\/yyyy\#")[/COLOR] & "[/B] AND....
If the Form is the same as where the VBA SQL is run you can replace,
Code: 
...WHERE someNumberField = " & [COLOR=red][B]Forms!YourFormName![/B][/COLOR]YourControl & " AND....
To
Code: 
...WHERE someNumberField = " & [COLOR=red][B]Me.[/B][/COLOR]YourControl & " AND....
Click to expand...

Thanks, I thought I could take the code as per the QBE - obviously not. Do I need to remove all the brackets that get added to the query or can they stay?
 
Thanks for that.

Im still getting mismatch errors and am slightly bamboozled. Although fields within my table reference date for example 12 = December and 2013 reference the year, do I need to reference them within the SQL as dates with #'s?

Also with your example

Code: 
WHERE someDateField = [B]" & [COLOR=red]Format([/COLOR]Forms!YourFormName!YourControl, [COLOR=red]"\#mm\/dd\/yyyy\#")[/COLOR] & "[/B] AND....

You've got both forward and back slashes - Im only referencing the year and month, do I need both slashers?

Cheers
 
What is the Datatype of the Field you are trying to reference? By the looks of it, I think it is Text and not Dates.
 
Again thanks I appreciate your help. The bits that are pink reference number values from the form (the number values make up the date which references a date value on the table ) the blue bit references the project reference stored within the table which is stored as text and the green bit references the date stored within the table which is obviously stored as a date.

The reason the date is separate is because instead of selecting a date I wanted to be able to select a month and a year independently and then lookup the values from the tables based on this.


Code: 
strSQL = "SELECT TimesheetTable.ProjectRef, TimesheetTable.sUser, Sum(TimesheetTable.Hours) AS SumofHours, Month([task date]) AS [Month], Year([task date]) AS [Year]" & _
"FROM (UserNames_tbl INNER JOIN UserGroups_tbl ON UserNames_tbl.GroupID = UserGroups_tbl.GroupID) INNER JOIN (TimesheetTable INNER JOIN ProjectsTable ON TimesheetTable.ProjectRef = ProjectsTable.REFERENCE) ON UserNames_tbl.sUser = TimesheetTable.sUser" & _
"GROUP BY TimesheetTable.ProjectRef, TimesheetTable.sUser, Month([task date]), Year([task date]), Format([Task Date],[COLOR=yellowgreen]'yyyymm'[/COLOR], ProjectsTable.NAME2, UserNames_tbl.GroupID, UserNames_tbl.GroupID" & _
[U]"HAVING (((TimesheetTable.ProjectRef) Not Like [COLOR=blue]""" & CENG & "" * ")[/COLOR] AND ((Month([task date]))= [COLOR=magenta]" & Me.MonthCombo & " [/COLOR]AND ((Year([task date]))= [COLOR=magenta]" & Me.CHYear_tb & "[/COLOR] AND ((UserNames_tbl.GroupID)<>7 And (UserNames_tbl.GroupID)<>8))" & _
"ORDER BY TimesheetTable.ProjectRef, TimesheetTable.sUser, Format([Task Date], [COLOR=magenta]" & yyyymm & "[/COLOR]);"
[/U]
 
Got it to work....

Code: 
strSql = "SELECT TimesheetTable.ProjectRef, TimesheetTable.sUser, Sum(TimesheetTable.Hours) AS SumofHours, Month([task date]) AS Expr1, Year([task date]) AS [Year] " & vbCrLf & _
"FROM (UserNames_tbl INNER JOIN UserGroups_tbl ON UserNames_tbl.GroupID = UserGroups_tbl.GroupID) INNER JOIN (TimesheetTable INNER JOIN ProjectsTable ON TimesheetTable.ProjectRef = ProjectsTable.REFERENCE) ON UserNames_tbl.sUser = TimesheetTable.sUser " & vbCrLf & _
"GROUP BY TimesheetTable.ProjectRef, TimesheetTable.sUser, Month([task date]), Year([task date]), Format([Task Date],""yyyymm""), ProjectsTable.NAME2, UserNames_tbl.GroupID, UserNames_tbl.GroupID " & vbCrLf & _
"HAVING (((TimesheetTable.ProjectRef) Not Like ""CENG*"") AND ((Month([task date]))=[Forms]![ChargeableHours]![MonthCombo]) AND ((Year([task date]))=[Forms]![ChargeableHours]![CHYear_tb]) AND ((UserNames_tbl.GroupID)<>7 And (UserNames_tbl.GroupID)<>8)) " & vbCrLf & _
"ORDER BY TimesheetTable.ProjectRef, TimesheetTable.sUser, Format([Task Date],""yyyymm"");"

A handy tool I found was here (converts the Access sql to VBA SQL)

http://allenbrowne.com/ser-71.html
 
You must log in or register to reply here.

Similar threads

C
Getting a Data Type Mismatch error... I can't see why
Replies
10
Views
1,898
gemma-the-husky
gemma-the-husky

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom