Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2006
    Posts
    11

    Unanswered: Help with building a query using SQL code

    Hello,
    I have this code that I have used before for other things and have worked properly but for this case it isn't working. Here is the code that is giving me a syntax error:

    strSQL = "SELECT qryViewStats.* " & _
    "FROM qryViewStats " & _
    "WHERE qryViewStats.School" & strSchool & _
    "AND qryViewStats.State" & strState & _
    "AND qryViewStats.VA Division" & strDivision & _
    "AND qryViewStats.VA Region" & strRegion & _
    "AND qryViewStats.VA District" & strDistrict & _
    "AND qryViewStats.WV Class" & strClass & _
    "ORDER BY qryViewStats.State, qryViewStats.School;"

    My guess is that it doesn't like the Lines:
    "AND qryViewStats.VA Division" & strDivision & _
    "AND qryViewStats.VA Region" & strRegion & _
    "AND qryViewStats.VA District" & strDistrict & _
    "AND qryViewStats.WV Class" & strClass & _
    becuase the names have spaces in them. I want to know if that is the problem becuase I would have to fix that at the table level first and then fix all references to the old names.

    Any help is appreciated!

    Thanks,
    Chuck

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    assuming you are using JET, the default data engine in Access then enclose your column names in square brackets

    strSQL = "SELECT qryViewStats.* " & _
    "FROM qryViewStats " & _
    "WHERE School" & strSchool & _
    " AND State" & strState & _
    " AND [VA Division]" & strDivision & _
    " AND [VA Region]" & strRegion & _
    " AND [VA District]" & strDistrict & _
    " AND [WV Class]" & strClass & _
    " ORDER BY State, School;"

    assuming that you have only numeric data in the columns called str.....
    if they contain non numeric data then they need to be prefixed by double quote / speech marks
    your sql needed a space between the 'AND's and the preceeding column value

    incidentally you don't need to sepcify the table / query name unless there are duplicate column names in the tables, so the "qryViewStats." is redundant

    if you wanted to fully qualify the table name for JET consider using
    [qryViewStats].[VA District]

    personally i find it quite usefull to check the SQL if you print (using a msg box or debug just to see what the SQL is, as opposed to what you think you have typed. On far to many times the code left my braincell(s) correct (obviously) but arrive at my fingertips mangled. - just a thought, save hours swearing at something computers, and placates the locals.
    HTH

  3. #3
    Join Date
    Jan 2006
    Posts
    11
    Thanks healdem for your help,
    I made a copy of my database and tried just renaming the fields in my table with the spaces and luckily it transferred those changes to all of my forms, queries that I had so the change was painless. It also made the code that I had work.

    Thanks,
    Chuck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •