Remove Date from a Record using SQL VBA

[Bigmo2u]

I am trying to do an update query via vba sql . I have tried setting the variable to Null and to "" and throws an error, I am sure that this is an easy solution, but my brain is fried and tried searching see if anyone has had this issue.

With db
    .Execute "UPDATE tblSerialNumber " & _
             "Set ModelID = " & mdi & ", PickupLocationID = " & pul & ", DeliveredByID = " & dbi & ", " & _
             "PickupTime = #" & pkt & "#, PickupDate = #" & pud & "#, ArrivalDate = #" & ald & "#, " & _
             "ArrivalTime = #" & ald & "#, ArrivalTime = #" & alt & "#, eDriverID = " & edi & ", " & _
             "CurrentLocation ID = " & cli & ", ReturnLocationID = " & rli & ", DepartureDate =  " & _
             "#" & dpd & "#, DepartureTime = #" & dpt & "# " & _
             "WHERE SerialNumberID = '" & ssn & "'"
End With

The ones that are giving me issues is dpd and dpt, they are declared dates. I don't want to delete the record just remove the date and time from the record.

Thank you in advance for the help.

 

[vbaInet]

Re: Remove Date rom a Record using SQL VBA

You want to set a particular field within a record to Null?

 

[Bigmo2u]

Re: Remove Date rom a Record using SQL VBA

Basically yes, If there is a date in that field I want the date removed and to be blank or Null as you put it.

 

[boblarson]

Basically yes, If there is a date in that field I want the date removed and to be blank or Null as you put it.

Blank (empty string) is different from Null.

Date/Time fields can either have a value or be Null, you can't store an empty string in one.

 

[Bigmo2u]

Empty string would be nice, but it is a date field in table.

 

[vbaInet]

1. Add extra criteria to get those records with non-empty dates. That is,

... AND Not [DateField] Is Null

2. Set the field to Null in your query

"Set [DateField] = Null, " & ...etc

Note how that is written above.

 

[Bigmo2u]

I tried

dpd = Null
dpt = Null

and it said invalid use of Null.

Would SQL look like this:

DepartureDate = Null, DepartureTime = Null

In SQL string

 

[vbaInet]

I can't see how or where it would throw that error. Let's see the full SQL statement.

 

[Bigmo2u]

With db
    .Execute "UPDATE tblSerialNumber " & _
             "Set ModelID = " & mdi & ", PickupLocationID = " & pul & ", DeliveredByID = " & dbi & ", " & _
             "PickupTime = #" & pkt & "#, PickupDate = #" & pud & "#, ArrivalDate = #" & ald & "#, " & _
             "ArrivalTime = #" & ald & "#, ArrivalTime = #" & alt & "#, eDriverID = " & edi & ", " & _
             "CurrentLocation ID = " & cli & ", ReturnLocationID = " & rli & ", DepartureDate =  " & _
             "#" & dpd & "#, DepartureTime = #" & dpt & "# " & _
             "WHERE SerialNumberID = '" & ssn & "'"
End With

 

[vbaInet]

That's the same query you have in your first post. I would like to see your amended query based on my suggestions in post #6. You haven't written DepartureDate = Null there.

Perhaps what you're doing is setting the value of the variable to Null and trying to assign it to the SQL string, but that wasn't what I advised.

 

[boblarson]

You don't need the variables. Just

DepartureDate = Null, ..etc

 

[Bigmo2u]

Sorry vbaInet, I miss understood. it now looks like:

With db
    .Execute "UPDATE tblSerialNumber " & _
             "Set ModelID = " & mdi & ", PickupLocationID = " & pul & ", DeliveredByID = " & dbi & ", " & _
             "PickupTime = #" & pkt & "#, PickupDate = #" & pud & "#, ArrivalDate = #" & ald & "#, " & _
             "ArrivalTime = #" & ald & "#, ArrivalTime = #" & alt & "#, eDriverID = " & edi & ", " & _
             "CurrentLocation ID = " & cli & ", ReturnLocationID = " & rli & ", DepartureDate =  " & _
             "Null, DepartureTime = Null " & _
             "WHERE SerialNumberID = '" & ssn & "'"
End With

 

[vbaInet]

Is it working now? And I noticed that "CurrentLocation ID" has a space between them. Is this correct?

 

[Bigmo2u]

Not only did I have a space there I had ArrivalTime twice... i swear my brain is fried. Thank you vbaInet and Boblarson, the code works as intened! Thank you so much!

 

[vbaInet]

You might find it helpful to first build your query in the Query Builder/Designer then get a copy of the SQL statement by going to SQL View. That will help you to avoid problems like this.

Happy developing!