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

problem with DLookup code. Getting error that I can't figure out

P: 22
This is a little complicated but I'll do my best to explain. In my db I have a table called L_AgeCorrection which has the following fields: Age, Sex, Frequency, AgeValue

This is a table used to assign an Age Correction value to hearing test results - since some degree of hearing loss naturally occurs with aging - OSHA lets us calculate that in before determining if the employee has an actual "significant" hearing loss.

Anyway...I have my main form F_Demographics with a tabbed control. One of the tabs is for audiograms. This has the current audiogram fields where we enter the date of test, type of test, and test results. This tab also has a subform nested in it to show the employee's baseline audiogram results. I need to compare the current and baseline results but AFTER I correct them for age. So here is an example of how I attempted to do this. On the tab for audiogram I used the After Update event to do a lookup and get the age correction values. I have unbound fields to put the Adjusted values and then unbound fields to later calculate the difference.

But for the lookup table here is the code I came up with...

Expand|Select|Wrap|Line Numbers
  1. Private Sub AudioR6000_AfterUpdate()
  2.  
  3. 'Get Values for current test from L_AgeCorrection table
  4. If Me.AudioAgeL > 60 Then
  5.     Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
  6.     Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
  7.     Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
  8.     Else
  9.     Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
  10.     Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
  11.     Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
  12. End If
  13.  
  14. 'Get values for baseline test from L_AgeCorrection table
  15. If Me.BRAge > 60 Then
  16.     Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
  17.     Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
  18.     Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
  19.     Else
  20.     Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
  21.     Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
  22.     Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
  23. End If
The first part is because the lookup table only goes up to age 60 so if they are over 60 the value is the same as if they were 60.

I think I'm so confused because not all the data fields are on the Audio tab - some of them are on the main form and some of them are on the subform.

Anyway...the error I'm getting is:
Run-time error '2447':

There is an invalid use of the .(dot) or ! or invalid parentheses.

The highlighted line is: If Me.BRAge > 60 Then

Can someone help me troubleshoot my code please? FYI...I had it working in my old 2003 Access db but for some reason I'm getting a hiccup here.

Thanks!
Bekah
Nov 16 '09 #1
Share this Question
Share on Google+
15 Replies


Expert 100+
P: 266
Replace you DLookUp with this:
Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]='" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
I didn't read through your entire post, I just saw this jump out at me, let me know if this fixes your DLookUp.

-AJ
Nov 16 '09 #2

P: 22
Unfortunately that didn't do it...
Nov 16 '09 #3

Expert 100+
P: 266
@rleepac
Ok I noticed you have a few more places where you have issues,
Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender...)
Replace with
Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]='" & Forms![F_Demographics]!empGender & "' ...)

Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender...")
Replace with
Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex]='" & Forms![F_Demographics]!empGender & "' ...)

Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender ...
Replace with
Expand|Select|Wrap|Line Numbers
  1. DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex]='" & Forms![F_Demographics]!empGender & "' ...

NOTE: You need to work on using values like BRAge in VBA. I will look for the article for you and post it unless someone else beats me to it.

-AJ
Nov 16 '09 #4

P: 22
Yup. I fixed all instances of it and I get the same error.
Nov 16 '09 #5

Expert 100+
P: 266
Try my post #4 now, and let me know.

-AJ
Nov 16 '09 #6

P: 22
Ok. Fixed all that and still same error. I'd love to see the article if someone would post it that would be great!
Nov 16 '09 #7

Expert 100+
P: 266
While I am looking for that, please post your code as it is now.

EDIT: here is the link, Quotes (') and Double-Quotes (") - Where and When to use them

Thanks,
-AJ
Nov 16 '09 #8

P: 22
Ok. Here is what I have now...

Expand|Select|Wrap|Line Numbers
  1. Private Sub AudioR6000_AfterUpdate()
  2.  
  3. 'Get values for current test from L_AgeCorrection table
  4. If Me.AudioAgeL > 60 Then
  5.     Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
  6.     Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
  7.     Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
  8.     Else
  9.     Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
  10.     Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
  11.     Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
  12. End If
  13.  
  14. 'Get values for baseline test from L_AgeCorrection table
  15. If Me.BRAge > 60 Then
  16.     Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
  17.     Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
  18.     Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
  19.     Else
  20.     Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
  21.     Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
  22.     Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
  23. End If
Nov 16 '09 #9

P: 22
I read the link and I bookmarked it. It's a little confusing but if I read it a few dozen times I'm sure it will start to sink in... Thank you!
Nov 16 '09 #10

Expert 100+
P: 266
Ok, one question, what data type is Age? because if it is a number, you need to drop all the single quotes(') around the age operators, if it is a text, you need to add single quotes(') to
Expand|Select|Wrap|Line Numbers
  1. If Me.AudioAgeL > 60 Then
and
Expand|Select|Wrap|Line Numbers
  1. If Me.BRAge > 60 Then
-AJ
Nov 16 '09 #11

P: 22
It's a text field. I tried putting single quotes where you suggested but I got an error that said "expected an expression"
Nov 16 '09 #12

Expert 100+
P: 266
Well I have hit a wall, I would ask you to upload the DB, but I do not have access 2007, so I will post this on our expert help form if no one has picked this up here soon, to bring more attention to it. Sorry I couldn't help, I hope it is just something I am over looking but I just don't know without seeing the DB.

-AJ
Nov 16 '09 #13

P: 22
Actually the AudioAgeL and BRAge are both calculated fields using the date of the test and a datediff calculation. So I guess it is a number field. I pulled the single quotes from the age operators and now I get a different error.

Now I get:
Run-time error '2450':

Microsoft Access can't find the form 'F_TestsAudioBaseline' referred to in a macro expression or Visual Basic code.

The BRAge data is pulled from the subform F_TestsAudioBaseline. Is that where my new problem is?
Nov 16 '09 #14

P: 22
Maybe I should start a new thread? I'm wondering if my table set up is part of the problem.

I have a table for audiograms and a table for baseline audiograms. I can't for the life of me figure out why I did it that way. But I should probably just have one table for audiograms and a field that designates if it is a baseline or annual. I'll try messing with that a little and if I can't figure it out then I'll make a new post.

Thanks for all your help!

Bekah
Nov 16 '09 #15

NeoPa
Expert Mod 15k+
P: 31,489
@rleepac
Huh. My article too complicated to read. So harsh. So harsh (J/K of course :D)

Just in case it helps, you may also find Referring to Items on a Sub-Form helps.

For debugging concepts, try Debugging in VBA.

Welcome to Bytes Bekah!
Nov 19 '09 #16

Post your reply

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