Results 1 to 3 of 3
  1. #1
    Anjan is offline Novice
    Windows 10 Access 2016
    Join Date
    Sep 2016
    Posts
    1

    criteria/condition with memory variable is not workig. in dlookup, the m_last_addt_cost gets "null"

    Dim d As Database



    Dim r As Recordset



    Dim m_vin, m_cost As Field

    Set d = CurrentDb()

    Set r = d.OpenRecordset("overhauling_history_temp_1")




    Set m_vin = r.Fields("vin")

    Set m_cost = r.Fields("original_amount")

    While Not r.EOF


    Dim m_last_addt_cost As Variant

    m_last_addt_cost = DLookup("additional_costs", "inventory_temp_1", "vin = ' " & m_vin & " ' ")

    CurrentDb.Execute "update inventory_temp_1 set additional_costs = " & m_cost & " where inventory_temp_1.vin = ' " & m_vin & " ' "

    CurrentDb.Execute "insert into justtest(vin,cost,last_cost) values(' " & m_vin & " ', " & m_cost & " , " & m_last_addt_cost & " )"

    r.MoveNext


    Wend

    r.Close


    End Sub

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    put 'option explicit' at the top of your module just below 'option database'. And if you have disabled error messages, comment them out until you have the problem resolved

    assuming m_vin is text or a number you don't need set here

    Set m_vin = r.Fields("vin")

    Set m_cost = r.Fields("original_amount")

    and for this to work

    "vin = ' " & m_vin & " ' "

    vin needs to be text

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Also, declarations (Dim m_last_addt_cost As Variant) should NOT be inside a loop structure.

    Code:
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    
    Dim m_vin as String
    Dim m_cost As Double
    
    Dim m_last_addt_cost As Variant  '<<--maybe should be Double???
    
    
    Set d = CurrentDb()
    Set r = d.OpenRecordset("overhauling_history_temp_1")
    
    While Not r.EOF
    
       m_vin = r.Fields("vin")
       m_cost = r.Fields("original_amount")
    
    
      m_last_addt_cost = DLookup("additional_costs", "inventory_temp_1", "vin = '" & m_vin & "'")
    
      CurrentDb.Execute "UPDATE inventory_temp_1 SET additional_costs = " & m_cost & " where inventory_temp_1.vin = '" & m_vin & "'"
    
      CurrentDb.Execute "INSERT INTO justtest(vin,cost,last_cost) VALUES('" & m_vin & "', " & m_cost & ", " & m_last_addt_cost & ")"
    
      r.MoveNext
    
    Wend
    
    r.Close
    
    Set r = Nothing
    Set d = Nothing

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 06-09-2016, 05:45 AM
  2. Replies: 6
    Last Post: 02-11-2016, 02:05 PM
  3. Replies: 4
    Last Post: 01-22-2015, 10:30 AM
  4. Simple "Or is null" Criteria question
    By floyd in forum Queries
    Replies: 2
    Last Post: 11-22-2013, 10:05 AM
  5. Passing criteria "NULL" or "IS NOT NULL" to a query
    By SgtSaunders69 in forum Forms
    Replies: 1
    Last Post: 12-24-2011, 02:22 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums