Free Lessons
Fast Tips
Topic Index
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
Home > TechHelp > Directory > Access > DLookup < Markup | Quote, Invoice, Receipt >
Back to DLookup    Comments List
DLookUp With Variable Field Upload Images   Link   Email  
Patrick McCabe       
2 years ago
I have been looking around through the various videos involving DLookUp, and I can't quite find the answer to this question.
For this example I have 3 tables. The payment I receive for a service depends on 2 variables: PayerName (one of 27 different payers, housed in the CustomerT) and BillingCode (one of 3 codes used depending on the service provided, house in my AppointmentT). The 3rd table is imported from Excel and has the payment information for each service for each payer. The names of the Fields are the names of the billing codes, if that makes sense... Will upload a screenshot.

I'm trying to write a query that pulls Payer Name from CustomerT, BillingCode from Appointment T, then perform a DLookup to pull the Payment information from the Payment Table. I have tried several iterations of the expressions based on the TechHelp videos, including string concatenation, but I cant seem to get it.

Happy to wait until Expert 10, if you cover using Dlookup with a variable "field" there!
Adam Schwanz             
2 years ago
I'll wait for your picture, but it sounds like you just need a query with your 2 fields and then a self made dlookup expression, like

PaymentInfo: DLOOKUP("Field","Table","ID=" & ID)
Scott Axton            
2 years ago
Take a look at the Search Form 2.0 video.

That one will give you lots of ideas on searching / filtering your records as well.
Patrick McCabe       
2 years ago

Adam Schwanz             
2 years ago
Can you upload a picture in design view of a query with the 3 tables in it so we can see them and if they have any relationships?
Patrick McCabe       
2 years ago
So this is the Table imported from Excel. It is a work in progress; where is says "No data" it is because I literally don't know what we'll be paid, but for my test I made sure to use an example that did have data.

Right now, I have a Field called code billed in the VisitT, the values of which are 98966,98967,98968,99211. I'm trying to write the D-Lookup to use the "value" of CodeBilled to equal the the field name. Ie If CodeBilled= 98966, then use the Field named 98966 from the PlanReimbursementT.

Likely there is a better way to do it!
Patrick McCabe       
2 years ago

Adam Schwanz             
2 years ago
So it looks like you wouldn't even need DLOOKUP, if you drag PatientID from PatientT to PatientID from VisitT, it all should have access to everything then. Assuming everything is setup as expected, you may also need to adjust to Outer Joins.

Have you tried that?
Patrick McCabe       
2 years ago
I'm not sure I follow. I would still need the value from the PlanReimbursementT.

Oh, maybe you're thinking that Reimbursement is already in VisitT. Sorry the field "Reimbursement" on the VisitT does not have anything in it- it was a test field to see if I would have any better luck putting it right into the table instead of building a Query. "Reimbursement" in my query was the name of the Field I was going to give to the Dlookup expression.

Sorry for the confusion. But because the reimbursement could come from one of 3 Fields from PlanInformationT, based on the value of CodeBilled, I think I would need some kind of expression to tell it where to look, right?

I posted this far too early in my education. I find some value in trial and error and Expert 10 is so far away!
Adam Schwanz             
2 years ago
If you can make a relationship between all involved tables (3 in this case), by dragging the PatientID between the two tables with no relationship(looks like PatientT is already related to PlanReimbursementT by PayerName), you should be able to bring in literally every single field in all 3 tables and have it work (may need to toy with joins, I would probably do show every record from VisitT and matching from PatientT, then every record from PatientT and matching from PlanReimbursementT if so).

If you try this to no success, we can mess with dlookup (assuming I'm on the same page of what you want to do)

This thread is now CLOSED. If you wish to comment, start a NEW discussion in DLookup.


The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
My Account
My Courses
Lost Password
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Customer Support
Web Site Tour
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 6/15/2024 6:35:54 PM. PLT: 1s