Parsing Question

B

Bunky

I have a record coming to me that is 203 bytes long. The first 10 are an
account number followed by the 2 byte Project number then a 1 byte Area
code(not a telephone code) etc., etc. It goes on for 15 fields or so. I
tried to use the format command in the query

Acct Num: IIf(IsNull([Field1]),'0000',Format(Left([Field1],10),'General
Number'))

but do not know how to reference the 2nd and third fields by telling the
format to start at a certain byte. How can this be accomplished?

Thanks,
Kent
 
J

John W. Vinson

I have a record coming to me that is 203 bytes long. The first 10 are an
account number followed by the 2 byte Project number then a 1 byte Area
code(not a telephone code) etc., etc. It goes on for 15 fields or so. I
tried to use the format command in the query

Acct Num: IIf(IsNull([Field1]),'0000',Format(Left([Field1],10),'General
Number'))

but do not know how to reference the 2nd and third fields by telling the
format to start at a certain byte. How can this be accomplished?

Thanks,
Kent
Click to expand...

Use the Mid() function to extract a substring from within the string; and - if
you want a numeric value - use Val() rather than the Format() clause. Format
returns a text string.

If this is coming to you as a Text file, I'd suggest using File... Import and
setting up an import specification to chop the block of text up into fields.

John W. Vinson [MVP]
 
D

dbahooker

John

can I ask a dumb question?

this whole VAL() thiing... has that been around forever? I think that
in Crystal it does something different-- but it's funny..

changing a string to a number is one of those things that was giving
me grief in Vb.net and finally I read somethnig that said 'use VAL()'

has this been inside of Access forever?

I hadn't realized it did a datatype conversion; I guess I never really
stopped to think about what Val() did

thanks

-Aaron




I have a record coming to me that is 203 bytes long. The first 10 are an
account number followed by the 2 byte Project number then a 1 byte Area
code(not a telephone code) etc., etc. It goes on for 15 fields or so. I
tried to use the format command in the query
Click to expand...
Acct Num: IIf(IsNull([Field1]),'0000',Format(Left([Field1],10),'General
Number'))
Click to expand...
but do not know how to reference the 2nd and third fields by telling the
format to start at a certain byte. How can this be accomplished?
Click to expand...
Thanks,
Kent
Click to expand...

Use the Mid() function to extract a substring from within the string; and - if
you want a numeric value - use Val() rather than the Format() clause. Format
returns a text string.

If this is coming to you as a Text file, I'd suggest using File... Import and
setting up an import specification to chop the block of text up into fields.

John W. Vinson [MVP]
Click to expand...
 
Top