Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Jul 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL with a Variable - not working

    This module is in MS Access 2007. I've written sql statement that uses a variable 'strName'. The append is working but the 'strName' = "HP" is not. I tried using the quotes many different ways. There must be something fundamental I'm missing. I've put the statements in question in Red.
    When I step thru the code, strName = "HP" as expected.
    Thanks for your help!!!
    Here is the code:

    Code:
    Private Sub ImportData()
    On Error GoTo Err_ImportData
     
    Dim strPath As String
    Dim strFileNm As String
    Dim db As Database, ws As Workspace
    Dim strSQL As String
    Dim strSQLYE As String
    Dim strSQLLM As String
    Dim Response As Variant
    Dim txtMonth As String
    Dim archivedb As String
    Dim tblArchiveNM As String
    Dim tblName As String
    Dim curDate As Date
    Dim rst1 As DAO.Recordset
    Dim strName As String
     
     
    Set db = CurrentDb
    Set ws = Workspaces(0)
     
     
    tblName = "HP Procurement"
    VendorNM = "Dell"
     
    strSQLDel = "DELETE [tblProcurement].* FROM [tblProcurement];"
     
    strSQLDell = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
    "SELECT [Dell Procurement].[PO Number], [Dell Procurement].[PO Date], [Dell Procurement].[Cust Order No], [Dell Procurement].[Ship Date], [Dell Procurement].Carrier, [Dell Procurement].[Carrier Tracking No], [Dell Procurement].AU, [Dell Procurement].Entity, [Dell Procurement].[Cost Center], [Dell Procurement].[Invoice Number], [Dell Procurement].[Invoice Date], [Dell Procurement].[SKU Number], [Dell Procurement].[SKU Description], [Dell Procurement].[Mfg Name], [Dell Procurement].[Serial No], [Dell Procurement].[Order Price], [Dell Procurement].[Sales Tax], [Dell Procurement].[Extended Price], [Dell Procurement].[Sold To], [Dell Procurement].Qty, ""Dell"" AS Vendor, '" & Date & "'" & _
    "FROM [Dell Procurement];"
    strSQLHP = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
    "SELECT [HP Procurement].[PO Number], [HP Procurement].[PO Date], [HP Procurement].[Cust Order No], [HP Procurement].[Ship Date], [HP Procurement].Carrier, [HP Procurement].[Carrier Tracking No], [HP Procurement].AU, [HP Procurement].Entity, [HP Procurement].[Cost Center], [HP Procurement].[Invoice Number], [HP Procurement].[Invoice Date], [HP Procurement].[SKU Number], [HP Procurement].[SKU Description], [HP Procurement].[Mfg Name], [HP Procurement].[Serial No], [HP Procurement].[Order Price], [HP Procurement].[Sales Tax], [HP Procurement].[Extended Price], [HP Procurement].[Sold To], [HP Procurement].Qty, ""HP"" AS Expr1, '" & Date & "'" & _
    "FROM [HP Procurement];"
    strSQLInsight = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
    "SELECT [Insight Procurement].[PO Number], [Insight Procurement].[PO Date], [Insight Procurement].[Cust Order No], [Insight Procurement].[Ship Date], [Insight Procurement].Carrier, [Insight Procurement].[Carrier Tracking No], [Insight Procurement].AU, [Insight Procurement].Entity, [Insight Procurement].[Cost Center], [Insight Procurement].[Invoice Number], [Insight Procurement].[Invoice Date], [Insight Procurement].[SKU Number], [Insight Procurement].[SKU Description], [Insight Procurement].[Mfg Name], [Insight Procurement].[Serial No], [Insight Procurement].[Order Price], [Insight Procurement].[Sales Tax], [Insight Procurement].[Extended Price], [Insight Procurement].[Sold To], [Insight Procurement].Qty, ""Insight"" AS Expr1, '" & Date & "'" & _
    "FROM [Insight Procurement];"
     
    strSQLVendor = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
    "SELECT tblProcurement.[PO Number], tblProcurement.[PO Date], tblProcurement.[Cust Order No], tblProcurement.[Ship Date], tblProcurement.Carrier, tblProcurement.[Carrier Tracking No], tblProcurement.AU, tblProcurement.Entity, tblProcurement.[Cost Center], tblProcurement.[Invoice Number], tblProcurement.[Invoice Date], tblProcurement.[SKU Number], tblProcurement.[SKU Description], tblProcurement.[Mfg Name], tblProcurement.[Serial No], tblProcurement.[Order Price], tblProcurement.[Sales Tax], tblProcurement.[Extended Price], tblProcurement.[Sold To], tblProcurement.Qty, '" & strName & "', #" & Date & "# " & "FROM tblProcurement;"
    'strSQLVendor = "INSERT INTO dbo_tblProcurement_Files ( PONbr, PODate, CustOrderNbr, ShipDate, Carrier, CarrierTrackingNbr, AU, Entity, CostCenter, InvoiceNbr, InvoiceDate, SKUNbr, SKUDescription, MfgName, SerialNbr, OrderPrice, SalesTax, TotalPrice, SoldTo, Quantity, Vendor, ImportDate )" & _
    "SELECT tblProcurement.[PO Number], tblProcurement.[PO Date], tblProcurement.[Cust Order No], tblProcurement.[Ship Date], tblProcurement.Carrier, tblProcurement.[Carrier Tracking No], tblProcurement.AU, tblProcurement.Entity, tblProcurement.[Cost Center], tblProcurement.[Invoice Number], tblProcurement.[Invoice Date], tblProcurement.[SKU Number], tblProcurement.[SKU Description], tblProcurement.[Mfg Name], tblProcurement.[Serial No], tblProcurement.[Order Price], tblProcurement.[Sales Tax], tblProcurement.[Extended Price], tblProcurement.[Sold To], tblProcurement.Qty, " & "'" & strName & "','" & Date & " '" & "FROM tblProcurement;"
     
    ' Set MaxLocksPerFile.
    DBEngine.SetOption dbMaxLocksPerFile, 200000
     
    curDate = Date
     
    Set rst1 = db.OpenRecordset("tblVendorData", dbOpenTable)
    rst1.MoveFirst
     
    Do While Not rst1.EOF
    With rst1
    strPath = !VendorLocation
    strFileNm = !VendorFileName
    strPathBackup = !VendorBackup
    strName = !VendorName
     
    CurrentDb().Execute strSQLVendor, dbFailOnError ' 030320111 variable strName did not work
    FileDate = FileLastModified(strPath & strFileNm)
    If FileDate = curDate Then
    'MsgBox FileDate & " is equal to " & curDate
    'deletes data from the master files
    CurrentDb().Execute strSQLDel, dbFailOnError
    DoCmd.TransferText acImportDelim, "Hp_asset_management Import", "tblProcurement", strPath & strFileNm
    If strName = "HP" Then
    CurrentDb().Execute strSQLHP, dbFailOnError
    Else
    If strName = "Dell" Then
    CurrentDb().Execute strSQLDell, dbFailOnError
    Else
    CurrentDb().Execute strSQLInsight, dbFailOnError
    End If
    End If
     
     
    'CurrentDb().Execute strSQLVendor, dbFailOnError ' 030320111 variable strName did not work
     
    strNewFile = Left(strFileNm, InStr(strFileNm, ".") - 1) & " " & Format(FileDate, "mm dd yyyy") & ".csv"
    strOrig = strPath & strFileNm
    strDest = strPathBackup & strNewFile
    Name strOrig As strDest 'works and renames
     
    End If
     
    rst1.MoveNext
    End With
    Loop
     
     
     
    MsgBox "Done!"
     
     
    Exit_ImportData:
    Exit Sub
     
    Err_ImportData:
    MsgBox Err.Description
    Resume Exit_ImportData
     
    End Sub
    Last edited by jscher2000; 2011-03-05 at 14:31. Reason: Added [code] and [/code] around code to preserve formatting

  2. #2
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts
    I'm going to move this to the Databases forum, since that is where most of the Access gurus can be found.

  3. #3
    Lounger
    Join Date
    Feb 2011
    Posts
    43
    Thanks
    0
    Thanked 6 Times in 6 Posts
    You need to put the line
    strSQLVendor = "INSERT INTO ....
    after the line
    strName = !VendorName

    Otherwise, you are just inserting blanks.

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    To amplify the previous reply, I think that you do things in the wrong order.

    When you define strSQLVendor, and include strName in it via : '" & strName & "', what you are putting into the SQL is the value of StrName rather than the variable itself.
    So when you assign a value to strName later in the code it is too late. The newly assigned value does not find its way back to the SQL.

    So you need to move the definition of strSQLVendor inside the loop, after you have assigned a value to strName.
    Regards
    John



  5. #5
    New Lounger
    Join Date
    Jul 2007
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thanks for the fix and the explanation!

    THANKS so much!!! Now that I'm back from vacation, I changed the code and it works! Thanks for the fix and for the explanation!

Posting Permissions

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