Nulls or Blanks in ASCII output

[vtj]

An existing program is trying to use the following to create a file that is
exported as an ASCII file. The field ‘Appr’ may be an alphanumeric value or
spaces and is three positions. It can not be ‘000.’ When there is a value,
the insert works great. But when there are spaces in the field, nothing (not
blanks, nulls, or spaces) is put in that position. That creates an error in
the ASCII file as everything thereafter is shifted three spaces left with the
effect of ‘bombing’ the batch.

Is there a way to fix this or will I have to build the file ‘Main’ using
some form of command that will allow an 'IIF' test to see if it is null and
therefore am able to force spaces into the file or pass the value that is in
the field?

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' & "
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "' &
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"[Appr] & " & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Thanks for your help!!!

 

[Douglas J. Steele]

Try using

Right(Space(3) & CStr([Appr]), 3)

rather than just [Appr].

 

[Douglas J. Steele]

Actually, if [Appr] can be Null, that won't work. You'll need to use:

Right(Space(3) & CStr(Nz([Appr],"")), 3)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try using

Right(Space(3) & CStr([Appr]), 3)

rather than just [Appr].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

An existing program is trying to use the following to create a file that
is
exported as an ASCII file. The field 'Appr' may be an alphanumeric value
or
spaces and is three positions. It can not be '000.' When there is a
value,
the insert works great. But when there are spaces in the field, nothing
(not
blanks, nulls, or spaces) is put in that position. That creates an error
in
the ASCII file as everything thereafter is shifted three spaces left with
the
effect of 'bombing' the batch.

Is there a way to fix this or will I have to build the file 'Main' using
some form of command that will allow an 'IIF' test to see if it is null
and
therefore am able to force spaces into the file or pass the value that is
in
the field?

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' &
"
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "'
&
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"[Appr] & " & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Thanks for your help!!!

 

[vtj]

The comma before the last 3 makes it think that should be the end of the
statement. It is packing numerous fields into the 'Data' field in the Main
table and because there are no definitions beyond 'Data' as to where to put
the information, the comma is telling it to end the statement and it doesn't
want the rest of the statement.

I tried putting quotes around the whole statement and then it tells me I
have a syntax error.

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' & "
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "' &
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"Right(Space(3) & CStr(Nz([Appr], "")), 3)" & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Actually, if [Appr] can be Null, that won't work. You'll need to use:

Right(Space(3) & CStr(Nz([Appr],"")), 3)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try using

Right(Space(3) & CStr([Appr]), 3)

rather than just [Appr].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

An existing program is trying to use the following to create a file that
is
exported as an ASCII file. The field 'Appr' may be an alphanumeric value
or
spaces and is three positions. It can not be '000.' When there is a
value,
the insert works great. But when there are spaces in the field, nothing
(not
blanks, nulls, or spaces) is put in that position. That creates an error
in
the ASCII file as everything thereafter is shifted three spaces left with
the
effect of 'bombing' the batch.

Is there a way to fix this or will I have to build the file 'Main' using
some form of command that will allow an 'IIF' test to see if it is null
and
therefore am able to force spaces into the file or pass the value that is
in
the field?

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' &
"
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "'
&
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"[Appr] & " & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Thanks for your help!!!

 

[Douglas J. Steele]

It's because you're putting double quotes inside of double quotes.

You need to use two double quotes wherever you want one inside, just as you
did in the Format statement:

