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