Question DLookup with 3 multiple numeric Criteria (1 Viewer)

nofeara · May 10, 2015

Hello everyone!

Uhm i have a problem regarding this DLookup, and if i am able to solve this problem i am sure to unlock many possibilities in ms access.

Ok heres the problem:

I have this payroll system: View attachment JJAEPAYROLL.accdb

In the "qryEmployeeWTax" i need to Lookup the [InitialTax] from the "qryreftbl_WTAX" based from the [ETPES] and [SalaryAfterStatutoryDeductions], so i am using this function:

Code:

InitialTax: DLookUp("[InitialTax]","qryreftbl_WTAX","[ETPES]=" & [ETPES] & "AND [ValueRangeStart]<=" & [SalaryAfterStatutoryDeductions] & "AND [ValueRangeEnd]<" & [SalaryAfterStatutoryDeductions])

But it doesn't work:banghead:

Why? What's wrong?

I am able to use DLookup successfully using 2 criteria s in my "qryEmployeesStatutoryInfo"

Please help me

Rabbie · May 10, 2015

Are you getting an error message? If so, what? If not what happens? I don't have the versions of Access that can open your attachment so I need more info to help you.

nofeara · May 10, 2015

I got a #Error sir.

Here is an earlier version: View attachment JJAEPR.mdb

JHB · May 10, 2015

Try by adding a CCur:

Code:

InitialTax: DLookUp("[InitialTax]","qryreftbl_WTAX","[ETPES]=" & [ETPES] & " AND [ValueRangeStart]<=" & [B][COLOR=Red]CCur([/COLOR][/B][SalaryAfterStatutoryDeductions][B][COLOR=Red])[/COLOR][/B] & " AND [ValueRangeEnd]<" & [COLOR=Red][B]CCur([/B][/COLOR][SalaryAfterStatutoryDeductions][COLOR=Red])[/COLOR])

Else try to click in the column in which the #Error occur!

nofeara · May 10, 2015

Uhm, when i put that Currency function, it does not lookup the proper value and if i click on that #Error it shows:

jdraw · May 10, 2015

I think the use of lookup fields at the table field level is part of your problem.
This sort of thing:
EmployeeTaxingPeriod is defined as a number data type and the field value is Monthly

Most developers will tell you to avoid lookups at the table field level(lookup datatype).

Good luck.

nofeara · May 10, 2015

uhm, if that is the evil case, what should i do? xD [edit: uhm ok wow, i will just adjust my db sometime]

But, uhm i already made [ETPES]

Code:

InitialTax: DLookUp("[InitialTax]","qryreftbl_WTAX","[ETPES]=" & [ETPES])

The code above can actually find the first record having that [ETPES]. Now i think the problem is finding the proper combination of [ValueRangeStart] and [ValueRangeEnd] that satisfies the [SalaryAfterStatutoryDeductions] in "qryreftbl_WTAX"

jdraw · May 10, 2015

i will just adjust my db sometime

.
Have you documented what your database is about-from general to more detailed?
Have you identified the business rules?

If you were to "hand over" your database for someone else to adjust or maintain, WHAT info do you think they would need? Is that info available? That is the info you should be using to keep focus and to know whether something is working properly or not.

You have said you're thinking up the database requirements based on your experience. Getting those facts together will help you with design, now.

Here's the analogy-- do you think buildings get designed after they are completed?

nofeara · May 11, 2015

Uhm, now im getting confused, jdraw ur making me read, reading is killing me, i learn when i do things. xD

But again

But, uhm i already made [ETPES]

Code:

InitialTax: DLookUp("[InitialTax]","qryreftbl_WTAX","[ETPES]=" & [ETPES])

The code above can actually find the first record having that [ETPES]. Now i think the problem is finding the proper combination of [ValueRangeStart] and [ValueRangeEnd] that satisfies the [SalaryAfterStatutoryDeductions] in "qryreftbl_WTAX"

jdraw · May 11, 2015

I'm not making you read. I am offering my thoughts and concerns based on what you have posted (or haven't posted).

When you say that you are building the database based on your understanding of what is required, and you have no documentation and no plan, I'm just trying to get you to see what a vulnerable position you are in. Your approach indicates to me you have little to no experience with database and possibly none with Access. When I see the use of a Lookup within a table field, it sends up a flag. To use the Lookup data type you have to be aware of the implications and ramifications (as the article in your last posts also says). It may be a short cut to some, but you can achieve the same result with an explicitly defined separate table and foreign key. It is the source of tremendous confusion for most developers, although it does things behind the scenes that aren't obvious.

Here is the point from your reference link re Lookup fields that is often overlooked/misunderstood/ignored:

As pointed out in the comments (thanks JamesL), in my rebuttal to the weak arguments against using lookup fields, I was remiss in not mentioning that there are some legitimate complaints about the implementation of this feature in MS-Access. I still don’t think the feature should be discarded completely, but it definitely needs to be used with an understanding of its quirks and shortcomings.

Click to expand...

If your interpretation of my comments is that I am making you read, and you don't want to do any reading, then ignore my comments.

Good luck with your project, however you decide to proceed.

nofeara · May 11, 2015

Hehehe jdraw dude, I am very thankful of all your replies, because it really helped me a lot, it was just an expression

but sorry if it offended you or whatever.

Uhm again, so i take into consideration that lookup evil and now i have tested to create another db with only that tax table and that wtax. Here it is: View attachment TestPJdb.accdb

Now the problem is still there, it does not lookup the values that i want.

