Exporting Delimited and Fixed Width Text Formatting

[Waylen]

I would love to be able to use date and currency/number
formats, but I'm forced to keep working in text due to
the follow problem.

I work for a marketing company and we have many clients
that we export calling data back to. Most of our clients
require different date and amount formats. I end up using
text formats and I have to mid() and len() them to get
the required output in a query. This is an example of an
amount format that one of our client's need. We don't
work generally with values less than zero dollars or
greater than 9999.99 I would use the next statement to
format a currency field in the query:

amount: IIf(Len([amt])=4,Mid([amt],1,1) & Mid
([amt],3,2),IIf(Len([amt])=5,Mid([amt],1,2) & Mid
([amt],4,2),IIf(Len([amt])=6,Mid([amt],1,3) & Mid
([amt],5,2),IIf(Len([amt])=7,Mid([amt],1,4) & Mid
([amt],6,2),"A number less that 3 digits or greater than
7 has been entered"))))

I have to use similar functions for dates as well. When I
use a Date Format It's beautiful in Access and easy! dd-
mmm-yy to return 15-MAY-04 or to pad an amount field with
zeros 0000.00

However, when I use date or currency formatting I can not
export to a text delimited or a fixed with file and keep
the formatting as I intend. Please help, this has plauged
me for 2 years now.

Sincerely,

~Waylen...

PS. I'm using Access 2000

 

[John Nurick]

Hi Waylen,

You don't say what format(s) you want or what you're actually getting,
so I'd be guessing if I offered any specific solutions.

In general, one formats data for output by using the Format() function
in calculated fields in queries.

The example you give seems to be trying to format a number stored with 2
decimal places for output without a decimal point. Try
amount: Format(100 * [amt],"0")
or
amount: Format(100 * [amt], "0000000")
if you want leading zeroes.

I would love to be able to use date and currency/number
formats, but I'm forced to keep working in text due to
the follow problem.

I work for a marketing company and we have many clients
that we export calling data back to. Most of our clients
require different date and amount formats. I end up using
text formats and I have to mid() and len() them to get
the required output in a query. This is an example of an
amount format that one of our client's need. We don't
work generally with values less than zero dollars or
greater than 9999.99 I would use the next statement to
format a currency field in the query:

amount: IIf(Len([amt])=4,Mid([amt],1,1) & Mid
([amt],3,2),IIf(Len([amt])=5,Mid([amt],1,2) & Mid
([amt],4,2),IIf(Len([amt])=6,Mid([amt],1,3) & Mid
([amt],5,2),IIf(Len([amt])=7,Mid([amt],1,4) & Mid
([amt],6,2),"A number less that 3 digits or greater than
7 has been entered"))))

I have to use similar functions for dates as well. When I
use a Date Format It's beautiful in Access and easy! dd-
mmm-yy to return 15-MAY-04 or to pad an amount field with
zeros 0000.00

However, when I use date or currency formatting I can not
export to a text delimited or a fixed with file and keep
the formatting as I intend. Please help, this has plauged
me for 2 years now.

Sincerely,

~Waylen...

PS. I'm using Access 2000

 

[Guest]

Hello John,

Very sorry about the confusion on the formats. I'm
exporting generally to Delimited text files or Fixed
width text files. Which is the problem I'm having with
using the currency or date formats. When I export them I
loose the format.

I've tried to use the amount: Format(100 * [amt],"0")but
I keep getting a compile error.

I'm using the function in the query Field:
myfieldname:Format(100*[myfieldname],"0")

Sincerely,

~Waylen...

-----Original Message-----
Hi Waylen,

You don't say what format(s) you want or what you're actually getting,
so I'd be guessing if I offered any specific solutions.

In general, one formats data for output by using the Format() function
in calculated fields in queries.

The example you give seems to be trying to format a number stored with 2
decimal places for output without a decimal point. Try
amount: Format(100 * [amt],"0")
or
amount: Format(100 * [amt], "0000000")
if you want leading zeroes.

I would love to be able to use date and currency/number
formats, but I'm forced to keep working in text due to
the follow problem.

I work for a marketing company and we have many clients
that we export calling data back to. Most of our clients
require different date and amount formats. I end up using
text formats and I have to mid() and len() them to get
the required output in a query. This is an example of an
amount format that one of our client's need. We don't
work generally with values less than zero dollars or
greater than 9999.99 I would use the next statement to
format a currency field in the query:

amount: IIf(Len([amt])=4,Mid([amt],1,1) & Mid
([amt],3,2),IIf(Len([amt])=5,Mid([amt],1,2) & Mid
([amt],4,2),IIf(Len([amt])=6,Mid([amt],1,3) & Mid
([amt],5,2),IIf(Len([amt])=7,Mid([amt],1,4) & Mid
([amt],6,2),"A number less that 3 digits or greater than
7 has been entered"))))

I have to use similar functions for dates as well. When I
use a Date Format It's beautiful in Access and easy! dd-
mmm-yy to return 15-MAY-04 or to pad an amount field with
zeros 0000.00

However, when I use date or currency formatting I can not
export to a text delimited or a fixed with file and keep
the formatting as I intend. Please help, this has plauged
me for 2 years now.

Sincerely,

~Waylen...

PS. I'm using Access 2000

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[John Nurick]

This
myfieldname:Format(100*[myfieldname],"0")
will produce a circular reference error because the alias (to the left
of the : ) is the same as the fieldname passed to the Format() function,
but it shouldn't produce a compile error in a normal Access/Jet database
(.mdb file).

Does the Format() function work elsewhere? To find out, hit Ctrl-G to
open the Immediate pane, and then type
? Format(Date(), "dd mmmm yyyy")
and hit Enter. If this doesn't work, start the troubleshooting by trying
to compile the database's VBA project.

Hello John,

Very sorry about the confusion on the formats. I'm
exporting generally to Delimited text files or Fixed
width text files. Which is the problem I'm having with
using the currency or date formats. When I export them I
loose the format.

I've tried to use the amount: Format(100 * [amt],"0")but
I keep getting a compile error.

I'm using the function in the query Field:
myfieldname:Format(100*[myfieldname],"0")

Sincerely,

~Waylen...

-----Original Message-----
Hi Waylen,

You don't say what format(s) you want or what you're actually getting,
so I'd be guessing if I offered any specific solutions.

In general, one formats data for output by using the Format() function
in calculated fields in queries.

The example you give seems to be trying to format a number stored with 2
decimal places for output without a decimal point. Try
amount: Format(100 * [amt],"0")
or
amount: Format(100 * [amt], "0000000")
if you want leading zeroes.

I would love to be able to use date and currency/number
formats, but I'm forced to keep working in text due to
the follow problem.

I work for a marketing company and we have many clients
that we export calling data back to. Most of our clients
require different date and amount formats. I end up using
text formats and I have to mid() and len() them to get
the required output in a query. This is an example of an
amount format that one of our client's need. We don't
work generally with values less than zero dollars or
greater than 9999.99 I would use the next statement to
format a currency field in the query:

amount: IIf(Len([amt])=4,Mid([amt],1,1) & Mid
([amt],3,2),IIf(Len([amt])=5,Mid([amt],1,2) & Mid
([amt],4,2),IIf(Len([amt])=6,Mid([amt],1,3) & Mid
([amt],5,2),IIf(Len([amt])=7,Mid([amt],1,4) & Mid
([amt],6,2),"A number less that 3 digits or greater than
7 has been entered"))))

I have to use similar functions for dates as well. When I
use a Date Format It's beautiful in Access and easy! dd-
mmm-yy to return 15-MAY-04 or to pad an amount field with
zeros 0000.00

However, when I use date or currency formatting I can not
export to a text delimited or a fixed with file and keep
the formatting as I intend. Please help, this has plauged
me for 2 years now.

Sincerely,

~Waylen...

PS. I'm using Access 2000

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.

 

[Guest]

John,

Thank you so much! This is going to save me a hundred
advil a month!

Sincerely,

~Waylen...