Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Seminars   Templates   TechHelp   Fast Tips   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > DLookup < QQ4 | Quote, Invoice, Receipt >
 
DLookup
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   13 months ago


Back to DLookup
 

DLookUp With Variable Field Upload Images   Link  
Patrick McCabe 
2 months ago
Hi,
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 months 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 months 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 months ago

Adam Schwanz
2 months 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 months 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 months ago

Adam Schwanz
2 months 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 months 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 months 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, below.
 


Back to DLookup Comments
 

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

6/30/2022Sales Chart
6/27/2022Rounding Errors
6/26/2022Calendar Seminar Students Only
6/24/2022Multi Combo One Field
6/23/2022Modal & Popup
6/21/2022Week of Year
6/16/2022Value List Combo
6/15/2022Weekday
6/14/2022Project Budgets
6/14/2022Find Record
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn