Appending zero-length string

[Claire]

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

 

[John Spencer]

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

 

[KenSheridan via AccessMonster.com]

Claire:

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]

Beautiful! That should do the trick!

Claire:

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


--



.

 

[David W. Fenton]

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