DLOOKUP

C

Christina

Please help. I hae two tables. Field GROSS in Salaries Register. Nother
table with PayRangeLow, PayRangeHigh, Tax Amount.
I need a query to return the tax amount if the gross falls between the
PayRangeLow and PayRangeHigh.
Please however give me all details that should occur on every line of the
query, eg field, table, criteria etc. Thanks
 
K

Ken Snell \(MVP\)

This type of query is more easily built using SQL View rather than design
view. But, let me give you the full SQL statement of the query and then I'll
discuss how to build it in design view -- note that you didn't tell us the
name of the second table, so I'm using a generic name (
WhateverTheNotherTableIsNamed ) for it in this statement:

SELECT [Salaries Register].GROSS,
(SELECT T.[Tax Amount]
FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between
T.PayRangeLow And T.PayRangeHigh) AS TaxAmount
FROM [Salaries Register];


To do this in design view, add the [Salaries Register] table to the grid.
Put the GROSS field on the grid.
Then, in the first empty column, put this entire expression in the Field:
box (be sure to replace the generic table name with the real name of your
other table):

TaxAmount: (SELECT T.[Tax Amount] FROM WhateverTheNotherTableIsNamed AS T
WHERE [Salaries Register].GROSS Between T.PayRangeLow And T.PayRangeHigh)


An alternative way to do this query is to use the DLookup function for the
TaxAmount field value:

SELECT [Salaries Register].GROSS,
DLookup("Tax Amount", "WhateverTheNotherTableIsNamed",
[Salaries Register].GROSS & " Between
[PayRangeLow] And [PayRangeHigh]") AS TaxAmount
FROM [Salaries Register];


In this example, the second column in design view would have this expression
in the Field box (again, remember to replace the generic table name with the
real name):

TaxAmount: DLookup("Tax Amount", "WhateverTheNotherTableIsNamed", [Salaries
Register].GROSS & " Between [PayRangeLow] And [PayRangeHigh]")
 
C

Christina

This is what I posted to the grid. It is giving me an error code # 3075


TaxAmount: (SELECT T.[TaxAmount] FROM IncomeTax AS T
WHERE [Salaries Register].GROSS Between T.PayRangeLow And T.PayRangeHigh)
Error 3075

I have 1 table in the query, Salaries Register and gross in the Field on the
query.


Grateful for your help.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top