Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Union query screws currency format on report

Discussion in 'Information Technology' started by default105, Jul 28, 2006.

  1. default105

    default105
    Expand Collapse
    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
     
  2. Loading...

    Similar Threads Forum Date
    Sikh News Sikh Union Make Special Visit To Houses Of Parliament To Showcase Their Community Work - Breaking News Jul 30, 2016
    Heritage India's Union Sports Ministry To Promote Gatka History of Sikhism Oct 23, 2013
    Hi-Tech Unites the World's Sikhs in Daily Communion Sikh Sikhi Sikhism Jun 29, 2013
    The Holy Union of Anand Karaj New to Sikhism Mar 16, 2013
    General Union with God (BoKSD 6) Hard Talk Dec 4, 2012

  3. Duane Hookom

    Duane Hookom
    Expand Collapse
    Guest

    You can wrap the field in Val() in the control source. Then apply the
    currency display in the format property.
    --
    Duane Hookom
    MS Access MVP

    "default105" <default105@discussions.microsoft.com> wrote in message
    news:616F9F67-7B87-42F7-A366-F99DFE006341@microsoft.com...
    >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
     
  4. default105

    default105
    Expand Collapse
    Guest

    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


    "Duane Hookom" wrote:

    > You can wrap the field in Val() in the control source. Then apply the
    > currency display in the format property.
    > --
    > Duane Hookom
    > MS Access MVP
     
  5. Duane Hookom

    Duane Hookom
    Expand Collapse
    Guest

    =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" <default105@discussions.microsoft.com> wrote in message
    news:482BAFE4-0B8C-4847-983A-019E1FBDCB85@microsoft.com...
    > 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
    >
    >
    > "Duane Hookom" wrote:
    >
    >> You can wrap the field in Val() in the control source. Then apply the
    >> currency display in the format property.
    >> --
    >> Duane Hookom
    >> MS Access MVP

    >
     
  6. default105

    default105
    Expand Collapse
    Guest

    That is what it was, I had the textbox name the same as the control source,
    thanks for the info on Nz().


    "Duane Hookom" wrote:

    > =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
     

Share This Page