null dates with asp3.0 (pulling out hair)

I want a quick piece of script which will test a date for an empty string, in which case post NULL but if there is a date, post the date which is within a variable.

it does one of the two, i.e if the date contains a date, i use formatdatetime,2 and its fine. the problem comes when i try to set a variable to null and post it in my insert statement, or on my editing page as an update statement.

how do i post a variable which contains null, to a date field in ms access?



matt

'dateVar=date variable, may be empty string
'dateField is the name of the column in the table

sql="insert into " & tblName & " (dateField) Values ("
sql=sql & iif(dateVar<>"", dateVar ,"null")
sql=sql & ")"

Is that what you mean?

would that work then?

i didnt know you could put if statements in sql statements.

ill try it out though

thanks

no that iif is in the vbscript to build the sql statement

the sql statement is just a string so build it using standard vb string processing

actually iif may not be in vbscript - its in vb - you can either make your own iif or use an equivalent if then else. iif evaluates the first param, and if ithat's true it returns the second param else it return the third param

iif() is in vb but not vbscript. here's from my vbscript library

function iif(condition, trueval,falseval)
if condition then
iif=trueval
else
iif=falseval
end if
end function

i managed to get around the problem, by doing this:

if strRevDate > "" then
strRevDate = "'" & FormatDateTime(strRevDate,2) & "'"
else
strRevDate = "null"
end if

and then in the update statement:

Review_date = " & strRevDate & "

so, if its a date, the single quotes get posted and if not, they dont. this seems to work a treat (i did a lot of testing )

thanks for your advice, i just needed to use my brain a bit in the end.

matt