Dlookup no returning a value

[Vasago]

I am having trouble getting my dlooup to return any value. I have 5 that I am doing in my vba code. The first two work great but the other three don't return any value or errors.

Private Sub Report_Load()
Text72 = DLookup("[address]", "Customer Database", "school='" & Reports![Create Route Invoice]![School] & "'")
Text75 = DLookup("[city State, Zip Code]", "Customer Database", "school='" & Reports![Create Route Invoice]![School] & "'")
Text93 = DLookup("[PRP]", "Route Pricing per School", "school='" & Reports![Create Route Invoice]![School] & "'")
[Excess time per Hour] = DLookup("[ETPH]", "Route Pricing per School", "School='" & Reports![Create Route Invoice]![School] & "'")
[Excess Mileage Rate per Mile] = DLookup("[EMRPM]", "Route Pricing per School", "School='" & Reports![Create Route Invoice]![School] & "'")
'[number of trips]
End Sub

I did check to see if school data type matches. Which it does. The only real difference between the two and the bottom three is the table that its is lookup up the values from. I have triple checked the spelling of all field and the table name. Thank you for the help.

[zmbd]

OK,
Soap Box Out (^_^)

IMHO
It is always best practice to build your strings first.

Take for example your first construct:
Text72 = DLookup("[address]", "Customer Database", "school='" & Reports![Create Route Invoice]![School] & "'")

Let's modify this just a tad:

'NOTE: The following is "air code"
Dim Text72 as string
Dim zCriteria as String
 
zCriteria = "school='" & _
   Reports![Create Route Invoice]![School] & "'"
'
'
Text72 = DLookup("[address]", "Customer Database", zCriteria) 

Now why do this?
Because we can trouble shoot:

'NOTE: The following is "air code"
Dim Text72 as string
Dim zCriteria as String
 
zCriteria = "school='" & _
   Reports![Create Route Invoice]![School] & "'"
debug.print zCriteria
'
Text72 = DLookup("[address]", "Customer Database", zCriteria) 

Now by pressing <ctrl><g> the immedate window will open and we can see our resolved string for zCriteria

In my humble experiance, 90% of all dlookup errors, or indeed any function with a string criteria, occurs because the string is malformed.

Make the above changes for all of your Dlookups and see what you are actually returning to the function(s).


While were at it: [city State, Zip Code]"
is an extremely bad field name - I don't mean to be harsh; however, it's the truth in that it denotes a lack of normalization:[*]> Database Normalization and Table Structures.
and violates the token restrictions:
-alphanumeric this is very importaint to keep in mind when nameing fields, alphanumeric and preferably no spaces just underscores:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

(^_^)
Ok, soap box put away now