SQL Syntax (Error3075) (1 Viewer)

[hascons]

I'm having a problem with the proper syntax for a query Statement and I'm getting An Error 3075 Syntax Error.


Sql2 = "SELECT WeeklyPicks.GameID, WeeklyPicks.Week, WeeklyPicks.Username, WeeklyPicks.Pick," & _
"qryCalculateScores.Winner, WeeklyPicks.TieBreakerPoints" & _

"FROM WeeklyPicks INNER JOIN qryCalculateScores ON" & _
"(WeeklyPicks.Pick = qryCalculateScores.Winner) AND (WeeklyPicks.GameID = qryCalculateScores.GameID)" & _

"WHERE (((WeeklyPicks.Week) =" & WeekNum & ") And ((WeeklyPicks.UserName) ='" & CurrentUser & "'))" & _

"ORDER BY WeeklyPicks.Username;"

WeekNum is an Integer
CurrentUser is Text

I'm not sure if there are any apostrophe's required in the join statements. I've tried to add spaces here to separate each section of the query to make it easier to read.

hascons

 

[JANR]

If you Debug.Print Sql2 the you might see the problem, a small hint is SPACING around keyword like FORM, ON,WHERE...

JR

 

[jdraw]

I'm having a problem with the proper syntax for a query Statement and I'm getting An Error 3075 Syntax Error.


Sql2 = "SELECT WeeklyPicks.GameID, WeeklyPicks.Week, WeeklyPicks.Username, WeeklyPicks.Pick," & _
"qryCalculateScores.Winner, WeeklyPicks.TieBreakerPoints" & _

"FROM WeeklyPicks INNER JOIN qryCalculateScores ON" & _
"(WeeklyPicks.Pick = qryCalculateScores.Winner) AND (WeeklyPicks.GameID = qryCalculateScores.GameID)" & _

"WHERE (((WeeklyPicks.Week) =" & WeekNum & ") And ((WeeklyPicks.UserName) ='" & CurrentUser & "'))" & _

"ORDER BY WeeklyPicks.Username;"

WeekNum is an Integer
CurrentUser is Text

I'm not sure if there are any apostrophe's required in the join statements. I've tried to add spaces here to separate each section of the query to make it easier to read.

hascons

Your syntax issue is related to " you need a space before and after keywords such as 'FROM' 'ORDER BY' 'WHERE' 'HAVING' etc.

You may find that, when creating SQL strings in vba, aligning quotes on the left and starting with a space may help reduce syntax issues.

sql2 = "SELECT WeeklyPicks.GameID, WeeklyPicks.Week, WeeklyPicks.Username, " & _
" WeeklyPicks.Pick, qryCalculateScores.Winner, WeeklyPicks.TieBreakerPoints" & _
" FROM WeeklyPicks INNER JOIN qryCalculateScores ON " & _
" (WeeklyPicks.Pick = qryCalculateScores.Winner) AND (WeeklyPicks.GameID = qryCalculateScores.GameID)" & _
" WHERE (((WeeklyPicks.Week) =" & WeekNum & ") And ((WeeklyPicks.UserName) ='" & CurrentUser & "'))" & _
" ORDER BY WeeklyPicks.Username;"

 

[hascons]

Thanks JanR and JDraw

I 'm thankful for both of tips you gave me.
I do struggle with writing Longer SQL statements and you both have given me great tips for dealing with them.

hascons