formatting questions

[fishqqq]

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

 

[Access Developer]

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).

 

[Access Developer]

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

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

 

[fishqqq]

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

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

 

[fishqqq]

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

news:[email protected]...

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?

 

[Marshall Barton]

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

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 #]

I just realized that this field is a NUMBER field (not text field).
does that make a difference?

That sure looks lke it is a field in a query, not a macro.

The reason you are prompted for
[Forms]![fFlightDetails]![AWB #] is because the form is not
open when you run the query.

As Larry said, an 11 digit number will cause problems. You
may be able to make it look ok using:

AWB #: Format([Forms]![fFlightDetails]![AWB
#],"000-0000-0000"
but that may only work because the leading digit is 0 and
the next digit is 1. Once the leading digits are big
enough, the number type will cause problems.

So, you should do something to force the text box on the
form to have a Text string instead of a number. This may be
as simple as setting the text box's Format property to @ to
force it to have a text string as its data type.

If you have a field in a table with the AWB#, make sure the
field's data type is Text.

 

[Access Developer]

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

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

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]

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

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

news:[email protected]...

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?