Results 1 to 9 of 9
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Unanswered: How to use VBA string in SQL statement

    I tried using a VBA string in SQL statement from within VBA but it wasn't liking it. Do I need to convert the VBA string to SQL? How would I do this?

    Here's what I tried but it did not recognize ResponseCWS1 in the SQL Statement.
    'ResponseCWS1 = API.ResponseText

    'Dim mySQL As String
    'DoCmd.SetWarnings False
    'mySQL = "INSERT INTO ReportIDLog_UnshippedOrder (ReportID, DateRan)"
    'mySQL = mySQL & " VALUES (" & ResponseCWS1& ", now())"

    'DoCmd****nSQL mySQL
    'DoCmd.SetWarnings True

    Thanks in advance for your help!
    Joshua
    Last edited by scrtchmstj; 08-17-09 at 17:03.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Hopefully there's really a space between the variable and the second ampersand? Have you stepped through the code to make sure the first line is getting the value correctly?
    Paul

  3. #3
    Join Date
    May 2005
    Posts
    1,191
    There's no such thing as "converting" a VBA string to SQL. SQL is fundamentally a string, you just do stuff with it (apply it to a control, run it, whatever). So no worries there.

    What is "ResponseCWS1"? Is it a variable? It looks like you're having a little trouble building a string in VBA, that is to say, what's a string and what's a variable, and how to put them together. I'd suggesst having a look at Martin Green's Lectures for help on this topic.

    Cheers!
    Me.Geek = True

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Dim strSQL As String
    DoCmd.SetWarnings False
    strSQL = "INSERT INTO ReportIDLog_UnshippedOrder (ReportID, DateRan)"
    strSQL = mySQL & " VALUES (" & ResponseCWS1 & "," & now() &")"

    if you are US based you may get away with that
    alternatively you will have to coerce the date into MM/DD/YYYY format
    I think you can also use ISO date YYYY/MM/DD

    strSQL = mySQL & " VALUES (" & ResponseCWS1 & ", " & chr(34) & format(now(),"MM/DD/YY#") & chr(34) &")"

    best thing to do in these sort of occasions is to examine the SQL to make certain that what you are sending is what you think you are.

    so either set a watch/break point or a msgbox(strSQL)
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    ResponseCWS1 is a response string from an XML Get Statement. I want to append it to a single record in a table and add the date ran as well.

    Joshua

  6. #6
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Ok, it looks like I just needed to add the single quote marks.

    Code:
    mySQL = mySQL & " VALUES ('" & ResponseCWS1 & "', now())"
    Now I need to write a code to pull the ResponseID (ResponseCWS1) from that table with the most recent date to include in my XML header. Any ideas what I would use? FindFirst? Sorry, I've been trying to learn VBA on my own but it's been slow going. Thanks, Joshua
    Last edited by scrtchmstj; 08-18-09 at 09:21.

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so the lesson form here is..

    non numeric columns need values to be encapsulated in either a " or ', or if you prefer use the equivalents chr$(34) or chr$(39)

    eg
    strWhereClaue = "where MyNumericColumn = 123"
    strWhereClaue = "where MyStringColumn = 'Blah-di-Blah'"
    strWhereClaue = "where MyStringColumn = " & chr$(34) & "Blah-di-Blah" & chr$(34)
    or as a variable
    strWhereClaue = "where MyNumericColumn = " & aNumericVaraible
    strWhereClaue = "where MyStringColumn = '" & aStringVariable & "'"
    strWhereClaue = "where MyStringColumn = " & chr$(34) & aStringVariable & chr$(34)
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Oct 2004
    Location
    Melbourne, Australia
    Posts
    201

    VBA and SQL

    I think the previous posts have just about covered your immediate problem. If you have more complex criteria or you need to combine data elements into one, you could write a DBA function which returns the end value. This can be called from within an SQL query as though it were a field. The general syntax is SELECT(... , MyFunction([Table].[Field1], [Table].[Field2]) As EndResult, ...)

    I have a client who sends out lots of generated mail and uses this technique to embed data in paragraphs and tables which are then placed in mail-merge documents. This is necessary because it is difficult to include multiple records or heavily modified data in the data source for a single letter.

  9. #9
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Cool Thanks!

Posting Permissions

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