Appending zero-length string

Hello all,
I recently ran Allen Browne's function to change all my fields to not accept
zero-length strings- which makes so much sense to me. Of course, I'm now
feeling some repercussions.

I have a command button on a form that runs an append query. This query
includes two fields which are not required, and which access seems to append
as zero-length strings if they are blank on the form. Is there a way to
change the query to append a null value if it is blank, or in other words, to
only append data that has values? Or should I pull out the saved query and
use VBA to append the values depending on what is blank on the form?

It seems a shame to allow zero-length strings if I'm appending into them,
when everything else is going to be clean.

Thanks for you suggestions,
Claire

Care to post the query or the code you are using? It is really difficult to
trouble-shoot without the needed information.

IF you are building the query string, you probably need to put the word NULL
into the string when the control has a null or zero-length string value.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Claire wrote:
Hello all,
I recently ran Allen Browne's function to change all my fields to not accept
zero-length strings- which makes so much sense to me. Of course, I'm now
feeling some repercussions.

I have a command button on a form that runs an append query. This query
includes two fields which are not required, and which access seems to append
as zero-length strings if they are blank on the form. Is there a way to
change the query to append a null value if it is blank, or in other words, to
only append data that has values? Or should I pull out the saved query and
use VBA to append the values depending on what is blank on the form?

It seems a shame to allow zero-length strings if I'm appending into them,
when everything else is going to be clean.

Thanks for you suggestions,
Claire

Clai

An expression in the query such as the following:

IIF(TRIM(Forms!YourForm!YourControl & "")="",NULL,Forms!YourForm!YourControl)

would return a Null if the control is Null, contains a zero-length string, or
a string made up entirely of space characters, but the value of the control
otherwise.

Ken Sheridan
Stafford, England

Claire wrote:
Hello all,
I recently ran Allen Browne's function to change all my fields to not accept
zero-length strings- which makes so much sense to me. Of course, I'm now
feeling some repercussions.

I have a command button on a form that runs an append query. This query
includes two fields which are not required, and which access seems to append
as zero-length strings if they are blank on the form. Is there a way to
change the query to append a null value if it is blank, or in other words, to
only append data that has values? Or should I pull out the saved query and
use VBA to append the values depending on what is blank on the form?

It seems a shame to allow zero-length strings if I'm appending into them,
when everything else is going to be clean.

Thanks for you suggestions,
Claire

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

Beautiful! That should do the trick!

"KenSheridan via AccessMonster.com" wrote:

Clai

An expression in the query such as the following:

IIF(TRIM(Forms!YourForm!YourControl & "")="",NULL,Forms!YourForm!YourControl)

would return a Null if the control is Null, contains a zero-length string, or
a string made up entirely of space characters, but the value of the control
otherwise.

Ken Sheridan
Stafford, England

Claire wrote:
Hello all,
I recently ran Allen Browne's function to change all my fields to not accept
zero-length strings- which makes so much sense to me. Of course, I'm now
feeling some repercussions.

I have a command button on a form that runs an append query. This query
includes two fields which are not required, and which access seems to append
as zero-length strings if they are blank on the form. Is there a way to
change the query to append a null value if it is blank, or in other words, to
only append data that has values? Or should I pull out the saved query and
use VBA to append the values depending on what is blank on the form?

It seems a shame to allow zero-length strings if I'm appending into them,
when everything else is going to be clean.

Thanks for you suggestions,
Claire

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...eries/201002/1

.

"KenSheridan via AccessMonster.com" u51882@uwe wrote in
news:a3d6f81d91b92@uwe:

An expression in the query such as the following:

IIF(TRIM(Forms!YourForm!YourControl &
"")="",NULL,Forms!YourForm!YourControl)

would return a Null if the control is Null, contains a zero-length
string, or a string made up entirely of space characters, but the
value of the control otherwise.

Or you could use a ZLS-To-Null function, like the one after my sig.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Public Function ZLStoNull(ByVal varInput As Variant) As Variant
If Len(varInput) = 0 Then
ZLStoNull = Null
Else
ZLStoNull = varInput
End If
End Function