SQL query in Ms-access

Question

I got and error message when I am trying to implement this query. It is saying syntax error, can someone help me...

strLocalSql = "SELECT tblEventTypeList.EventType" & _
                  "FROM tblEventTypeList" & _
                  " WHERE tblEventTypeList.[EventPage] = " & ts & "" & _
                  "ORDER BY tblEventTypeList.[EventType]"";"

Answer 1

Looks like you need a space before ORDER in "ORDER BY ..." and before FROM (thanks hawbsl)

Extra whitespace doesn't hurt anything in your queries. It's easy to forget those spaces between lines. It might be worth getting in the habit of starting each line with a space, so that you avoid these situations:

strLocalSql = " SELECT tblEventTypeList.EventType" & _
              " FROM tblEventTypeList" & _
              " WHERE tblEventTypeList.[EventPage] = " & ts & _
              " ORDER BY tblEventTypeList.[EventType]"";"

Answer 2

Also, looks like there are some superfluous double quotes - at the end of line 4 and just before the last ampersand in line 3

Answer 3

Others have gotten parts of this, but I don't think anybody has gotten it all correct. It should be this:

  strLocalSql = "SELECT tblEventTypeList.EventType" & _
                  " FROM tblEventTypeList" & _
                  " WHERE tblEventTypeList.[EventPage] = " & ts & _
                  " ORDER BY tblEventTypeList.[EventType];"

This assumes that your EvenPage field is numeric (and that the value in the ts variable is numeric). If it's text, then you need this, instead:

  strLocalSql = "SELECT tblEventTypeList.EventType" & _
                  " FROM tblEventTypeList" & _
                  " WHERE tblEventTypeList.[EventPage] = " _
                   & Chr(34) & ts & Chr(34) & _
                  " ORDER BY tblEventTypeList.[EventType];"

Personally, I maintain in all my Access projects a constant defined thus:

  Global Const STR_QUOTE As String = """"

...and then I use that constant for concatenating. I wish I'd named it strQ instead, though, as the name is too long. But I've been using it for more than a decade so it's too hard for me to change! This would work:

  Global Const strQ As String = """"

  strLocalSql = "SELECT tblEventTypeList.EventType" & _
                  " FROM tblEventTypeList" & _
                  " WHERE tblEventTypeList.[EventPage] = " _
                   & strQ & ts & strQ & _
                  " ORDER BY tblEventTypeList.[EventType];"

...but of course, that's only correct if EvenPage is a string.