formatting questions

Discussion in 'Microsoft Access Form Coding' started by fishqqq@hotmail.com, Jun 4, 2011.

  1. Guest

    I'm having trouble formatting a couple of fields on my AP form and am
    hoping someone can offer some ideas.

    [System Notes] is currently formatted as such: "*Split Charge* - MRef
    " & [Forms]![fAPSplit]![MRef] & " For a total of " & [Forms]!
    [fAPSplit]![Total Chg]

    which returns the following: *Split Charge* - MRef 26836 For a total
    of 1000

    What I would like to do is have [Total Chg] formatted to read
    $1000.00 (instread of 1000)

    field 2...

    [AWB#] is just a straight 11 digit number which returns 01412345678

    And I would like to format this as follows: 014 1234 5678

    Any help is appreciated.

    Thanks
     
    , Jun 4, 2011
    #1
    1. Advertisements

  2. <> wrote

    > I'm having trouble formatting a couple of fields


    > [System Notes] is currently formatted as such:
    > "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > " For a total of " & [Forms]! [fAPSplit]![Total Chg]
    > which returns the following: *Split Charge* - MRef
    > 26836 For a total of 1000
    >
    > What I would like to do is have [Total Chg] formatted to read
    > $1000.00 (instread of 1000)


    Just for the record, [System Notes] may be defined or declared as what you
    show but that isn't "formatting".

    Why don't you try "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    " For a total of " & Format([Forms]! [fAPSplit]![Total Chg], "currency")

    FmtCur = "The amount is " & Format(amount, "currency")

    > field 2...
    >
    > [AWB#] is just a straight 11 digit number which returns 01412345678
    >
    > And I would like to format this as follows: 014 1234 5678


    What is a "straight 11 digit number"? Is it a field in a record, or a
    variable, or ??? And how is the field, variable, ??? defined? The longest
    integer number, Long, is only 10-digits. Somehow I don't think you are
    talking about a decimal number in a variant, subtype decimal using the CDec
    function, so I'm guessing you have a Text Field or String Variable.

    If it is a Text Field or String Variable, try

    Format([AWB#], "@@@ @@@@ @@@@")

    if it's not obvious because of font spacing there is one space after the
    first three @ and one space before the last four @s).

    --
    Larry Linson, Microsoft Office Access MVP
    Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    Access newsgroup support is alive and well in USENET
    comp.databases.ms-access
     
    Access Developer, Jun 4, 2011
    #2
    1. Advertisements

  3. You can ignore the line that reads:

    FmtCur = "The amount is " & Format(amount, "currency")

    It was just a test I did to make certain of the format statement I was
    suggesting.
    --
    Larry Linson, Microsoft Office Access MVP
    Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    Access newsgroup support is alive and well in USENET
    comp.databases.ms-access

    "Access Developer" <> wrote in message
    news:...
    > <> wrote
    >
    > > I'm having trouble formatting a couple of fields

    >
    > > [System Notes] is currently formatted as such:
    > > "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > " For a total of " & [Forms]! [fAPSplit]![Total Chg]
    > > which returns the following: *Split Charge* - MRef
    > > 26836 For a total of 1000
    > >
    > > What I would like to do is have [Total Chg] formatted to read
    > > $1000.00 (instread of 1000)

    >
    > Just for the record, [System Notes] may be defined or declared as what you
    > show but that isn't "formatting".
    >
    > Why don't you try "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > " For a total of " & Format([Forms]! [fAPSplit]![Total Chg], "currency")
    >
    > FmtCur = "The amount is " & Format(amount, "currency")
    >
    > > field 2...
    > >
    > > [AWB#] is just a straight 11 digit number which returns 01412345678
    > >
    > > And I would like to format this as follows: 014 1234 5678

    >
    > What is a "straight 11 digit number"? Is it a field in a record, or a
    > variable, or ??? And how is the field, variable, ??? defined? The
    > longest integer number, Long, is only 10-digits. Somehow I don't think
    > you are talking about a decimal number in a variant, subtype decimal using
    > the CDec function, so I'm guessing you have a Text Field or String
    > Variable.
    >
    > If it is a Text Field or String Variable, try
    >
    > Format([AWB#], "@@@ @@@@ @@@@")
    >
    > if it's not obvious because of font spacing there is one space after the
    > first three @ and one space before the last four @s).
    >
    > --
    > Larry Linson, Microsoft Office Access MVP
    > Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    > Access newsgroup support is alive and well in USENET
    > comp.databases.ms-access
    >
    >
    >
    >
     
    Access Developer, Jun 4, 2011
    #3
  4. Guest

    On Jun 4, 5:17 pm, "Access Developer" <> wrote:
    > You can ignore the line that reads:
    >
    >  FmtCur = "The amount is " & Format(amount, "currency")
    >
    > It was just a test I did to make certain of the format statement I was
    > suggesting.
    > --
    >  Larry Linson, Microsoft Office Access MVP
    >  Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    >  Access newsgroup support is alive and well in USENET
    > comp.databases.ms-access
    >
    > "Access Developer" <> wrote in message
    >
    > news:...
    >
    > > <> wrote

    >
    > > > I'm having trouble formatting a couple of fields

    >
    > > > [System Notes] is currently formatted as such:
    > > > "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > > " For a total of " & [Forms]! [fAPSplit]![Total Chg]
    > > > which returns the following: *Split Charge* - MRef
    > > > 26836 For a total of 1000

    >
    > > > What I would like to do is have  [Total Chg] formatted to read
    > > > $1000.00 (instread of 1000)

    >
    > > Just for the record, [System Notes] may be defined or declared as what you
    > > show but that isn't "formatting".

    >
    > > Why don't you try "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > " For a total of " & Format([Forms]! [fAPSplit]![Total Chg], "currency")

    >
    > > FmtCur = "The amount is " & Format(amount, "currency")

    >
    > > > field 2...

    >
    > > > [AWB#] is just a straight 11 digit number which returns 01412345678

    >
    > > > And I would like to format this as follows: 014 1234 5678

    >
    > > What is a "straight 11 digit number"?  Is it a field in a record, or a
    > > variable, or ???  And how is the field, variable, ??? defined?  The
    > > longest integer number, Long, is only 10-digits.  Somehow I don't think
    > > you are talking about a decimal number in a variant, subtype decimal using
    > > the CDec function, so I'm guessing you have a Text Field or String
    > > Variable.

    >
    > > If it is a Text Field or String Variable, try

    >
    > > Format([AWB#], "@@@ @@@@ @@@@")

    >
    > > if it's not obvious because of font spacing there is one space after the
    > > first three @ and one space before the last four @s).

    >
    > > --
    > > Larry Linson, Microsoft Office Access MVP
    > > Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    > > Access newsgroup support is alive and well in USENET
    > > comp.databases.ms-access


    Thanks Larry,
    the first format works perfectly.

    The second is still giving me some problems

    here's what I have in the "field" section of my update macro:
    AWB #: Format([Forms]![fFlightDetails]![AWB #],"@@@ @@@@ @@@@") -
    doesn't seem to work and i'm not sure why? The macro asks me for the
    parameter value of [Forms]![fFlightDetails]![AWB #]

    any thoughts?
    thanks
    Steve
     
    , Jun 5, 2011
    #4
  5. Guest

    On Jun 4, 9:14 pm, "" <> wrote:
    > On Jun 4, 5:17 pm, "Access Developer" <> wrote:
    >
    >
    >
    > > You can ignore the line that reads:

    >
    > >  FmtCur = "The amount is " & Format(amount, "currency")

    >
    > > It was just a test I did to make certain of the format statement I was
    > > suggesting.
    > > --
    > >  Larry Linson, Microsoft Office Access MVP
    > >  Co-author: "Microsoft Access Small Business Solutions", published byWiley
    > >  Access newsgroup support is alive and well in USENET
    > > comp.databases.ms-access

    >
    > > "Access Developer" <> wrote in message

    >
    > >news:...

    >
    > > > <> wrote

    >
    > > > > I'm having trouble formatting a couple of fields

    >
    > > > > [System Notes] is currently formatted as such:
    > > > > "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > > > " For a total of " & [Forms]! [fAPSplit]![Total Chg]
    > > > > which returns the following: *Split Charge* - MRef
    > > > > 26836 For a total of 1000

    >
    > > > > What I would like to do is have  [Total Chg] formatted to read
    > > > > $1000.00 (instread of 1000)

    >
    > > > Just for the record, [System Notes] may be defined or declared as what you
    > > > show but that isn't "formatting".

    >
    > > > Why don't you try "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > > " For a total of " & Format([Forms]! [fAPSplit]![Total Chg], "currency")

    >
    > > > FmtCur = "The amount is " & Format(amount, "currency")

    >
    > > > > field 2...

    >
    > > > > [AWB#] is just a straight 11 digit number which returns 01412345678

    >
    > > > > And I would like to format this as follows: 014 1234 5678

    >
    > > > What is a "straight 11 digit number"?  Is it a field in a record, or a
    > > > variable, or ???  And how is the field, variable, ??? defined?  The
    > > > longest integer number, Long, is only 10-digits.  Somehow I don't think
    > > > you are talking about a decimal number in a variant, subtype decimal using
    > > > the CDec function, so I'm guessing you have a Text Field or String
    > > > Variable.

    >
    > > > If it is a Text Field or String Variable, try

    >
    > > > Format([AWB#], "@@@ @@@@ @@@@")

    >
    > > > if it's not obvious because of font spacing there is one space after the
    > > > first three @ and one space before the last four @s).

    >
    > > > --
    > > > Larry Linson, Microsoft Office Access MVP
    > > > Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    > > > Access newsgroup support is alive and well in USENET
    > > > comp.databases.ms-access

    >
    > Thanks Larry,
    > the first format works perfectly.
    >
    > The second is still giving me some problems
    >
    > here's what I have in the "field" section of my update macro:
    > AWB #: Format([Forms]![fFlightDetails]![AWB #],"@@@ @@@@ @@@@")  -
    > doesn't seem to work and i'm not sure why? The macro asks me for the
    > parameter value of [Forms]![fFlightDetails]![AWB #]
    >
    > any thoughts?
    > thanks
    > Steve


    I just realized that this field is a NUMBER field (not text field).
    does that make a difference?
     
    , Jun 5, 2011
    #5
  6. Apparently the macro processor doesn't understand that
    "[Forms]![fFlightDetails]![AWB #]" is a reference to a Control on a Form
    rather than a value. The only macros I have used in many years were simple
    ones like "AutoExec" because they are so limited in comparison to VBA, so
    I'm just not able to assist on "macro stuff".

    --
    Larry Linson, Microsoft Office Access MVP
    Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    Access newsgroup support is alive and well in USENET
    comp.databases.ms-access

    <> wrote in message
    news:...
    On Jun 4, 5:17 pm, "Access Developer" <> wrote:
    > You can ignore the line that reads:
    >
    > FmtCur = "The amount is " & Format(amount, "currency")
    >
    > It was just a test I did to make certain of the format statement I was
    > suggesting.
    > --
    > Larry Linson, Microsoft Office Access MVP
    > Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    > Access newsgroup support is alive and well in USENET
    > comp.databases.ms-access
    >
    > "Access Developer" <> wrote in message
    >
    > news:...
    >
    > > <> wrote

    >
    > > > I'm having trouble formatting a couple of fields

    >
    > > > [System Notes] is currently formatted as such:
    > > > "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > > " For a total of " & [Forms]! [fAPSplit]![Total Chg]
    > > > which returns the following: *Split Charge* - MRef
    > > > 26836 For a total of 1000

    >
    > > > What I would like to do is have [Total Chg] formatted to read
    > > > $1000.00 (instread of 1000)

    >
    > > Just for the record, [System Notes] may be defined or declared as what
    > > you
    > > show but that isn't "formatting".

    >
    > > Why don't you try "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > " For a total of " & Format([Forms]! [fAPSplit]![Total Chg], "currency")

    >
    > > FmtCur = "The amount is " & Format(amount, "currency")

    >
    > > > field 2...

    >
    > > > [AWB#] is just a straight 11 digit number which returns 01412345678

    >
    > > > And I would like to format this as follows: 014 1234 5678

    >
    > > What is a "straight 11 digit number"? Is it a field in a record, or a
    > > variable, or ??? And how is the field, variable, ??? defined? The
    > > longest integer number, Long, is only 10-digits. Somehow I don't think
    > > you are talking about a decimal number in a variant, subtype decimal
    > > using
    > > the CDec function, so I'm guessing you have a Text Field or String
    > > Variable.

    >
    > > If it is a Text Field or String Variable, try

    >
    > > Format([AWB#], "@@@ @@@@ @@@@")

    >
    > > if it's not obvious because of font spacing there is one space after the
    > > first three @ and one space before the last four @s).

    >
    > > --
    > > Larry Linson, Microsoft Office Access MVP
    > > Co-author: "Microsoft Access Small Business Solutions", published by
    > > Wiley
    > > Access newsgroup support is alive and well in USENET
    > > comp.databases.ms-access


    Thanks Larry,
    the first format works perfectly.

    The second is still giving me some problems

    here's what I have in the "field" section of my update macro:
    AWB #: Format([Forms]![fFlightDetails]![AWB #],"@@@ @@@@ @@@@") -
    doesn't seem to work and i'm not sure why? The macro asks me for the
    parameter value of [Forms]![fFlightDetails]![AWB #]

    any thoughts?
    thanks
    Steve
     
    Access Developer, Jun 6, 2011
    #6
  7. What kind of number? I think I responded earlier. The only numeric field
    that can hold that many integers would be "decimal" and that is valid only
    in a variant. If there were a numeric field type that could hold as many
    digits as you have, then, yes, it would make a different.

    I'm sorry, there just is NOT a field type of "NUMBER"... that's easy
    reference to the numeric types.

    --
    Larry Linson, Microsoft Office Access MVP
    Co-author: "Microsoft Access Small Business Solutions", published by Wiley
    Access newsgroup support is alive and well in USENET
    comp.databases.ms-access

    <> wrote in message
    news:...
    On Jun 4, 9:14 pm, "" <> wrote:
    > On Jun 4, 5:17 pm, "Access Developer" <> wrote:
    >
    >
    >
    > > You can ignore the line that reads:

    >
    > > FmtCur = "The amount is " & Format(amount, "currency")

    >
    > > It was just a test I did to make certain of the format statement I was
    > > suggesting.
    > > --
    > > Larry Linson, Microsoft Office Access MVP
    > > Co-author: "Microsoft Access Small Business Solutions", published by
    > > Wiley
    > > Access newsgroup support is alive and well in USENET
    > > comp.databases.ms-access

    >
    > > "Access Developer" <> wrote in message

    >
    > >news:...

    >
    > > > <> wrote

    >
    > > > > I'm having trouble formatting a couple of fields

    >
    > > > > [System Notes] is currently formatted as such:
    > > > > "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef] &
    > > > > " For a total of " & [Forms]! [fAPSplit]![Total Chg]
    > > > > which returns the following: *Split Charge* - MRef
    > > > > 26836 For a total of 1000

    >
    > > > > What I would like to do is have [Total Chg] formatted to read
    > > > > $1000.00 (instread of 1000)

    >
    > > > Just for the record, [System Notes] may be defined or declared as what
    > > > you
    > > > show but that isn't "formatting".

    >
    > > > Why don't you try "*Split Charge* - MRef " & [Forms]![fAPSplit]![MRef]
    > > > &
    > > > " For a total of " & Format([Forms]! [fAPSplit]![Total Chg],
    > > > "currency")

    >
    > > > FmtCur = "The amount is " & Format(amount, "currency")

    >
    > > > > field 2...

    >
    > > > > [AWB#] is just a straight 11 digit number which returns 01412345678

    >
    > > > > And I would like to format this as follows: 014 1234 5678

    >
    > > > What is a "straight 11 digit number"? Is it a field in a record, or a
    > > > variable, or ??? And how is the field, variable, ??? defined? The
    > > > longest integer number, Long, is only 10-digits. Somehow I don't think
    > > > you are talking about a decimal number in a variant, subtype decimal
    > > > using
    > > > the CDec function, so I'm guessing you have a Text Field or String
    > > > Variable.

    >
    > > > If it is a Text Field or String Variable, try

    >
    > > > Format([AWB#], "@@@ @@@@ @@@@")

    >
    > > > if it's not obvious because of font spacing there is one space after
    > > > the
    > > > first three @ and one space before the last four @s).

    >
    > > > --
    > > > Larry Linson, Microsoft Office Access MVP
    > > > Co-author: "Microsoft Access Small Business Solutions", published by
    > > > Wiley
    > > > Access newsgroup support is alive and well in USENET
    > > > comp.databases.ms-access

    >
    > Thanks Larry,
    > the first format works perfectly.
    >
    > The second is still giving me some problems
    >
    > here's what I have in the "field" section of my update macro:
    > AWB #: Format([Forms]![fFlightDetails]![AWB #],"@@@ @@@@ @@@@") -
    > doesn't seem to work and i'm not sure why? The macro asks me for the
    > parameter value of [Forms]![fFlightDetails]![AWB #]
    >
    > any thoughts?
    > thanks
    > Steve


    I just realized that this field is a NUMBER field (not text field).
    does that make a difference?
     
    Access Developer, Jun 6, 2011
    #7
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Paul

    First of a series of questions. 1. Table vs. Questions

    Paul, Jul 31, 2007, in forum: Microsoft Access Form Coding
    Replies:
    2
    Views:
    163
    John W. Vinson
    Jul 31, 2007
  2. Paul

    First of a series of questions. 1. Table vs. Questions

    Paul, Jul 31, 2007, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    153
  3. David

    Continuous Forms Formatting Questions

    David, Jun 24, 2008, in forum: Microsoft Access Form Coding
    Replies:
    1
    Views:
    134
    Marshall Barton
    Jun 24, 2008
  4. Kenny G

    Bookmark questions Access/Word Error Message Two Questions

    Kenny G, Aug 1, 2008, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    247
    Kenny G
    Aug 1, 2008
  5. Damon Heron

    Questions about conditional formatting

    Damon Heron, Jul 1, 2009, in forum: Microsoft Access Form Coding
    Replies:
    0
    Views:
    113
    Damon Heron
    Jul 1, 2009
Loading...

Share This Page