|
| 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?
|
|