Originally Posted by
aytee111
What is the SQL of the query? It needs the full statement in order to run. "SELECT ...... FROM...."
Code:
Public Function SQLSTR(gp As String, _
mnth As String) As String
Yr = Left(gp, 1)
mnth = Left(mnth, 3)
Debug.Print (gp & " " & Yr & " " & mnth)
string1 = "[Lower_School_Students].[First name], [Lower_School_Students].[Last name], [Lower_School_Students].[Mathematics :Group(s)], "
string2 = "results.[" & Yr & "p1" & mnth & "], results.[" & Yr & "p2" & mnth & "], results.[" & Yr & "MA" & mnth & "], results.[" & Yr & "" & mnth & "RAW] "
SQLSTR = "SELECT " & string1 & string2 & " FROM [Lower_School_Students] LEFT JOIN results ON [Lower_School_Students].UPN = results.upn " _
& "WHERE ((([Lower_School_Students].[Mathematics :Group(s)]) = " & Chr(34) & gp & Chr(34) & ")) ORDER BY [Lower_School_Students].[Mathematics :Group(s)],[Lower_School_Students].[Last name];"
Debug.Print (SQLSTR)
End Function
I've tested this and it returns
Code:
SELECT [Lower_School_Students].[First name], [Lower_School_Students].[Last name], [Lower_School_Students].[Mathematics :Group(s)], results.[7p1Nov], results.[7p2Nov], results.[7MANov], results.[7NovRAW] FROM [Lower_School_Students] LEFT JOIN results ON [Lower_School_Students].UPN = results.upn WHERE ((([Lower_School_Students].[Mathematics :Group(s)]) = "7jk/Mm4")) ORDER BY [Lower_School_Students].[Mathematics :Group(s)],[Lower_School_Students].[Last name];
which produces the datasheet I need when I put it directly into an SQL
My apologies if it reads a bit bad but this is still a bit new to me