Data Type Mismatch Error

[vikramonline]

I have a table called tblFlight_Schedule in MS-Access which has got two fields arrival time and departure time (both date/time fields and not reqired).I want to update the details of a flight online.

sql = "update tblFlight_Schedule set Origin = '" & org & "',Destination='"
sql = sql & destination & "',Flight_No='" & flt & "',Category='"
sql = sql & category
sql = sql & "',DepartureTime = '" & deptime
sql = sql & "',ArrivalTime = '" & arrtime & "'"

It works fine when both arrival time and departure time is given,but if anyone is left blank I get a Data type mismatch error.

I know I am doing something stupid somewhere.

Please advise.

 

[vongrunt]

When field is left blank, append "NULL" (without '') to sql string.

 

[vikramonline]

Thanx vongrunt,

How do u append null to sql string ??

 

[vongrunt]

You'll probably need this feature frequently - isolate it in function:

...
sql = sql & "', DepartureTime=" & sqlNullStr(deptime) & ", ArrivalTime=" & sqlNullStr(arrtime)
...
Function sqlNullStr( vData )
	vData = Trim(vData)
	If vData <> "" Then	sqlNullStr = "'" & vData & "'" Else sqlNullStr = "NULL"
End Function

 

[vikramonline]

I already got it working.

Thanx a lot for all ur input.U have been a great help.