Results 1 to 4 of 4
  1. #1
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87

    Question VBA Public Function using DLookup not working, can't find problem

    Hello All you Database Gurus!

    I have a spreadsheet that I'm "sucking" into my database, but the spreadsheet needs to be "cleaned".

    I'm starting by creating some public functions that I can call from other areas of the VBA Code. Right now, I'm just doing the functions, I haven't written any code actually using them ... I'm testing by using the immediate window.

    So, I have a table of grades. This is a test table, so it's named "Table1"; a very creative name, I might add!

    The table has the fields CurGrd, AdvGrd, and SprdSht_Grade. All the fields are text fields and all the fields are filled in, there are no blank, empty, or null fields.
    (**Note, grades must be text fields because "Kindergarten" or "K" is text ...)

    The table that has the initial spreadsheet data has a field called rawGrade. It is also text. It is the same information as SprdSht_Grade in Table1.

    I want to use Table1 to lookup the CurGrd using the rawGrade.

    Table1 looks like:

    CurGrd AdvGrd SprdSht_Grade
    K 1 0 Grade K
    1 2 Grade 01
    2 3 Grade 02
    etc ... etc ... etc ...

    My first try was:

    Code:
    Public Function clnGrade(rawGrade As String)
    
    Dim Grade As String
    
    Debug.Print rawGrade
    Grade = DLookup("CurGrd", "Table1", "SprdSht_Grade= ' " & rawGrade & " ' ")
    Debug.Print Grade
    
    End Function
    In the immediate window, I entered: (Another note, the people who produce the spreadsheet can't figure out how to get Grade K to sort before Grade 01, so they add a leading zero ... sigh)

    ? clnGrade("0 Grade K")

    I get:
    0 Grade K <-(This is from the "Debug.Print rawGrade", just to make sure it's seeing what I'm entering)

    Then I get an "Invaild use of Null" error.

    But, why? There are no nulls in the table!

    So, I tried this:

    Code:
    Public Function clnGrade(rawGrade As String)
    
    Dim Grade As String
    
    Debug.Print rawGrade
    If DLookup("CurGrd", "Table1", "SprdSht_Grade= ' " & rawGrade & " ' ") <> "" Then
    Grade = DLookup("CurGrd", "Table1", "SprdSht_Grade = ' " & rawGrade & " ' ")
    Debug.Print Grade
    End If
    
    End Function
    And got the same result (it printed the rawGrade in the immediate window), but without the error ... so, it knows what the rawGrade is, but doesn't do the DLookup as I'd like.

    I've looked all over this forum and others and I'm 95% sure my syntax is correct for using DLookup with text.



    I have tried declaring Grade as a Variant, but I get the same results.

    This seems so basic! I've used DLookup lots of times in both queries and VBA code! This is just the first part of the function, because there are some "grades" in the spreadsheet that are not really grades ... like "Grade Unknown", "Adult", or "Not Applicable". Those WILL need to return a Null value, but for now, I'm testing with real actual grades ... and I can't get it to do it's thing correctly!

    Any and all help would be appreciated!


    Susie
    Olathe, Kansas
    Frustrated Girl Scout Volunteer

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Maybe you have unwanted spaces in
    Code:
    DLookup("CurGrd", "Table1", "SprdSht_Grade= ' " & rawGrade & " ' ")
    Try:
    Code:
    DLookup("CurGrd", "Table1", "SprdSht_Grade= '" & rawGrade & "'")
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    catluvr is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2018
    Location
    Kansas
    Posts
    87


    I can't believe it was that simple! ARGH! I removed the spaces and everything worked fine.

    Thank you very much Bob!

    Susie
    Olathe Kansas
    Less Frustrated Girl Scout Volunteer

  4. #4
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Always a pleasure to help a frustrated young lady 😎.
    Good luck with your project 😊.

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

Similar Threads

  1. Replies: 2
    Last Post: 11-06-2019, 03:24 AM
  2. Public Function problem on form open
    By d9pierce1 in forum Forms
    Replies: 1
    Last Post: 11-27-2018, 11:19 PM
  3. VBA Can't find Public Function
    By GraeagleBill in forum Programming
    Replies: 15
    Last Post: 03-17-2016, 09:38 PM
  4. dlookup function problem
    By bdaniel in forum Programming
    Replies: 3
    Last Post: 04-26-2010, 05:55 AM
  5. I have Problem in processing Dlookup Function
    By Katada in forum Programming
    Replies: 2
    Last Post: 04-23-2006, 12:07 AM

Tags for this Thread

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