Welcome to SPN

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

Sign Up Now!

date format in expression

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

  1. Brian

    Brian
    Expand Collapse
    Guest

    I have a report that is derived from an iif expression within a query. -
    =iif([NAdate]=true,"NA",[date])

    The report field always returns the date in the MM/DD/YYYY format. I Just
    want to display the M/D format (which works fine on the fields that are not
    expressions) regardless of whether I have entered M/D in the format
    properties of either the report and/or query field. What am I missing?

    Thanks
    Brian
     
  2. Loading...


  3. tina

    tina
    Expand Collapse
    Guest

    try using the Format() function to explicitly format the date value, as

    =IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))

    hth


    "Brian" <1@3.net> wrote in message news:zxQag.1298$rS6.238@fed1read11...
    > I have a report that is derived from an iif expression within a query. -
    > =iif([NAdate]=true,"NA",[date])
    >
    > The report field always returns the date in the MM/DD/YYYY format. I Just
    > want to display the M/D format (which works fine on the fields that are

    not
    > expressions) regardless of whether I have entered M/D in the format
    > properties of either the report and/or query field. What am I missing?
    >
    > Thanks
    > Brian
    >
    >
     
  4. Brian

    Brian
    Expand Collapse
    Guest

    Ok, that works - thanks

    Now my question is why will it not accept the format properties of the
    field - or is that one of the quirks we are supposed to live with?


    "tina" <nospam@address.com> wrote in message
    news:sJQag.118384$eR6.37445@bgtnsc04-news.ops.worldnet.att.net...
    > try using the Format() function to explicitly format the date value, as
    >
    > =IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))
    >
    > hth
    >
    >
    > "Brian" <1@3.net> wrote in message news:zxQag.1298$rS6.238@fed1read11...
    >> I have a report that is derived from an iif expression within a query. -
    >> =iif([NAdate]=true,"NA",[date])
    >>
    >> The report field always returns the date in the MM/DD/YYYY format. I Just
    >> want to display the M/D format (which works fine on the fields that are

    > not
    >> expressions) regardless of whether I have entered M/D in the format
    >> properties of either the report and/or query field. What am I missing?
    >>
    >> Thanks
    >> Brian
    >>
    >>

    >
    >
     
  5. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    Access can only apply the Format property if the field consistently contains
    a date. NA is not a date.

    Tina's workaround solves the problem because the Format() function spits out
    text too.

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.
    "Brian" <1@3.net> wrote in message news:EXQag.1300$rS6.1013@fed1read11...
    > Ok, that works - thanks
    >
    > Now my question is why will it not accept the format properties of the
    > field - or is that one of the quirks we are supposed to live with?
    >
    >
    > "tina" <nospam@address.com> wrote in message
    > news:sJQag.118384$eR6.37445@bgtnsc04-news.ops.worldnet.att.net...
    >> try using the Format() function to explicitly format the date value, as
    >>
    >> =IIf([NAdate]=true,"NA",Format([date], "m\/d\/yyyy"))
    >>
    >> hth
    >>
    >>
    >> "Brian" <1@3.net> wrote in message news:zxQag.1298$rS6.238@fed1read11...
    >>> I have a report that is derived from an iif expression within a query. -
    >>> =iif([NAdate]=true,"NA",[date])
    >>>
    >>> The report field always returns the date in the MM/DD/YYYY format. I
    >>> Just
    >>> want to display the M/D format (which works fine on the fields that are

    >> not
    >>> expressions) regardless of whether I have entered M/D in the format
    >>> properties of either the report and/or query field. What am I missing?
     

Share This Page