Page 1 of 2 12 LastLast
Results 1 to 15 of 21
  1. #1
    Join Date
    Jan 2008
    Posts
    74

    Question Unanswered: VBA-String in SQL Statement Syntax Error

    Hi everyone,

    I'm running SQL statement in my VBA code (importing an Excel file), but there seems to be a syntax mistake. I've googled and yahooed the Internet, but found nothing that applies to my problem.

    My goal is to fill the "Monat" column in the IS table like this:

    30.11.2008 or 31.10.2008.

    sJahr String in the Code means year, which is taken from the Excel file name.

    Code:
    'this way doesn't work:
    
    strSQL = "UPDATE IS SET [Monat]= #11/30/" & sJahr & "#;"
    DoCmd.RunSQL strSQL
    
    'this way either:
    DoCmd.RunSQL "UPDATE IS SET IS.Monat = '31.10.'" & sJahr & ";"
    Any help would be greatly appreciated!!!

    Best,
    OfficeDummy

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'd put money on IS being the problem. Can you call it something else? Otherwise enclose it in square brackets.
    http://support.microsoft.com/kb/286335

  3. #3
    Join Date
    Jan 2008
    Posts
    74
    pootle flump, thanks so much for the quick reply!

    I bracketed "IS", but it still doesn't work: I get the following error message (horribly translated from German)

    Error '3075': invalid usage of '!', '.', or '()' in query expression "31.10.".xls

    The same for "#11/30/#".xls

    Any suggestions? Please?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Change to something like:

    Code:
    Dim sql AS String
    
    sql = "UPDATE [IS] SET [IS].[Monat] = #10/31/" & sJahr & "#;"
    Debug.Print sql
    DoCmd.RunSQL sql
    What is the output (to the Immediate Window)? (Note I've made the odd extra correction apart from the obvious changes)

  5. #5
    Join Date
    Jan 2008
    Posts
    74
    I get the following output to the Immediate Window:

    UPDATE [IS] SET [Monat]= #11/30/.xls#;

    Doesn't ring a bell for me.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    So sJahr isn't the year at all... it's a string that is equal to ".xls" and this is why the SQL fails.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Jan 2008
    Posts
    74
    Yeah, I've figured it out just seconds ago. But I still don't know how to get the year from the file name:

    See, my Excel file is a table, which is created once in a month, and then has to be imported to Access. It's full name is

    IT_IS Verrechnung Oktober 2008.xls or
    IT_IS Verrechnung November 2008.xls or
    .
    .
    IT_IS Verrechnung Mai 2008.xls

    -- you get the picture.

    At the very beginning of the code I'm opening the File Dialog window and choose the table that has to be imported, and set the value of sJahr:

    Code:
    With fd
            .AllowMultiSelect = True
            .Filters.Clear
            .Filters.Add "Excel", "*.xls"
            If .Show = -1 Then
                'Durchläuft die ausgewählten Dateien
                For Each vrtSelectedItem In .SelectedItems
                
                           sJahr = Right(Dir(vrtSelectedItem), 4)
                           'outputs .xls instead of needed 2008
    Maybe, there's a way to cut off the .xls from the String, or somehow yield the needed 2008?

    Thanks again for your help!
    OfficeDummy

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    'Remove last 4 characters (".xls")
    sJahr = Left(sJahr, Len(sJahr) - 4)
    
    'Strip the last 4 characters (i.e. the year)
    sJahr = Right(sJahr, 4)
    George
    Home | Blog

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    sJahr = Right(Dir(vrtSelectedItem), 4)

    becomes

    sJahr = Right(Dir(vrtSelectedItem), 8)
    sJahr = Left(sJahr, 4)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Or just sJahr = Left(Right(Dir(vrtSelectedItem), 8),4)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by OfficeDummy
    I get the following output to the Immediate Window:

    UPDATE [IS] SET [Monat]= #11/30/.xls#;

    Doesn't ring a bell for me.
    Just so the lesson is not lost in the detail - your first port of call whenever you get any problems like this (where a dynamically created bit of code does not execute as expected) should be to see what is actually being created & executed. Your next step should be to read up on more advanced debugging techniques (breakpoints, stepping through code, watches etc)

  12. #12
    Join Date
    Jan 2008
    Posts
    74
    Thanks, everyone! Now it works!

    Just another quick question, and I'm done. I'm importing those Excel table with the TransferSpreadsheet method:

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                                "IT", vrtSelectedItem, False, "IT!A1:Z1000"
    1)How do I set the fields name of the new, Access "IT" table during the import?

    2)How is 'like' the operator used properly in the TransferSpreadsheet method? For example, this doesn't work:

    Code:
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
                                                "IT", vrtSelectedItem, False, like"IT!A1:Z100"
    Yeah, pootle flump, I'm learning...

    Thanks again!!!!

  13. #13
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    Or just sJahr = Left(Right(Dir(vrtSelectedItem), 8),4)
    Indeed you can; I just wanted to split it up to show the steps needed to do this. I had considered using the right, 8; but chose not to incase we were dealing with files with more than 3 character extensions (in which cans the len() and instr() functions come in to play).

    for example, I opened a file yesterday with the extension .ssmssln
    George
    Home | Blog

  14. #14
    Join Date
    Jan 2008
    Posts
    74
    I've got another warning from Access when I try to execute delete query statement:

    Code:
    DoCmd.RunSQL "DELETE [IS].F1 FROM [IS] WHERE ([IS].F1) = 3040 OR ([IS].F3) = 3032 OR" & _
                                   "([IS].F1) = 3029 OR ([IS].F1) = 3006 OR ([IS].F1) = 4400 OR ([IS].F1) = 4100" & _
                                   "OR ([IS].F1) = 4120 OR ([IS].F1) = 4302 OR ([IS].F1) = 4306 OR ([IS].F1) = 4330" & _
                                   "OR ([IS].F1) = 4331 OR ([IS].F1) = 4332 OR ([IS].F1) = 4360 OR ([IS].F1) = 4400" & _
                                   "OR ([IS].F1) = 3630 OR ([IS].F1) = 3628 OR ([IS].F1) = 3530 OR ([IS].F1) = 3529" & _
                                   "OR ([IS].F1) = 3521 OR ([IS].F1) = 3520 OR ([IS].F1) = 3519 OR ([IS].F1) = 3518" & _
                                   "OR ([IS].F1) = 3517 OR ([IS].F1) = 3516 OR ([IS].F1) = 3511" & _
                                   "OR ([IS].F1) = 3502 OR ([IS].F1) = 3500;"

    The compiler says that date types in criteria expression are incompatible. The field F1 in the IS table is formatted as Number (Integer).

    Any suggestions??

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DoCmd.RunSQL "DELETE * FROM [IS] WHERE [IS].[F1] IN (3040, 3032, 3029, .....);"

Posting Permissions

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