Union query screws currency format on report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a union query that I am using (thanks to help from MVP's) with a
select top query to generate x number of records for a report look. It is
however causing my currency fields to show as ie. 2 instead of $2.00.

I have all fields set to currency in my tables, forms and reports. Not that
the form matters but I feel it is being caused by the union query. Is this
correct and if so can it be corrected easily?

Thanks in advance,

Pete
 
You can wrap the field in Val() in the control source. Then apply the
currency display in the format property.
 
Good Evening Duane,
I am assuming that you mean in the report field [UnitPrice] control source.
Is this correct? I can not seem to get it to work, I have tried
Val(UnitPrice) gives me EPV box(Enter Perimeter Value)
Val("UnitPrice") gives me EPV box(Enter Perimeter Value)
Val([UnitPrice]) gives me EPV box(Enter Perimeter Value)
=Val([UnitPrice]) gives me #error in field on the report
=Val(UnitPrice) gives me #error in field on the report
=Val("UnitPrice") gives me $0.00 in field on the report

I did however try this in the Top20Select query
SELECT TOP 19 POUnion.*, Format(Val(Nz([UnitPrice])),"Currency") AS Expr1
FROM POUnion;
and changed the control source on UnitPrice to Expr1 and I get the correct
display.

But, (every notice there is always a but) the report was created to keep
the purchaser happy about keeping with the existing layout. That is why I am
using a union all query and a topselect, however if there is more 19 items
being ordered I had to use a different filter so all the items are able to be
printed allowing the next page. So on the report_open I have it determine
how many items are in the subform using dcount and choose the appropriate
filter.

This in turn will bring me back to the beginning. Since I have two filters
and have not been able to get the Val() to work (if it would be the correct
option in this instance) should I just add to the queries the appropriate
information so this will work with either filter or am I missing something
with the Val() function.

Thanks, Is appreciated
Pete
 
=Val([UnitPrice])
should work but you must make sure the name of the text box is not the name
of a field in the report's record source.

Nz() will return a variant and you should always provide two arguements
like:
Nz([UnitPrice],0)

--
Duane Hookom
MS Access MVP

default105 said:
Good Evening Duane,
I am assuming that you mean in the report field [UnitPrice] control
source.
Is this correct? I can not seem to get it to work, I have tried
Val(UnitPrice) gives me EPV box(Enter Perimeter Value)
Val("UnitPrice") gives me EPV box(Enter Perimeter Value)
Val([UnitPrice]) gives me EPV box(Enter Perimeter Value)
=Val([UnitPrice]) gives me #error in field on the report
=Val(UnitPrice) gives me #error in field on the report
=Val("UnitPrice") gives me $0.00 in field on the report

I did however try this in the Top20Select query
SELECT TOP 19 POUnion.*, Format(Val(Nz([UnitPrice])),"Currency") AS Expr1
FROM POUnion;
and changed the control source on UnitPrice to Expr1 and I get the correct
display.

But, (every notice there is always a but) the report was created to keep
the purchaser happy about keeping with the existing layout. That is why I
am
using a union all query and a topselect, however if there is more 19 items
being ordered I had to use a different filter so all the items are able to
be
printed allowing the next page. So on the report_open I have it determine
how many items are in the subform using dcount and choose the appropriate
filter.

This in turn will bring me back to the beginning. Since I have two
filters
and have not been able to get the Val() to work (if it would be the
correct
option in this instance) should I just add to the queries the appropriate
information so this will work with either filter or am I missing something
with the Val() function.

Thanks, Is appreciated
Pete
Click to expand...
 
That is what it was, I had the textbox name the same as the control source,
thanks for the info on Nz().
 
Back
Top