"Right(Space(3) & CStr(Nz([Appr], """")), 3)" & _

What that does, by the way, is concatenate whatever's in [Appr] to 3 spaces,
then takes the last 3 characters of the resultant string. That way, if
there's nothing in [Appr], you'll get 3 spaces, if what's in [Appr] is 2
characters long, you'll get a space followed by the 2 characters and so on.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The comma before the last 3 makes it think that should be the end of the
statement. It is packing numerous fields into the 'Data' field in the
Main
table and because there are no definitions beyond 'Data' as to where to
put
the information, the comma is telling it to end the statement and it
doesn't
want the rest of the statement.

I tried putting quotes around the whole statement and then it tells me I
have a syntax error.

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' &
"
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "' &
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"Right(Space(3) & CStr(Nz([Appr], "")), 3)" & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Actually, if [Appr] can be Null, that won't work. You'll need to use:

Right(Space(3) & CStr(Nz([Appr],"")), 3)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Try using

Right(Space(3) & CStr([Appr]), 3)

rather than just [Appr].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


An existing program is trying to use the following to create a file
that
is
exported as an ASCII file. The field 'Appr' may be an alphanumeric
value
or
spaces and is three positions. It can not be '000.' When there is a
value,
the insert works great. But when there are spaces in the field,
nothing
(not
blanks, nulls, or spaces) is put in that position. That creates an
error
in
the ASCII file as everything thereafter is shifted three spaces left
with
the
effect of 'bombing' the batch.

Is there a way to fix this or will I have to build the file 'Main'
using
some form of command that will allow an 'IIF' test to see if it is
null
and
therefore am able to force spaces into the file or pass the value that
is
in
the field?

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] &
"' &
"
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] &
"'
&
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"[Appr] & " & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Thanks for your help!!!

 

[vtj]

Thank you for your explaination of what is happening. It is really helpful
to know. That part works after I put an '&' in before the last quote on that
line. But now the items on the next line do not get inserted into the file.
It doesn't give me any errors so I must be doing something dumb. That is why
I copied it below again. I think it must be reading to the end as it appears
to be getting the information from Temp3.

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' & "
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "' &
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "'& " & _
"Right(Space(3) & CStr(Nz([Appr], """")), 3) & " & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

It's because you're putting double quotes inside of double quotes.

You need to use two double quotes wherever you want one inside, just as you
did in the Format statement:

"Right(Space(3) & CStr(Nz([Appr], """")), 3)" & _

What that does, by the way, is concatenate whatever's in [Appr] to 3 spaces,
then takes the last 3 characters of the resultant string. That way, if
there's nothing in [Appr], you'll get 3 spaces, if what's in [Appr] is 2
characters long, you'll get a space followed by the 2 characters and so on.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

The comma before the last 3 makes it think that should be the end of the
statement. It is packing numerous fields into the 'Data' field in the
Main
table and because there are no definitions beyond 'Data' as to where to
put
the information, the comma is telling it to end the statement and it
doesn't
want the rest of the statement.

I tried putting quotes around the whole statement and then it tells me I
have a syntax error.

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] & "' &
"
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] & "' &
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"Right(Space(3) & CStr(Nz([Appr], "")), 3)" & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Actually, if [Appr] can be Null, that won't work. You'll need to use:

Right(Space(3) & CStr(Nz([Appr],"")), 3)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Try using

Right(Space(3) & CStr([Appr]), 3)

rather than just [Appr].

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


An existing program is trying to use the following to create a file
that
is
exported as an ASCII file. The field 'Appr' may be an alphanumeric
value
or
spaces and is three positions. It can not be '000.' When there is a
value,
the insert works great. But when there are spaces in the field,
nothing
(not
blanks, nulls, or spaces) is put in that position. That creates an
error
in
the ASCII file as everything thereafter is shifted three spaces left
with
the
effect of 'bombing' the batch.

Is there a way to fix this or will I have to build the file 'Main'
using
some form of command that will allow an 'IIF' test to see if it is
null
and
therefore am able to force spaces into the file or pass the value that
is
in
the field?

DoCmd.RunSQL "insert into Main (Counter, AcctNbr, RecType, Data) " & _
"Select [RecCount], [Acct], ""L"", " & _
"'" & [Fix1] & [JulianDate] & [Fix2] & "' & " & _
"[Agcy] & '" & [Fix25] & "' & [Loc] & '" & [DocNumber1] &
"' &
"
& _
"format([reccount],""000"") & '" & [DocNumber2] & [Fix3] &
"'
&
" & _
"[Fund] & '" & [Fix25] & "' & [Agcy] & [Orgn] & " & _
"'" & [Fix4] & "' & " & _
"[RSC] & '" & [Fix45] & [Fix44] & "' & " & _
"'" & [Fix5] & [LineDesc] & "' & " & _
"Format([Total], ""00000000000000"") & " & _
"'" & [Fix6] & "' & " & _
"[Appr] & " & _
"'" & [Fix7] & [Fix8] & [Fix9] & "' " & _
"From Temp3"

Thanks for your help!!!