Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2010
    Posts
    4

    Unanswered: script/ MS Access problem

    I have a VBscript that opens an MS Access DB creates a query. I keep getting a a Code: 80040E14 Syntax error (missing operator) in Query Expression 'Project Year' I was wondering if anyone could help me out. Let me know if there are any questions or if i wasnt clear enough. Here is the code i have:

    Dim COMPLETED_PROJECTS
    COMPLETED_PROJECTS = projectCount

    '' Check the year to see if the completed project should be included in the G&O document
    '' Dim sqlGetYear
    sqlGetYear = "SELECT Project Year FROM [Completed Projects];"
    cmd.CommandText = sqlGetYear
    Set yearRecordSet = CreateObject("ADODB.Recordset")
    Set yearRecordSet = cmd.Execute(sqlGetYear)
    yearRecordSet.MoveNext

    '' Depending on the year, add it to the G&O
    IF sqlGetYear > 2010 THEN
    sqlGetProjects = "SELECT Title FROM [Completed Projects];"
    cmd.CommandText = sqlGetProjects
    Set projectRecrodSet = CreateObject("ADODB.Recordset")
    Set projectRecordSet = cmd.Execute(sqlGetProjects)
    Do While Not projectRecordSet.EOF
    ReDim Preserve projects(projectCount)
    projects(projectCount) = "[" & cstr(projectRecordSet("Title")) & "]"
    projectCount = projectCount + 1
    projectRecordSet.MoveNext
    Loop
    END IF

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can't have a space in a column or table name without enclosing in square brackets [t name].
    BTW - always, always, always get your SQL working in the query view before putting into VBA. Also, when you have this error always, always, always print out the SQL string you are executing - 9 times out of 10 the error is obvious when you do this.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2010
    Posts
    4

    testing

    Since im new to this DB and scripting stuff, How do i run SQL quriues in MS Access 2007?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Go to create new query. You can create queries in the designer and then go to SQL view to see the SQL. You can also go straight to SQL view and enter your SQL there. Just execute to see the results. I would suggest using the designed initially while you find your feet.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jan 2010
    Posts
    4
    Im sorry for sounding completely like a noob, but how do i go straight to SQL view. based on what you say about the designer and everything it looks like my query works. By going into SQL view it wuld make sure my query works.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    When you are in the designer, there are buttons:
    Top left (big one - "View")
    Bottom right (little one - "SQL")
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2010
    Posts
    4

    working...kind of

    Ok, so i was able to get my sql query to work correctly. But for some reason i dont think im using the value correctly. If im using the value incorrectly, im not sure how to use it correctly. Here is the code:

    Dim COMPLETED_PROJECTS
    COMPLETED_PROJECTS = projectCount

    '' Check the year to see if the completed project should be included in the G&O document
    sqlGetYear = "SELECT [Project Year] FROM [Completed Projects];"
    cmd.CommandText = sqlGetYear
    Set yearRecordSet = CreateObject("ADODB.Recordset")
    Set yearRecordSet = cmd.Execute(sqlGetYear)

    '' Depending on the year, add it to the G&O
    IF sqlGetYear > 2009 THEN
    sqlGetProjects = "SELECT Title FROM [Completed Projects];"
    cmd.CommandText = sqlGetProjects
    Set projectRecrodSet = CreateObject("ADODB.Recordset")
    Set projectRecordSet = cmd.Execute(sqlGetProjects)
    Do While Not projectRecordSet.EOF
    ReDim Preserve projects(projectCount)
    projects(projectCount) = "[" & cstr(projectRecordSet("Title")) & "]"
    projectCount = projectCount + 1
    projectRecordSet.MoveNext
    Loop
    END IF

Posting Permissions

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