Results 1 to 7 of 7

Thread: Dlookup Help

  1. #1
    Join Date
    Dec 2011
    Posts
    3

    Unanswered: Dlookup Help

    I have an Access Table sOrderTable with [Unique ID], [Resource Group ID], [Priority] columns.

    I am trying to Identify "Unique IDs" in Column A of the excel sheet and looking them up in the access table and then pasting the matching [Resource Group ID] and [Priority] in columns AP and AQ.

    The Error that I am currently receiving is Run-time error 94 Invalid use of Null on the rID=Dlookup line.

    Thanks in advance for your help.

    'Open Target File
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open(sTargetCombine)
    Set xlSheet = xlBook.Worksheets("Tracking")
    xlSheet.Unprotect ("COBAMSupport")

    'Select Data to copy paste and compare
    Dim rID As Integer 'Resource ID
    Dim pID As Integer 'Priority ID
    Dim uID As Integer 'Unique Id


    'Select First Column
    xlSheet.Range("A1").Select
    xlSheet.Range(Selection, Selection.End(xlDown)).Select
    i = Selection.Rows.Count

    For i = 2 To Rows.Count

    uID = xlSheet.Range("A2" + Trim(Str(i))).Value
    rID = DLookup("[Resource Group ID]", sOrderTable, sOrderTable & ".[Unique ID] =" & uID)
    pID = DLookup("[Priority]", sOrderTable, "[Unique ID] =" & uID)

    Next

    xlSheet.Range("AP2" + Trim(Str(i))) = rID
    xlSheet.Range("AQ2" + Trim(Str(i))) = pID

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im suspicious of the . in
    rID = DLookup("[Resource Group ID]", sOrderTable, sOrderTable & ".[Unique ID] =" & uID)
    and the sordertable & looks odd
    try
    Code:
    rID = DLookup("[Resource Group ID]", sOrderTable, "[sOrderTable].[Unique ID] =" & uID)
    OR

    Code:
    rID = DLookup("[Resource Group ID]", sOrderTable, "[Unique ID] =" & uID)
    you don't need to specify the table unless its a view / query that joins two or mor etables together and there is more than one column of that name.
    had you not created column names with spaces then you wouldn't have had to place square brackets around those names either

    have you checked there is a value in uID in the debugger?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    3
    healdem,

    The value for uID = 0, which is probably why I am getting
    Runtime error 94 Invalid use of Null

    when I try:
    rID = DLookup("[Resource Group ID]", sOrderTable, "[Unique ID] =" & uID)

    there is no "0" in column A of the excel.

    Any thought of how to fix this?

    Thanks for the timely response.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Is this an excel or access problem?
    my reading of your dlookup is that you want to look in the table sOrderTable for a unique ID or whatever
    how you handle data errors is up to you
    you could try the nz function
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2011
    Posts
    3
    This is a VBA problem inside access involving an excel sheet.

    I have an Access Table "sOrderTable" with [Unique ID], [Resource Group ID], [Priority] columns.

    I am trying to Identify "Unique IDs" in Column A of the EXCEL SHEET and where they match [Unique ID] in the ACCESS TABLE matching and pasting[Resource Group ID] and [Priority] from ACCESS into columns AP and AQ in EXCEL.

    The current error is 13 type mismatch on the row defining uID

    'Open Target File
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Visible = True
    Set xlBook = xlApp.Workbooks.Open(sTargetCombine)
    Set xlSheet = xlBook.Worksheets("Tracking")

    'Select Data to copy paste and compare
    Dim rID As Integer 'Resource ID
    Dim pID As Integer 'Priority ID
    Dim uID As Integer 'Unique Id


    'Select First Column
    xlSheet.Range("A1").Select
    xlSheet.Range(Selection, Selection.End(xlDown)).Select
    i = Selection.Rows.Count

    For i = 2 To Rows.Count

    uID = xlSheet.Range("A2:A" + Trim(Str(i))).Value
    rID = DLookup("[Resource Group ID]", sOrderTable, "[Unique ID] =" & uID)
    pID = DLookup("[Priority]", sOrderTable, "[Unique ID] =" & uID)

    Next

    xlSheet.Range("AP2" + Trim(Str(i))) = rID
    xlSheet.Range("AQ2" + Trim(Str(i))) = pID

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    There is something wrong with the way you try to assign a value to uID:
    Code:
    uID = xlSheet.Range("A2:A" + Trim(Str(i))).Value
    As this line is in a loop, this means that you try to assign a Range made of several cells: "A2:A2", "A2:A3", "A2:A4",... "A2:A<Rows.Count> to a variable defined as an integer:
    Code:
    Dim uID As Integer 'Unique Id
    Have a nice day!

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    most likely cause of that error is you are trying to assign a value of the wrong datatype to the variable

    as uID is defined as integer, then the value from the spreadsheet cell must be numeric. my guess is that its either NULL or string / text.
    I'd suggest you test the value before assigning using either of:-

    nz(VariantVariable,ValueIfNull)

    isnumeric(variable)
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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