By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,253 Members | 1,690 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,253 IT Pros & Developers. It's quick & easy.

Dlookup no returning a value

P: 46
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Load()
  2. Text72 = DLookup("[address]", "Customer Database", "school='" & Reports![Create Route Invoice]![School] & "'")
  3. Text75 = DLookup("[city State, Zip Code]", "Customer Database", "school='" & Reports![Create Route Invoice]![School] & "'")
  4. Text93 = DLookup("[PRP]", "Route Pricing per School", "school='" & Reports![Create Route Invoice]![School] & "'")
  5. [Excess time per Hour] = DLookup("[ETPH]", "Route Pricing per School", "School='" & Reports![Create Route Invoice]![School] & "'")
  6. [Excess Mileage Rate per Mile] = DLookup("[EMRPM]", "Route Pricing per School", "School='" & Reports![Create Route Invoice]![School] & "'")
  7. '[number of trips]
  8. 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.
Apr 1 '14 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. 'NOTE: The following is "air code"
  2. Dim Text72 as string
  3. Dim zCriteria as String
  4.  
  5. zCriteria = "school='" & _
  6.    Reports![Create Route Invoice]![School] & "'"
  7. '
  8. '
  9. Text72 = DLookup("[address]", "Customer Database", zCriteria) 
Now why do this?
Because we can trouble shoot:
Expand|Select|Wrap|Line Numbers
  1. 'NOTE: The following is "air code"
  2. Dim Text72 as string
  3. Dim zCriteria as String
  4.  
  5. zCriteria = "school='" & _
  6.    Reports![Create Route Invoice]![School] & "'"
  7. debug.print zCriteria
  8. '
  9. 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

Share this Question
Share on Google+
1 Reply


zmbd
Expert Mod 5K+
P: 5,397
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:
Expand|Select|Wrap|Line Numbers
  1. 'NOTE: The following is "air code"
  2. Dim Text72 as string
  3. Dim zCriteria as String
  4.  
  5. zCriteria = "school='" & _
  6.    Reports![Create Route Invoice]![School] & "'"
  7. '
  8. '
  9. Text72 = DLookup("[address]", "Customer Database", zCriteria) 
Now why do this?
Because we can trouble shoot:
Expand|Select|Wrap|Line Numbers
  1. 'NOTE: The following is "air code"
  2. Dim Text72 as string
  3. Dim zCriteria as String
  4.  
  5. zCriteria = "school='" & _
  6.    Reports![Create Route Invoice]![School] & "'"
  7. debug.print zCriteria
  8. '
  9. 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
Apr 1 '14 #2

Post your reply

Sign in to post your reply or Sign up for a free account.