Dlookup #error help! (1 Viewer)

aluc131

New member
Local time
Today, 08:42
Joined
Jul 21, 2017
Messages
6
Hi, new to the forum and fairly new to Access. Thanks for taking the time to help me on this issue. I've been searching and searching and cannot figure it out alone. :banghead:

I have a form with a text box control where I am trying to perform Dlookup function. This is what I put in the Control Source:

=DLookUp("[Par Fee]","Medicare Fee Schedule - NON FACILITY - 2017","[Procedure Code w/Mod]='" & [CD1])

I'm trying to look up the field [Par Fee] from the table "Medicare Fee Schedule - NON FACILITY - 2017" based on the value in form field [CD1] which should equal the field [Procedure code w/Mod] from the table.

According to table design, the field [Procedure Code w/Mod] is Short Text data type. In case that makes a difference.

I keep getting #Error no matter what I try. Please help me!!!!
 

bob fitz

AWF VIP
Local time
Today, 15:42
Joined
May 23, 2011
Messages
4,717
Maybe:

=DLookUp("[Par Fee]","[Medicare Fee Schedule - NON FACILITY - 2017]","[Procedure Code w/Mod]='" & [CD1] & "'")
 

aluc131

New member
Local time
Today, 08:42
Joined
Jul 21, 2017
Messages
6
Thank You so much for your quick help. That solved my error.

Now what's happening is on records where [CD1] is blank the text box with Dlookup is showing no value. I would prefer it to display $0 if [CD1] is blank.
 

moke123

AWF VIP
Local time
Today, 11:42
Joined
Jan 11, 2013
Messages
3,851
aside from your current problem, do you really have a table "Medicare Fee Schedule - NON FACILITY - 2017"? Other than the spaces and special characters in the name, Are you going to have a new table every year?
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:42
Joined
Apr 27, 2015
Messages
6,281

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:42
Joined
Apr 27, 2015
Messages
6,281
Also, since it appears this database is fairly new, I urge you to heed moke123's post and structure your tables more efficiently. One of the biggest hurdles for Access "newbies" to clear is shifting their mindsets from Excel to Access. If you don't TAKE the time to do it now, you will have to MAKE the time to do it later.
 

aluc131

New member
Local time
Today, 08:42
Joined
Jul 21, 2017
Messages
6
aside from your current problem, do you really have a table "Medicare Fee Schedule - NON FACILITY - 2017"? Other than the spaces and special characters in the name, Are you going to have a new table every year?

Yes, this table comes from an outside source and will change yearly. When I imported the data in Access, I did so as a linked Excel file so that changes made to that file are reflected in the table.

Do you know of a better method to do this? I'm trying to integrate some of the things we do outside of Access into the database. This Dlookup is one example.

Thanks!
 

aluc131

New member
Local time
Today, 08:42
Joined
Jul 21, 2017
Messages
6
If the field is formatted as currency, then you could format it to show $0 if the value is Null. This link should get you on your way:

It is formatted as currency and default value set to 0 but still displays nothing. :confused:

Do I need to add something to the formula to account for null values?
 

aluc131

New member
Local time
Today, 08:42
Joined
Jul 21, 2017
Messages
6
Also, since it appears this database is fairly new, I urge you to heed moke123's post and structure your tables more efficiently. One of the biggest hurdles for Access "newbies" to clear is shifting their mindsets from Excel to Access. If you don't TAKE the time to do it now, you will have to MAKE the time to do it later.

Do you have any suggestions or recommendations as to structuring my tables more efficiently? Thanks!
 

NauticalGent

Ignore List Poster Boy
Local time
Today, 11:42
Joined
Apr 27, 2015
Messages
6,281
Do I need to add something to the formula to account for null values?

Yes. In the Format property of the field is where you would place the custom formats. The link I provided in my previous post will show you how to do it.

Also, without seeing your tables, I can only guess how you have them structured. If you can post them here, I (and others) can take a look.
 

Minty

AWF VIP
Local time
Today, 15:42
Joined
Jul 26, 2013
Messages
10,354
Normally when you see this type of structure you need to import it and add a field to identify the year , then you have one table with everything in it, and can use the additional field to pull in suitable reporting etc.

Hopefully there is a unique field in the underlying spreadsheet to let you only import new values with each update.
 

aluc131

New member
Local time
Today, 08:42
Joined
Jul 21, 2017
Messages
6
Normally when you see this type of structure you need to import it and add a field to identify the year , then you have one table with everything in it, and can use the additional field to pull in suitable reporting etc.

Hopefully there is a unique field in the underlying spreadsheet to let you only import new values with each update.

Thank You so much for that recommendation. I agree that is a much better use of the table.

I have made the suggested change to my table. It is now titled "Medicare Fee Schedule_NON FACILITY" and I added a column for the Fee Schedule Year (i.e 2017, 2018, etc)

How does this change to the table now change my Dlookup formula/set up?
 

Users who are viewing this thread

Top Bottom