Update query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

Can I use an update query to make the value "" ?

UPDATE " & sTable & " SET [" & sField & "] = " & _
"'" & sValue & "' " & _
"WHERE [Date]=#" & sDate & "#;"

What if sValue is ""? How do I pass it so it empties the field value?

Thanks,
Ernst.
 
Hello,

Can I use an update query to make the value "" ?
Click to expand...

Only if the Allow Zero Length Strings property of the text field is
true.
UPDATE " & sTable & " SET [" & sField & "] = " & _
"'" & sValue & "' " & _
"WHERE [Date]=#" & sDate & "#;"

What if sValue is ""? How do I pass it so it empties the field value?
Click to expand...

It *should* set the value to NULL if the Allow Zero Length is false.
If this causes problems, you'll need to update it to the word NULL
without quotes - probably using an IF block in your code to run the
above query if there is data in sValue, or to NULL if there isn't.


John W. Vinson[MVP]
 
UPDATE " & sTable & " SET [" & sField & "] = " & _
"'" & sValue & "' " & _
"WHERE [Date]=#" & sDate & "#;"
Click to expand...
Click to expand...
It *should* set the value to NULL if the Allow Zero Length is false.
If this causes problems, you'll need to update it to the word NULL
without quotes - probably using an IF block in your code to run the
above query if there is data in sValue, or to NULL if there isn't.
Click to expand...

VBA wont allow me to set it to Null. If I set it to "" then it gives me this:

ActionQuery error: Data type mismatch in criteria expression.


The expression (SQL) being passed is :

UPDATE tDayData SET [Daily Sales] = '' WHERE [Date]=#1/1/2005#;

Those are 2 single quotes... I am passing this from Excel using ADO if that
matters...

Ernst.
 
If it matters it works on a Text field but not in the Currency field...
and strange things happen when used on a memo field...

Ernst.
 
VBA wont allow me to set it to Null. If I set it to "" then it gives me this:

ActionQuery error: Data type mismatch in criteria expression.
Click to expand...

Try Dim sValue As Variant (which accepts NULL) rather than Dim sValue
As String.

John W. Vinson[MVP]
 
ActionQuery error: Data type mismatch in criteria expression.
Try Dim sValue As Variant (which accepts NULL) rather than Dim sValue
As String.
Click to expand...

Thanks. Got it.

Ernst.
 
Back
Top