DLOOKUP

  • Thread starter Thread starter Christina
  • Start date Start date
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
 
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]")
 
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.
 
Back
Top