Welcome to SPN

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

Sign Up Now!

Re: cannot set format in a query

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

  1. CotoJoe

    CotoJoe
    Expand Collapse
    Guest

    Ok I am having the same problem, only I can't format it in report or the
    query. When I pull up the control source in the report and go to the format
    area, it doesn't even give me a drop down list of any types of formats. This
    is true in the query.

    Some other calculaed fields in my query format properly on the report, this
    one does not.

    I tried putting in the format control the expression
    =Format([FieldName],"currency") but that gives me a weird expression.

    I am totally stumped.
    --
    CotoJoe


    "jkhouston" wrote:

    > From what I can remember, I needed to show a value. My query was creating a
    > null value which meant that there was nothing. Once I created the expression
    > to read zero, as in the numeral 0, the formatting worked correctly.
    >
    > Hope this helps,
    >
    > "SMac" wrote:
    >
    > > Can you tell me how you fixed this???? I have the exact same problem!
    > >
    > > Thanks, Stacey
    > >
    > > "jkhouston" wrote:
    > >
    > > > heheh, believe it or not, as I was replying to you I figured out what was
    > > > wrong.
    > > >
    > > > Basically it was because in my expression I created a null value which
    > > > couldn't be assigned a currency format.
    > > >
    > > > Thanks to Mike and yourself for giving me some ideas to troubleshoot this!
    > > >
    > > > "fredg" wrote:
    > > >
    > > > > On Fri, 24 Sep 2004 12:17:03 -0700, jkhouston wrote:
    > > > >
    > > > > > I am using access 2000 and I am trying to set the format of a field in a
    > > > > > query to currency but I can't. The field is an expression instead of linked
    > > > > > from a table. I have checked to make sure the values are numeric and not
    > > > > > string. If I put in custom formatting in the field properties, i.e. $#,##0.00
    > > > > > , it still does not recognize this.
    > > > > >
    > > > > > Can anybody suggest what is happening? I am using the field in a report and
    > > > > > am not adverse to using code but I don't know what the code would need to be
    > > > > > to set the correct format.
    > > > > >
    > > > > > Thanks in advance for any help,
    > > > > > Regards,
    > > > > > John
    > > > >
    > > > > If you are going to use this field in report, don't bother formatting
    > > > > it in the query.
    > > > >
    > > > > In the report, set the control's format property to currency.
    > > > > Or you can use an expression in an unbound control's control source:
    > > > > =Format([FieldName],"currency")
    > > > >
    > > > > --
    > > > > Fred
    > > > > Please only reply to this newsgroup.
    > > > > I do not reply to personal email.
    > > > >
     
  2. Loading...

    Similar Threads Forum Date
    Cannot Find A Thread ? Try "Search by Tag." Announcements Jan 21, 2013
    I cannot Keep My Hair No Longer Sikh Youth Aug 8, 2012
    India Govt cannot change purpose of acquired land: SC Breaking News Oct 6, 2011
    USA TSA: Body Scanners Cannot See Through Turbans Breaking News Jan 14, 2011
    India India cannot help but be complex Breaking News Oct 2, 2010

  3. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: cannot set format in a query -- use NZ

    Hi Joe,

    perhaps your Fieldname is not always filled out... the
    Format function will return an error if the Fieldname is
    null since it cannot format "nothing"

    to prevent against this, use

    =Format(nz([FieldName],0),"currency")

    *** NZ ***

    You can use the Nz function to return zero, a
    zero-length string (" "), or another specified value when a
    Variant is Null. For example, you can use this function to
    convert a Null value to another value and prevent it from
    propagating through an expression.

    Syntax

    Nz(variant[, valueifnull])

    The Nz function has the following arguments.

    Argument

    variant
    A variable of data type Variant.

    valueifnull
    Optional (unless used in a query).
    A Variant that supplies a value to be returned if the
    variant argument is Null. This argument enables you to
    return a value other than zero or a zero-length string.

    Note If you use the Nz function in an expression in a
    query without using the valueifnull argument, the results
    will be a zero-length string in the fields that contain null
    values.



    the thing to keep in mind when using Nz is what it will
    return if you don't the specify the second, optional, argument.

    If you are using Nz on a field, it will be the data type of
    that field -- 0 for numbers (including dates), and an empty
    string for text or memo

    Unbound textbox / combobox / listbox controls on a form are
    assumed to have TEXT in them... so an empty string will be
    returned if nothing is specified. And, if you specify
    something, it doesn't have to be 0 or ""


    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    CotoJoe wrote:
    > Ok I am having the same problem, only I can't format it in report or the
    > query. When I pull up the control source in the report and go to the format
    > area, it doesn't even give me a drop down list of any types of formats. This
    > is true in the query.
    >
    > Some other calculaed fields in my query format properly on the report, this
    > one does not.
    >
    > I tried putting in the format control the expression
    > =Format([FieldName],"currency") but that gives me a weird expression.
    >
    > I am totally stumped.
     
  4. CotoJoe

    CotoJoe
    Expand Collapse
    Guest

    Re: cannot set format in a query -- use NZ

    Dear Crystal:

    Thank you for your help and my ignorance, I am new to Access. I tried what
    you said but it didn't work.

    I am calling a field from a query called "OtEarn"....I therefore put in the
    following expression in the bound texbox on the report for OtEarn this
    expression:
    =Format(nz([OtEarn],0),"currency"), however I got an error message saying
    the formula was causing a circular reference.

    I tried using the Nz function in the query itself but it again did not let
    me format it as a currency in the drop down.

    Can you help this novice out?
    --
    CotoJoe


    "strive4peace" <"strive4peace2006 at yaho" wrote:

    > Hi Joe,
    >
    > perhaps your Fieldname is not always filled out... the
    > Format function will return an error if the Fieldname is
    > null since it cannot format "nothing"
    >
    > to prevent against this, use
    >
    > =Format(nz([FieldName],0),"currency")
    >
    > *** NZ ***
    >
    > You can use the Nz function to return zero, a
    > zero-length string (" "), or another specified value when a
    > Variant is Null. For example, you can use this function to
    > convert a Null value to another value and prevent it from
    > propagating through an expression.
    >
    > Syntax
    >
    > Nz(variant[, valueifnull])
    >
    > The Nz function has the following arguments.
    >
    > Argument
    >
    > variant
    > A variable of data type Variant.
    >
    > valueifnull
    > Optional (unless used in a query).
    > A Variant that supplies a value to be returned if the
    > variant argument is Null. This argument enables you to
    > return a value other than zero or a zero-length string.
    >
    > Note If you use the Nz function in an expression in a
    > query without using the valueifnull argument, the results
    > will be a zero-length string in the fields that contain null
    > values.
    >
    >
    >
    > the thing to keep in mind when using Nz is what it will
    > return if you don't the specify the second, optional, argument.
    >
    > If you are using Nz on a field, it will be the data type of
    > that field -- 0 for numbers (including dates), and an empty
    > string for text or memo
    >
    > Unbound textbox / combobox / listbox controls on a form are
    > assumed to have TEXT in them... so an empty string will be
    > returned if nothing is specified. And, if you specify
    > something, it doesn't have to be 0 or ""
    >
    >
    > Warm Regards,
    > Crystal
    > Microsoft Access MVP 2006
    >
    > *
    > Have an awesome day ;)
    >
    > remote programming and training
    > strive4peace2006 at yahoo.com
    >
    > *
    >
    > CotoJoe wrote:
    > > Ok I am having the same problem, only I can't format it in report or the
    > > query. When I pull up the control source in the report and go to the format
    > > area, it doesn't even give me a drop down list of any types of formats. This
    > > is true in the query.
    > >
    > > Some other calculaed fields in my query format properly on the report, this
    > > one does not.
    > >
    > > I tried putting in the format control the expression
    > > =Format([FieldName],"currency") but that gives me a weird expression.
    > >
    > > I am totally stumped.

    >
     
  5. strive4peace

    strive4peace
    Expand Collapse
    Guest

    Re: cannot set format in a query -- use NZ

    Hi Joe,

    when you have a calculated column, you cannot choose a name
    for the column that is the same as a field in the underlying
    recordset... make the name something like OtEarnCalc

    put this in the underlying recordset; no need to make a
    calculated control on the report

    Warm Regards,
    Crystal
    Microsoft Access MVP 2006

    *
    Have an awesome day ;)

    remote programming and training
    strive4peace2006 at yahoo.com

    *

    CotoJoe wrote:
    > Dear Crystal:
    >
    > Thank you for your help and my ignorance, I am new to Access. I tried what
    > you said but it didn't work.
    >
    > I am calling a field from a query called "OtEarn"....I therefore put in the
    > following expression in the bound texbox on the report for OtEarn this
    > expression:
    > =Format(nz([OtEarn],0),"currency"), however I got an error message saying
    > the formula was causing a circular reference.
    >
    > I tried using the Nz function in the query itself but it again did not let
    > me format it as a currency in the drop down.
    >
    > Can you help this novice out?
     

Share This Page