SQL HANGS

[DS]

This works unless the TxtCCExpDate field is left blank. If I leave that
field blank which it can sometimes be then I get a Syntax Error in Date
Query Expression. Any way of fixing this so that I can leave the
TxtCCExpDate field empty if I need to?
Thanks
DS

Me.TxtDepositID = Nz(DMax("[DepositID]", "Deposits"), 0) + 1
CurrentDb.Execute "INSERT Into
Deposits(DepositID,DepositDate,DepositAmount,Customer,Employee,Manager,Memo,DepositType,CardNumber,ExpDate)
" & _
"VALUES('" & Forms!AddDeposit!TxtDepositID & "'," & _
"#" & Forms!AddDeposit!TxtDepositDate & "#," & _
"'" & Forms!AddDeposit!TxtDepositAmount & "'," & _
"'" & Forms!AddDeposit!TxtCussID & "'," & _
"'" & Forms!AddDeposit!TxtEmployee & "'," & _
"'" & Forms!AddDeposit!TxtManager & "'," & _
"'" & Forms!AddDeposit!TxtMemo & "'," & _
"'" & Forms!AddDeposit!TxtDepositType & "'," & _
"#" & Forms!AddDeposit!TxtCCExpDate & "#," & _
"'" & Forms!AddDeposit!TxtCCNumber & "')"

 

[Dirk Goldgar]

This works unless the TxtCCExpDate field is left blank. If I leave
that field blank which it can sometimes be then I get a Syntax Error
in Date Query Expression. Any way of fixing this so that I can leave
the TxtCCExpDate field empty if I need to?
Thanks
DS

Me.TxtDepositID = Nz(DMax("[DepositID]", "Deposits"), 0) + 1
CurrentDb.Execute "INSERT Into
Deposits(DepositID,DepositDate,DepositAmount,Customer,Employee,Manager,M
emo,DepositType,CardNumber,ExpDate)
" & _
"VALUES('" & Forms!AddDeposit!TxtDepositID & "'," & _
"#" & Forms!AddDeposit!TxtDepositDate & "#," & _
"'" & Forms!AddDeposit!TxtDepositAmount & "'," & _
"'" & Forms!AddDeposit!TxtCussID & "'," & _
"'" & Forms!AddDeposit!TxtEmployee & "'," & _
"'" & Forms!AddDeposit!TxtManager & "'," & _
"'" & Forms!AddDeposit!TxtMemo & "'," & _
"'" & Forms!AddDeposit!TxtDepositType & "'," & _
"#" & Forms!AddDeposit!TxtCCExpDate & "#," & _
"'" & Forms!AddDeposit!TxtCCNumber & "')"

Please don't use the word "hangs" in describing your problem unless
whatever is happening causes the program to stop responding. It's
misleading. Just getting an error is not "hanging", nor is it
"crashing", as people sometimes write.

As for your problem: you need to check whether the control is Null, and
substitute the keyword "Null" in the SQL string in place of the date
literal you would otherwise build. For example,

[ ... ]
IIf(IsNull(Forms!AddDeposit!TxtCCExpDate), _
"Null,", _
"#" & Forms!AddDeposit!TxtCCExpDate & "#,") & _
[ ... ]

It would be better, though, to format date literals into mm/dd/yyyy
format, to avoid any ambiguity:

[ ... ]
IIf(IsNull(Forms!AddDeposit!TxtCCExpDate), _
"Null", _
Format(Forms!AddDeposit!TxtCCExpDate, "\#mm/dd/yyyy\#")) & _
"," & _
[ ... ]