Results 1 to 4 of 4

Thread: SQL in VBA

  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unhappy Unanswered: SQL in VBA

    I have this statement:

    strSQL4 = "SELECT [Main].*, [main].Division, [Main].Age, [Main].Type, * FROM [Main] WHERE((([Main].Division)=" & Division(i) & " AND (([Main].[Age] >29 AND (([Main].[Type] =INV Or ([Main].[Type] =CRE));"

    Set rs = db.OpenRecordset(strSQL4, 2)



    I keep getting an error when i run it. The error states that the format or puncuation maybe wrong in my SQL. I ran this SQL statement in a regular query and it worked. In the code i have the Division set to an array of numbers (i). I have used this line in previous code and I know it works. The only difference in this line is the WHERE statement. I know the format gets tricky and I'm sure i have it wrong. Can anyone see an error in my WHERE statement?

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    A quick paranthesis count seems to indicate you're missing a few closing para's there... Get the query working, with the where clause, in a normal query first. I've got a dollar on paranthesis shenanigans.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Jul 2004
    Location
    Blackburn, UK
    Posts
    169
    I dont know if it would make a difference but i would write the query as follows
    Code:
    strSQL4 = "SELECT [Main].*, [main].Division, [Main].Age, [Main].Type " & _ FROM [Main] " & _
    "WHERE ((([Main].Division)=" & Division(i) & AND ([Main].[Age]) > (29) " & _
    AND ([Main].[Type]) = 'INV' " & _
    Or ([Main].[Type]) =('CRE')));"
    You had too many open brackets and you need to enclose text in " ' " as it is a string
    Based on DAO 3.6 and Access 2000 + 2003
    VB.Net 2005 + ADO.Net

  4. #4
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    thanks! I got it to work

Posting Permissions

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