If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL HANGS
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,Custo mer,Employee,Manager,Memo,DepositType,CardNumber,E xpDate) " & _ "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 & "')" |
#2
|
|||
|
|||
SQL HANGS
"DS" wrote in message
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,Custo mer,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\#")) & _ "," & _ [ ... ] -- Dirk Goldgar, MS Access MVP www.datagnostics.com (please reply to the newsgroup) |
Thread Tools | |
Display Modes | |
|
|