VBA Code for SQL statement with variables

[Renate]

I have a SQL statement which I would like to rebuild from within VBA to
use different fieldnames and the content of variables.

This is the SQL:

'TRANSFORM Nz(Count([ReportNo]),0) AS TotalNumber
'SELECT Year([DateField]) AS Year
'FROM Table
'GROUP BY Year([DateField])
'PIVOT Month([DateField]) In
('1','2','3','4','5','6','7','8','9','10','11','12');

The name of the table (Table) and of the Datefield (Datefield) should
be populated with the content of variables.

This is my VBA code so far:

sSQL = "TRANSFORM Nz(Count([ReportNo]),0) AS TotalNumber"
sSQL = sSQL & " ,SELECT Year([" & sDateField & "]) AS Year"
sSQL = sSQL & " ,FROM " & sTabel
sSQL = sSQL & " ,GROUP BY Year([" & sDateField & "])"
sSQL = sSQL & " ,PIVOT Month([" & sDateField & "]) In (" & Chr(34) &
"1" & Chr(34) & ", " & Chr(34) & "2" & Chr(34) & ", " & Chr(34) & "3" &
Chr(34) & ", " & Chr(34) & "4 ," & Chr(34) & "5" & Chr(34) & ", " &
Chr(34) & "6" & Chr(34) & ", " & Chr(34) & "7" & Chr(34) & ", " &
Chr(34) & "8, " & Chr(34) & "9" & Chr(34) & ", " & Chr(34) & "10" &
Chr(34) & ", " & Chr(34) & "11" & Chr(34) & ", " & Chr(34) & "12)"

The variables sTabel and sDateField contain the names of table and
datefield I want to use.

I can't get this ssql to run. Can somebody help me please?

 

[Smartin]

I have a SQL statement which I would like to rebuild from within VBA to
use different fieldnames and the content of variables.

This is the SQL:

'TRANSFORM Nz(Count([ReportNo]),0) AS TotalNumber
'SELECT Year([DateField]) AS Year
'FROM Table
'GROUP BY Year([DateField])
'PIVOT Month([DateField]) In
('1','2','3','4','5','6','7','8','9','10','11','12');

The name of the table (Table) and of the Datefield (Datefield) should
be populated with the content of variables.

This is my VBA code so far:

sSQL = "TRANSFORM Nz(Count([ReportNo]),0) AS TotalNumber"
sSQL = sSQL & " ,SELECT Year([" & sDateField & "]) AS Year"
sSQL = sSQL & " ,FROM " & sTabel
sSQL = sSQL & " ,GROUP BY Year([" & sDateField & "])"
sSQL = sSQL & " ,PIVOT Month([" & sDateField & "]) In (" & Chr(34) &
"1" & Chr(34) & ", " & Chr(34) & "2" & Chr(34) & ", " & Chr(34) & "3" &
Chr(34) & ", " & Chr(34) & "4 ," & Chr(34) & "5" & Chr(34) & ", " &
Chr(34) & "6" & Chr(34) & ", " & Chr(34) & "7" & Chr(34) & ", " &
Chr(34) & "8, " & Chr(34) & "9" & Chr(34) & ", " & Chr(34) & "10" &
Chr(34) & ", " & Chr(34) & "11" & Chr(34) & ", " & Chr(34) & "12)"

The variables sTabel and sDateField contain the names of table and
datefield I want to use.

I can't get this ssql to run. Can somebody help me please?

Let's debug.
1) Since you're doing this in VBA, there's no need to write out all
those messy Chr(34) & etc. Put it in a string, just like you did in the
SQL example at the top like
sSQL = sSQL & "In ('1','2','3',....)"
2) You omitted a closing ' after & "12
3) Debug.Print the final sSQL and set a break point. Take a good long
stare at it and see if there are other anomalies.

HTH

 

[John Spencer]

You have commas in lines that should not have them (lines 2,3,4,and 5 in the
code below). Also, you can make life a little easier by change the Pivot
clause to make it more readable.

sSQL = "TRANSFORM Nz(Count([ReportNo]),0) AS TotalNumber"
sSQL = sSQL & " SELECT Year([" & sDateField & "]) AS Year"
sSQL = sSQL & " FROM [" & sTabel & "]"
sSQL = sSQL & " GROUP BY Year([" & sDateField & "])"
sSQL = sSQL & " PIVOT Month([" & sDateField & "]) In
('1','2','3','4','5','6','7','8','9','10','11',12)"

You could also use two quote marks around the 1,2,3, etc. Your method works
and I often use it

... in (""1"", ""2"", ""3"", ..., ""12"") "

Use Debug.Print sSQL to print a copy of the statement in the immediate
window to examine the result.

If the SQL looks good, but doesn't run, then copy and paste it into a new
query. That will usually give you better error messages and often point to
the exact point of failure.