DoCmd.RunSQL

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

Guest

How can I get the Zero to be Null or Empty or Text? CompletedDate is a Text
Field
I thought "" or "Not updated" would get it but error's out
DoCmd.RunSQL "update tblPayrollCompletion set CompletedDate = 0"

Thanks for any help
 
Hi Mike,

I won't ask why you have a completed date as a text datatype, rather than a
datetime datatype. But given that it IS a text datatype, the following will
work:

DoCmd.RunSQL "UPDATE tblPayrollCompletion SET CompletedDate = '0'"
will set CompletedDate to the text string '0'

DoCmd.RunSQL "UPDATE tblPayrollCompletion SET CompletedDate = Null"
will set CompletedDate to null

DoCmd.RunSQL "UPDATE tblPayrollCompletion SET CompletedDate = ''"
(the end of that line is singlequote singlequote doublequote)
will set CompletedDate to a zero-length text string (which is not the same
as null)

DoCmd.RunSQL "UPDATE tblPayrollCompletion SET CompletedDate = 'Not updated'"
will set CompletedDate to the text string 'Not updated'

You pays your money, you takes your choice ;-)

More explanation: Since the field is a text field, you must surround the new
value with text delimiters (except for the Null constant). In general, you
can use either singlequote or doublequote characters as the delimiter; here,
since you are already using the doublequote character to delimit the SQL
string, you need to use singlequote characters within that string.

HTH,

Rob
 
Hi Rob

Thanks alot
A little explanation. The reason for CompletedDate being a Text Field is
that,
The Form is only used to show the Date that a Region's Payroll is ready for
processing
Person doing payroll has a button to clear all checks and I needed Null or
an Empty string

I have been working with Excel, and Access is a little different

Thanks for the help
 
A little explanation. The reason for CompletedDate being a Text Field is
that,
The Form is only used to show the Date that a Region's Payroll is ready for
processing
Person doing payroll has a button to clear all checks and I needed Null or
an Empty string
Click to expand...

A Date/Time field can be set to NULL (though not to an empty string).

John W. Vinson [MVP]
 
Back
Top