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

[rleepac]

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...

Private Sub AudioR6000_AfterUpdate()
 
'Get Values for current test from L_AgeCorrection table
If Me.AudioAgeL > 60 Then
    Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
    Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
    Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
    Else
    Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
    Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
    Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
End If
 
'Get values for baseline test from L_AgeCorrection table
If Me.BRAge > 60 Then
    Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
    Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
    Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
    Else
    Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=2000")
    Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=3000")
    Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender and [Frequency]=4000")
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

[ajalwaysus]

Replace you DLookUp with this:

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

[rleepac]

Unfortunately that didn't do it...

[ajalwaysus]

@rleepac
Ok I noticed you have a few more places where you have issues,

DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]=Forms![F_Demographics]!empGender...)

Replace with

DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex]='" & Forms![F_Demographics]!empGender & "' ...)

DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = AudioAgeL and [Sex]=Forms![F_Demographics]!empGender...")

Replace with

DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex]='" & Forms![F_Demographics]!empGender & "' ...)

DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = BRAge and [Sex]=Forms![F_Demographics]!empGender ...

Replace with

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

[rleepac]

Yup. I fixed all instances of it and I get the same error.

[ajalwaysus]

Try my post #4 now, and let me know.

-AJ

[rleepac]

Ok. Fixed all that and still same error. I'd love to see the article if someone would post it that would be great!

[ajalwaysus]

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

[rleepac]

Ok. Here is what I have now...

Private Sub AudioR6000_AfterUpdate()
 
'Get values for current test from L_AgeCorrection table
If Me.AudioAgeL > 60 Then
    Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
    Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
    Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
    Else
    Me.Adj2000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
    Me.Adj3000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
    Me.Adj4000CR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & AudioAgeL & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
End If
 
'Get values for baseline test from L_AgeCorrection table
If Me.BRAge > 60 Then
    Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
    Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
    Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '60' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
    Else
    Me.Adj2000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=2000")
    Me.Adj3000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=3000")
    Me.Adj4000BR = DLookup("[AgeValue]", "L_AgeCorrection", "[Age] = '" & BRAge & "' and [Sex] = '" & Forms![F_Demographics]!empGender & "' and [Frequency]=4000")
End If

[rleepac]

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!

[ajalwaysus]

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

If Me.AudioAgeL > 60 Then

and

If Me.BRAge > 60 Then

-AJ

[rleepac]

It's a text field. I tried putting single quotes where you suggested but I got an error that said "expected an expression"

[ajalwaysus]

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

[rleepac]

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?

[rleepac]

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

[NeoPa]

@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!