Using results of DLookup in report

[Rose B]

I have a very simple report where I use DLookup to find the value of a field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate. Any
help greatly appreciate.

 

[Guest]

I have a very simple report where I use DLookup to find the value of a
field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate.
Any
help greatly appreciate.

You'll have to duplicate the formula used for CurrTaxRate in the Sum rather
than refer to the control name CurrTaxRate. Example:
If CurrTaxRate has a control source

=DLookup("[MyRate]", "Tax table", "State=" & State)

then you'd have to use

=Sum([Donation]*(1+ DLookup("[MyRate]", "Tax table", "State=" & State)))

Tom Lake

 

[Rose B]

I tried that, but the result does not show at all. In my first field I have

=DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom And [CreateDate]
<=TaxTo")

and that returns a result fine. In my second field I have

=Sum([Donation]*(100+(DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom
And [CreateDate] <=TaxTo")))/100)

If I replace the DLookup section with a number (that is the TaxRate) then it
works fine but the result is empty with the above. TaxRate in the table is
now defined as a general number.

Appreciate any thoughts!

I have a very simple report where I use DLookup to find the value of a
field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate.
Any
help greatly appreciate.

You'll have to duplicate the formula used for CurrTaxRate in the Sum rather
than refer to the control name CurrTaxRate. Example:
If CurrTaxRate has a control source

=DLookup("[MyRate]", "Tax table", "State=" & State)

then you'd have to use

=Sum([Donation]*(1+ DLookup("[MyRate]", "Tax table", "State=" & State)))

Tom Lake

 

[Duane Hookom]

Have you tried adding TaxRates to your report's record source and setting the
criteria under the CreateDate field to:
BETWEEN TaxFrom And TaxTo

Otherwise, I don't understand how your DLookup() works since CreateDate is
not in TaxRates.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I tried that, but the result does not show at all. In my first field I have

=DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom And [CreateDate]
<=TaxTo")

and that returns a result fine. In my second field I have

=Sum([Donation]*(100+(DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom
And [CreateDate] <=TaxTo")))/100)

If I replace the DLookup section with a number (that is the TaxRate) then it
works fine but the result is empty with the above. TaxRate in the table is
now defined as a general number.

Appreciate any thoughts!

I have a very simple report where I use DLookup to find the value of a
field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate.
Any
help greatly appreciate.

You'll have to duplicate the formula used for CurrTaxRate in the Sum rather
than refer to the control name CurrTaxRate. Example:
If CurrTaxRate has a control source

=DLookup("[MyRate]", "Tax table", "State=" & State)

then you'd have to use

=Sum([Donation]*(1+ DLookup("[MyRate]", "Tax table", "State=" & State)))

Tom Lake

 

[Rose B]

I ended up going right back to changing the table where the transactions are
held to add a field of tax rate and looking it up at the time that the
records are cretaed. It is then available for me to report on - chicken's way
out (and not efficient) I know but it was driving me crazy! I might go back
and experiment a bit more when I have time. As I say though the first lookup
did work - CreateDate is a control on my report but not in the TaxRates table

I did try adding the TaxRates table to my query but it is not joined at all
directly to the table with the transactions - does that matter or can you
pick a field from an unrelated table in a source query and then put some
criteria against it?

Thanks for coming back - really do appreciate that.

Have you tried adding TaxRates to your report's record source and setting the
criteria under the CreateDate field to:
BETWEEN TaxFrom And TaxTo

Otherwise, I don't understand how your DLookup() works since CreateDate is
not in TaxRates.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I tried that, but the result does not show at all. In my first field I have

=DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom And [CreateDate]
<=TaxTo")

and that returns a result fine. In my second field I have

=Sum([Donation]*(100+(DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom
And [CreateDate] <=TaxTo")))/100)

If I replace the DLookup section with a number (that is the TaxRate) then it
works fine but the result is empty with the above. TaxRate in the table is
now defined as a general number.

Appreciate any thoughts!

I have a very simple report where I use DLookup to find the value of a
field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate.
Any
help greatly appreciate.

You'll have to duplicate the formula used for CurrTaxRate in the Sum rather
than refer to the control name CurrTaxRate. Example:
If CurrTaxRate has a control source

=DLookup("[MyRate]", "Tax table", "State=" & State)

then you'd have to use

=Sum([Donation]*(1+ DLookup("[MyRate]", "Tax table", "State=" & State)))

Tom Lake

 

[Duane Hookom]

My suggestion didn't include any join between the TaxRates table and any
other table. You use the criteria to limit the TaxRates table to a single
record per record in your other table(s).

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I ended up going right back to changing the table where the transactions are
held to add a field of tax rate and looking it up at the time that the
records are cretaed. It is then available for me to report on - chicken's way
out (and not efficient) I know but it was driving me crazy! I might go back
and experiment a bit more when I have time. As I say though the first lookup
did work - CreateDate is a control on my report but not in the TaxRates table

I did try adding the TaxRates table to my query but it is not joined at all
directly to the table with the transactions - does that matter or can you
pick a field from an unrelated table in a source query and then put some
criteria against it?

Thanks for coming back - really do appreciate that.

Have you tried adding TaxRates to your report's record source and setting the
criteria under the CreateDate field to:
BETWEEN TaxFrom And TaxTo

Otherwise, I don't understand how your DLookup() works since CreateDate is
not in TaxRates.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I tried that, but the result does not show at all. In my first field I have

=DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom And [CreateDate]
<=TaxTo")

and that returns a result fine. In my second field I have

=Sum([Donation]*(100+(DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom
And [CreateDate] <=TaxTo")))/100)

If I replace the DLookup section with a number (that is the TaxRate) then it
works fine but the result is empty with the above. TaxRate in the table is
now defined as a general number.

Appreciate any thoughts!


:



I have a very simple report where I use DLookup to find the value of a
field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate.
Any
help greatly appreciate.

You'll have to duplicate the formula used for CurrTaxRate in the Sum rather
than refer to the control name CurrTaxRate. Example:
If CurrTaxRate has a control source

=DLookup("[MyRate]", "Tax table", "State=" & State)

then you'd have to use

=Sum([Donation]*(1+ DLookup("[MyRate]", "Tax table", "State=" & State)))

Tom Lake

 

[Rose B]

Thanks Duane

My suggestion didn't include any join between the TaxRates table and any
other table. You use the criteria to limit the TaxRates table to a single
record per record in your other table(s).

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm

I ended up going right back to changing the table where the transactions are
held to add a field of tax rate and looking it up at the time that the
records are cretaed. It is then available for me to report on - chicken's way
out (and not efficient) I know but it was driving me crazy! I might go back
and experiment a bit more when I have time. As I say though the first lookup
did work - CreateDate is a control on my report but not in the TaxRates table

I did try adding the TaxRates table to my query but it is not joined at all
directly to the table with the transactions - does that matter or can you
pick a field from an unrelated table in a source query and then put some
criteria against it?

Thanks for coming back - really do appreciate that.

Have you tried adding TaxRates to your report's record source and setting the
criteria under the CreateDate field to:
BETWEEN TaxFrom And TaxTo

Otherwise, I don't understand how your DLookup() works since CreateDate is
not in TaxRates.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


:

I tried that, but the result does not show at all. In my first field I have

=DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom And [CreateDate]
<=TaxTo")

and that returns a result fine. In my second field I have

=Sum([Donation]*(100+(DLookUp("TaxRate","TaxRates","[CreateDate] >=TaxFrom
And [CreateDate] <=TaxTo")))/100)

If I replace the DLookup section with a number (that is the TaxRate) then it
works fine but the result is empty with the above. TaxRate in the table is
now defined as a general number.

Appreciate any thoughts!


:



I have a very simple report where I use DLookup to find the value of a
field
(tax rate) and then I want to use this field to 'create' another field -
=Sum([Donation]*(1+[CurrTaxRate])) wher CurrTaxRate is the DLookup field.
However, it does not look like Access allows this. There is no direct link
between the table with the Donations and the Tax Rate table - I am using a
donation date to be between a start/end date range to get the tax rate.
Any
help greatly appreciate.

You'll have to duplicate the formula used for CurrTaxRate in the Sum rather
than refer to the control name CurrTaxRate. Example:
If CurrTaxRate has a control source

=DLookup("[MyRate]", "Tax table", "State=" & State)

then you'd have to use

=Sum([Donation]*(1+ DLookup("[MyRate]", "Tax table", "State=" & State)))

Tom Lake