In "qryWTax" i have this field Initial Tax which has this code:

Code:

InitialTax: DLookUp("InitialTax","TaxTable","[EmployeeTaxingPeriod]=" & [EmployeeTaxingPeriod] & " AND [EmployeeStatus]=" & [EmployeeStatus] & " AND [ValueRangeStart]<=" & [SalaryAfterStatutoryDeductions] & " AND [ValueRangeEnd]<" & [SalaryAfterStatutoryDeductions])

All i want, base from the values of my [EmployeeTaxingPeriod], [EmployeeStatus] and [SalaryAfterStatutoryDeductions] in "qryWTax" i am able to get the right InitialTax found in "TaxTable" which is 708.33 but why can't i do that, what is the problem?

jdraw · May 11, 2015

What exactly are you trying to accomplish -- in plain, simple English?
I'm looking at your database. The table names do not convey much meaning to readers.
Why does TaxTable have an EmployeeStatus field? Why is EmployeeStatus in both tables?
In plain English what does TaxTable represent?

It would be helpful if you added a description to each field in each table. Again, this highlights my earlier comment about no documentation and no plan. Readers have to guess at what everything means. You have to rely on your memory of what it is you are doing regardless of changes suggested/made or redone.

I have a suggestion for you.
Work through this tutorial which starts with a clear statement of the issue/opportunity/problem and leads you through a series of steps to identify and design tables, attributes and relationships.

And then write a description of your opportunity/issue at the same level as the tutorial.

plog · May 11, 2015

I'm here to back up jdraw. You really need to start from scratch. I looked at your first database and your tables are not properly set up. You should not have mutliple tables with the same structure. When you do that, you essentially store data in the table name.

You didn't make an Employees_Female and an Employees_Male table. You made a field in your table to distinguish if an employee was male or female. Same principle applies for all those tables that are differentiate by whatever the suffix in the table name means.

Also, you generally don't use DLookups in a query. You just bring in whatever datasource you want and link it appropriately to the other datasources in the query. Again, that's an issue for another day. Right now you need to work through jdraw's link and then focus on your tables.

nofeara · May 12, 2015

jdraw said:
What exactly are you trying to accomplish -- in plain, simple English?
I'm looking at your database. The table names do not convey much meaning to readers.
Why does TaxTable have an EmployeeStatus field? Why is EmployeeStatus in both tables?
In plain English what does TaxTable represent?

The Tax Table represents this one. It is a reference table.

The EmployeeTaxingPeriod represents: 1 = Daily, 2 = Weekly, 3 = Semi-Monthly and 4 = Monthly, so it means like an employee receives a semi-monthly salary or he is being paid after 15 days and is tax on that payment base on his salary after some qualified deductions.

The EmployeeStatus represents:
1 = Zero Exemption (Z)
2 = Single/Married without qualified dependents (S/ME)
3 = Single/Married with 1 qualified dependent (S/ME1)
4 = Single/Married with 2 qualified dependents (S/ME2)
5 = Single/Married with 3 qualified dependents (S/ME3)
6 = Single/Married with 4 qualified dependents (S/ME4)

Ok now, an employee will give us the details to his/her status (Z,S/ME,S/ME1,S/ME2,S/ME3,S/ME4).

and base from how we pay them we are going to refer to the 1= daily, 2= weekly, 3= semi-monthly or 4 = monthly table.

So what i just want is when an employee is categorized as 2 = S/ME and he is being paid/categorized in 4 = monthly in EmployeeTaxing Period, and his salary after some qualified deductions(ex.12,000.00) falls on the range 10,000.00 and 15,833 i will be able to get the initial tax 708.33 from the reference table as his initial tax.

Then if i am successful in doing that i will be subtracting his salary after some deductions by that initial tax and the result will then be multiplied to the corresponding ExcessPercentTax, which is in my example 20% or 0.2

The_Doc_Man · May 12, 2015

First things first. If the Criteria statement is well-formed, a DLookup with five criteria works fine. I've got one of those in one of my databases - though I don't recommend the messy query needed to support the operation.

Therefore, you have to consider two possibilities. First, your three-way WHERE filter is ill-formed, or if there is a JOIN involved and you are doing DLookup of a query rather than a table, you have a data type mismatch somewhere. (The pesky little #Error marker is telling you that.) So - simplest example: If you have three LONG (integer) fields and use a matching criteria from a text box on a form or a DOUBLE number, you will find that you cannot do that without first doing a type-cast. A text box returns text, which won't compare correctly to LONG. A DOUBLE is also not going to compare to LONG correctly. Plus there is the hidden little "gotcha" - neither will DATE compare to LONG because DATE is a type-case of DOUBLE.

Scrupulously assure yourself that your data types are correct in the three-way criteria statement and you should make some progress.

nofeara · May 12, 2015

Yes!!!!!!! After considering all your comments, i think i am now able to make solution to my problem. I will post here my final output and let u evaluate it again. Thanks.

Question DLookup with 3 multiple numeric Criteria (1 Viewer)

nofeara

Registered User.

Rabbie

Super Moderator

nofeara

Registered User.

JHB

Have been here a while

nofeara

Registered User.

jdraw

Super Moderator

nofeara

Registered User.

jdraw

Super Moderator

nofeara

Registered User.

jdraw

Super Moderator

nofeara

Registered User.

jdraw

Super Moderator

plog

Banishment Pending

nofeara

Registered User.

The_Doc_Man

Immoderate Moderator

nofeara

Registered User.

Similar threads

Users who are viewing this thread