Sql INSERT INTO Empty Txt Boxes??

[CharlesWhiteman]

I am using A2207 and deploying runtime.

Am using numerous strSQL strings and one strSql UPDATE. The problem occurs when the string involves a txt box which contains no data. A runtime warning occurs. Is there a way to make the string handle empty values?

 

[Scooterbug]

When a text box is empty, it's a null value. You can use the nz() function to tell access what to put in when a null is detected.

nz(Expression, WhatYouWantIfNull)

 

[CharlesWhiteman]

Will this work when the value is held within a string such as

'" & [varString] & "'

 

[Scooterbug]

Will this work when the value is held within a string such as

'" & [varString] & "'

Not sure what you mean by 'work'. Are you trying to put quotes around the value of varString so that they appear in the Sql statement? If so, then I believe so. I get confused by the use of single and double quotes inside of strings, so I generally have a function to do it for me.

Public Function QuoteWrapper(sString as String) as String
 
QuoteWrapper = chr(34) & sString & chr(34)
 
End Function

But when creating a sql statement, remember that only values that need to be text have to have quotes around them. Numbers do not. Dates must have the # at the beginning and end.

 

[CharlesWhiteman]

I got the nz additional part of the string and that works. But what if one wants the field to remain blank? How to handle blanks in systax?

 

[Scooterbug]

I'm assuming that the problem occurs with the UPDATE sql statement. The syntax for an update query is:

UPDATE tblName SET fieldName = NewValue

I would imagine an error would be thrown up if you are attempting to update a field in the table that is datatype Long with a datatype Text.

What exactly is the error message that get thrown up, and what is the actual Sql that is causing the error?

 

[CharlesWhiteman]

its runtime error 94 invalid use of null

Dim varPK As String
varPK = Me.txtPrimaryDataID
Dim strSQLUpdate As String
Dim varAddress1 As String
varAddress1 = Nz(Me.txtAddress1, Null)
Dim varAddress2 As String
varAddress2 = Me.txtAddress2
Dim VarCity As String
VarCity = Me.txtCity
Dim varCounty As String
varCounty = Me.txtCounty
Dim varPostCode As String
varPostCode = Me.txtPostCode
strSQLUpdate = "UPDATE tblPrimaryData SET Address1 = (nz('" & [varAddress1] & "',0)), Address2 = '" & [varAddress2] & "',City = '" & [VarCity] & "', County = '" & [varCounty] & "', PostCode = '" & [varPostCode] & "' WHERE PrimaryDataID = " & [varPK] & ";"
DoCmd.RunSQL strSQLUpdate

 

[Scooterbug]

its runtime error 94 invalid use of null

Dim varPK As String
varPK = Me.txtPrimaryDataID
Dim strSQLUpdate As String
Dim varAddress1 As String
varAddress1 = Nz(Me.txtAddress1, Null)
Dim varAddress2 As String
varAddress2 = Me.txtAddress2
Dim VarCity As String
VarCity = Me.txtCity
Dim varCounty As String
varCounty = Me.txtCounty
Dim varPostCode As String
varPostCode = Me.txtPostCode
strSQLUpdate = "UPDATE tblPrimaryData SET Address1 = (nz('" & [varAddress1] & "',0)), Address2 = '" & [varAddress2] & "',City = '" & [VarCity] & "', County = '" & [varCounty] & "', PostCode = '" & [varPostCode] & "' WHERE PrimaryDataID = " & [varPK] & ";"
DoCmd.RunSQL strSQLUpdate

Ok, already see some issues:

varAddress1 = Nz(Me.txtAddress1, Null)

what you are saying is that if txtAddress1 is null, then it's null.

Like I said, im cloudy when it comes to using quotes inside of quotes, so I'll show you what it should be using chr(34)

strSQLUpdate = "UPDATE tblPrimaryData " & _
"SET Address1 = nz([varAddress1] ,""), " & _
"Address2 = " & chr(34) & [varAddress2] & chr(34) & "," & _
"City = " & chr(34) & [VarCity] & chr(34) & "," & _
"County = " & chr(34) & [varCounty] & chr(34) ", " & _
"PostCode = " & chr(34) [varPostCode] & chr(34) & _
" WHERE PrimaryDataID = " & [varPK] & ";"
 
debug.print strsqlupdate

I am assuming that datatypes for all the fields except PrimaryDataID are Text. PrimaryDataID should be a number

The debug.print will print out the sql statement into the immediate window. Copy that into a blank query and try to run it.

 

[CharlesWhiteman]

Its great of you to help me out. Referring back to your earlier post. Do I first need to create the function?

 

[CharlesWhiteman]

This code did it. I noticed Access caused debug prior to the script runnign so it was as string level but here it is as there's not too much on this particular question.

'Update Address Details To Primary Data
Dim varPK As String
varPK = Me.txtPrimaryDataID
Dim strSQLUpdate As String
Dim varAddress1 As String
varAddress1 = Nz(Me.txtAddress1, "")
Dim varAddress2 As String
varAddress2 = Nz(Me.txtAddress2, "")
Dim VarCity As String
VarCity = Nz(Me.txtCity, "")
Dim varCounty As String
varCounty = Nz(Me.txtCounty, "")
Dim varPostCode As String
varPostCode = Nz(Me.txtPostCode, "")
strSQLUpdate = "UPDATE tblPrimaryData SET Address1 = '" & [varAddress1] & "', Address2 = '" & [varAddress2] & "',City = '" & [VarCity] & "', County = '" & [varCounty] & "', PostCode = '" & [varPostCode] & "' WHERE PrimaryDataID = " & [varPK] & ";"
DoCmd.RunSQL strSQLUpdate