By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,656 Members | 968 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,656 IT Pros & Developers. It's quick & easy.

How to write null value to table using sql statement?

P: n/a
TD
I have an unbound form that adds and updates records to one table. I
use an INSERT sql statment to add a new record and a UPDATE statement
to update a record. I created a function named C2F to check each field
for null. If the field is null I need to write the value null to the
table. This is a sniplet of the UPDATE sql statement:

sql = sql & "SSP4_Cut = '" & C2F(SSP4_Cut) & "', "

that is:

sql = sql<space> &<space><dbl quote>SSP4_Cut<space>=<space><single
quote><dbl quote><space>&<space>C2F(SSP4_Cut)<space>&<space>< dbl
quote><single quote>,<space><dbl quote>

This is C2F:

Public Function C2F(ctl As Control) As Variant

C2F = IIf(Nz(ctl, "") = "", "Null", ctl)

End Function

Those are dbl quotes around the word Null. To get this to work I need
to remove the single quotes in the sql statement, but then I need them
if the field is not null. So I was trying to rewrite C2F to this

Public Function C2F(ctl As Control) As Variant

IIf(IsNull(ctl.value),"Null","'" & ctl.value & "'")

End Function

The false part is <single quote><dbl
quote><space>&<space>ctl.value<space>&<space><dbl quote><single quote>.

This produces the error Compile Error expected expression. With this
arrangement then the sql statement would look like:

sql = sql & "SSP4_Cut = C2F(SSP4_Cut) & ", "

How can I do this?

Apr 6 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
TD
Anyone?

I really need help with this one!!!

Apr 20 '06 #2

P: n/a
one way is to use a recordset. Then you wouldn't even have to specify
the value of the field.

With rs
.AddNew
.Fields("TextField")="something"

..Fields("NumericField")=Forms![SomeOpenForm].Controls("SomeControlName")
.Fields("FieldAllowingNulls")=Null 'or just don't assign a
value if the field is not required
.UPDATE
end with

Apr 20 '06 #3

P: n/a
Construct your SQL strings so that they name only the fields for which
a value has been entered.

Apr 20 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.