473,836 Members | 1,449 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

22 New Member
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 "significan t" 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
15 3141
ajalwaysus
266 Recognized Expert Contributor
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
rleepac
22 New Member
Unfortunately that didn't do it...
Nov 16 '09 #3
ajalwaysus
266 Recognized Expert Contributor
@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
rleepac
22 New Member
Yup. I fixed all instances of it and I get the same error.
Nov 16 '09 #5
ajalwaysus
266 Recognized Expert Contributor
Try my post #4 now, and let me know.

-AJ
Nov 16 '09 #6
rleepac
22 New Member
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
ajalwaysus
266 Recognized Expert Contributor
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
rleepac
22 New Member
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
rleepac
22 New Member
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

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

Similar topics

9
4380
by: deko | last post by:
When doing a mail merge with Word, I can't seem to get the below (abbreviated) routine to iterate strBookmark01 -- it just uses the first name in the recordset for all 10 (or whatever) documents... Is there some other code I need to get the string to iterate with each name? Thanks in advance... Set rstOutput = db.OpenRecordset("qryMailMerge") Do Until rstOutput.EOF strBookmark01 = rstData!Name
10
5808
by: DataBard007 | last post by:
Hello Access Gurus: I use Win98SE and Access97. I just built a simple Access97 application which holds all contact information for my personal contacts, such as first name, last name, address, city, state, etc. When the user wants to search for a particular record, he does two things: 1. On the form is a text box on which he enters the text he is searching for.
0
1640
by: Tony Williams | last post by:
I have posted a number of posts in the Access newsgroups concerning my problem with DLookup. I have had a number of the experts with helpful suggestions but I still can't get it to work! This is what I am trying to do: I have a table called tblmaintabs which holds year to date data for a number of companies which they supply on a quarterly basis. The field txtDomfacsoleqtr is one of the fields that holds part of this data. The...
2
3754
by: g.ormesher | last post by:
Hi, my primary key is a field called "ind" 'Code Dim varUP as Varient Dim varIND as Interger varIND = Me.ind ' this sets varIND to the index value
2
4299
by: Dixie | last post by:
Hi, I am tyring to write some generic code that will send the source SQL for a mailmerge to a Word template. I am trying to use DLookup to insert the query name that is the record source for the mailmerge with Word. The code I am working with is as follows. This code works. wrdDoc.MailMerge.OpenDataSource Name:=CurrentProject.Path & "\" & DLookup("", "tblSetUp", "ID=88"), LinkToSource:=True, Connection:="QUERY MyQuery",...
3
1574
by: mmorgan1240 | last post by:
I have a form that I am trying to update. I would like to have a field populated by another table (State_laws) based on the value of another field on the form. I have a field called "state". When the user inputs a state I would like another field to be populated based on the lookup of another table based on the state. I am getting the value &name in my new unbound text box. Here is my dlookup statement please help me figure out what I am...
2
2349
by: chris.thompson13 | last post by:
I am having a problem setting the criteria part of the DLookup method correctly and am consequently getting an error message. I have a database of staff duties, part of which is a query (qryDaily) that returns all staffs duties for a selected day.e.g. Fullname Duty Name1 E1 Name2 Sick Name3 Pwk N2
7
3074
by: Simon | last post by:
Can any one see a problem with this code ( it does not work Private Sub cmdEmailOrderPlaced_Click() 'On Error GoTo Error_Handler Dim strTo As String Dim strBody As String Dim strSubject As String Dim strItemsOrdered As String Dim CRS1 As ADODB.Recordset Dim strSql As String
30
7299
by: DH22 | last post by:
Access 03 I'm currently having an issue using Dlookup when trying to reference a query (criteria syntax) Currently I have 1 table, which is L_Emps (which contains Employee_ID as my key (numeric), First_Name, and Last_Name (as text). My query is Query1 which is the following: SELECT L_Emps.Employee_ID, L_Emps.First_Name, L_Emps.Last_Name FROM L_Emps
0
9825
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
1
10601
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9388
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5652
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5829
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4460
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.