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

<(E-Mail Removed)> 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

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" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> <(E-Mail Removed)> 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
>
>
>
>

On Jun 4, 5:17*pm, "Access Developer" <accde...@gmail.com> 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" <accde...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > <fish...@hotmail.com> 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

On Jun 4, 9:14*pm, "fish...@hotmail.com" <fish...@hotmail.com> wrote:
> On Jun 4, 5:17*pm, "Access Developer" <accde...@gmail.com> 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" <accde...@gmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > <fish...@hotmail.com> 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?

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

<(E-Mail Removed)> wrote in message
news:90b823a4-9cf5-4218-94d6-(E-Mail Removed)...
On Jun 4, 5:17 pm, "Access Developer" <accde...@gmail.com> 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" <accde...@gmail.com> wrote in message
>
> news:(E-Mail Removed)...
>
> > <fish...@hotmail.com> 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

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

<(E-Mail Removed)> wrote in message
news:79d38a5d-aecd-457a-aa2d-(E-Mail Removed)...
On Jun 4, 9:14 pm, "fish...@hotmail.com" <fish...@hotmail.com> wrote:
> On Jun 4, 5:17 pm, "Access Developer" <accde...@gmail.com> 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" <accde...@gmail.com> wrote in message
>
> >news:(E-Mail Removed)...
>
> > > <fish...@hotmail.com> 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?