Printable View
If wkStationID = 1 and DistSerb(0) = "", then the statement:
SQLString = "UPDATE StationDet2 SET " _
& "SuperS = " & DistSerb(0) & " " _
& "WHERE Station_ID = " & wkStationID(ListChoice) & ";"
returns SQLString as "UPDATE StationDet2 SET SuperS = WHERE Station_ID = 1"
and this gives me a syntax error.
How can I create SQLString that if DistSerb(0) = "", the SQLString is "UPDATE StationDet2 SET SuperS = " " WHERE Station_ID = 1" ?
All take advantage of the IIF keyword in VB.
Note that the first two examples have the same output only one is easier to read than the other. Too many "''"'"'" in a line make your eyes gor weird...
The third example works assuming that Null is an allowable value for the field in your table. I would prefer to use Null rather than a space because unless a space is meaningful in your DB, it is ambiguous. For instance, Null means no text at all. Whereas " " means valid text.
Anyhow, see how you go.
Code:
sqlstring = "UPDATE StationDet2 SET " _
& "SuperS = " & IIf(DistSerb(0) = "", "' '", DistSerb(0)) & " " _
& "WHERE Station_ID = " & wkStationID & ";"
sqlstring = "UPDATE StationDet2 SET " _
& "SuperS = " & IIf(DistSerb(0) = "", Chr$(39) & Chr$(32) & Chr$(39), DistSerb(0)) & " " _
& "WHERE Station_ID = " & wkStationID & ";"
Debug.Print sqlstring
sqlstring = "UPDATE StationDet2 SET " _
& "SuperS = " & IIf(DistSerb(0) = "", "Null", DistSerb(0)) & " " _
& "WHERE Station_ID = " & wkStationID & ";"
Debug.Print sqlstring
Regards
Thank you very much.
Sandra