Query DLookup calculation results in text not number

[Kass]

I have a calculation in a query using DLookup to a table. The lookup value
in the table is a number and is formatted for currency. I want currency to
show in my qurey and related report, but is seems to be treating the DLookup
result as text. The DLookup string is as follows:
Bank Fee: IIf([Membership Opts]="Bank Monthly" And [Rate Opts]="NEW
RATE",DLookUp("[Bank Payroll Fee]","[Type]","[Type]![Membership Type]='" &
[Type] & "'")," X ")

I am trying to look up the "Bank Payroll Fee" currency value in a table
named "Type" when the "Membership Type" from the "Type" table and text from
the field "Type" match. The formula works fine and pulls out the results I
want buy it looks like text, ie.... 22 instead of 22.00 or $22.00 like it
should.

Thanks for your help!

 

[Duane Hookom]

You should be consistent with the data type returned by the IIf().
Bank Fee: IIf([Membership Opts]="Bank Monthly" And [Rate Opts]="NEW RATE",
Val(DLookUp("[Bank Payroll Fee]","[Type]","[Type]![Membership Type]='" &
[Type] & "'")),0)

Then set the format property in your report to display 0 as " X ".

 

[Kass]

Duane,

Thanks! That worked great...I see what you mean about mixing the "X" and 0.
I tried to use the Val function, but didn't have it in the right place. I
don't know how you guys get literate in this lingo...I know just enough to be
dangerous!

I have another problem now.... in the report (Bank Monthly Report) that
feeds off of a query (Current Members Query) and feeds off this query (Bank
Monthly Query), I'm trying to get a total on the [Bank Payroll Fee] column
you just helped me with. I used =Sum([Bank Payroll Fee]) in a text box in
the Report Header and I get the type mismatch error again. Just to see if it
was recognizing the Bank Payroll Fee column as currency (a number), I tried
doing a running total in the properties box for the field and it worked.
Between that and the fact it does give me the option to format that field as
currency in both the query and report, I feel I am dealing with a number now.
Go any ideas??

Thanks again,

Kass

You should be consistent with the data type returned by the IIf().
Bank Fee: IIf([Membership Opts]="Bank Monthly" And [Rate Opts]="NEW RATE",
Val(DLookUp("[Bank Payroll Fee]","[Type]","[Type]![Membership Type]='" &
[Type] & "'")),0)

Then set the format property in your report to display 0 as " X ".

--
Duane Hookom
MS Access MVP

I have a calculation in a query using DLookup to a table. The lookup value
in the table is a number and is formatted for currency. I want currency
to
show in my qurey and related report, but is seems to be treating the
DLookup
result as text. The DLookup string is as follows:
Bank Fee: IIf([Membership Opts]="Bank Monthly" And [Rate Opts]="NEW
RATE",DLookUp("[Bank Payroll Fee]","[Type]","[Type]![Membership Type]='" &
[Type] & "'")," X ")

I am trying to look up the "Bank Payroll Fee" currency value in a table
named "Type" when the "Membership Type" from the "Type" table and text
from
the field "Type" match. The formula works fine and pulls out the results
I
want buy it looks like text, ie.... 22 instead of 22.00 or $22.00 like it
should.

Thanks for your help!

 

[Duane Hookom]

Make sure your text box is in the Report Header and not the Page Header.
Also, make sure the name of the control is not a field name. I would also
try:
=Sum(Val([Bank Payroll Fee]))

--
Duane Hookom
MS Access MVP

Duane,

Thanks! That worked great...I see what you mean about mixing the "X" and
0.
I tried to use the Val function, but didn't have it in the right place. I
don't know how you guys get literate in this lingo...I know just enough to
be
dangerous!

I have another problem now.... in the report (Bank Monthly Report) that
feeds off of a query (Current Members Query) and feeds off this query
(Bank
Monthly Query), I'm trying to get a total on the [Bank Payroll Fee] column
you just helped me with. I used =Sum([Bank Payroll Fee]) in a text box in
the Report Header and I get the type mismatch error again. Just to see if
it
was recognizing the Bank Payroll Fee column as currency (a number), I
tried
doing a running total in the properties box for the field and it worked.
Between that and the fact it does give me the option to format that field
as
currency in both the query and report, I feel I am dealing with a number
now.
Go any ideas??

Thanks again,

Kass

You should be consistent with the data type returned by the IIf().
Bank Fee: IIf([Membership Opts]="Bank Monthly" And [Rate Opts]="NEW
RATE",
Val(DLookUp("[Bank Payroll Fee]","[Type]","[Type]![Membership Type]='"
&
[Type] & "'")),0)

Then set the format property in your report to display 0 as " X ".

--
Duane Hookom
MS Access MVP

I have a calculation in a query using DLookup to a table. The lookup
value
in the table is a number and is formatted for currency. I want
currency
to
show in my qurey and related report, but is seems to be treating the
DLookup
result as text. The DLookup string is as follows:
Bank Fee: IIf([Membership Opts]="Bank Monthly" And [Rate Opts]="NEW
RATE",DLookUp("[Bank Payroll Fee]","[Type]","[Type]![Membership
Type]='" &
[Type] & "'")," X ")

I am trying to look up the "Bank Payroll Fee" currency value in a table
named "Type" when the "Membership Type" from the "Type" table and text
from
the field "Type" match. The formula works fine and pulls out the
results
I
want buy it looks like text, ie.... 22 instead of 22.00 or $22.00 like
it
should.

Thanks for your help!