CODEWITHSUNDEEP
sqlvbams-access
CodeK
CodeK

Reputation: 1

Why is my update query passing incorrect information to the table?

I have an SQL statement in VBA that when i run it, it updates my table with incorrect information. I've been struggling with this code for over a week trying workarounds and debugging but to no avail. I've searched online and found nothing even close to this. 

DIM SQL as String
DIM periodStart as Date
DIM periodEnd as Date

periodStart = DateSerial(Year(Date), 12, 1)
periodEnd = DateSerial(Year(Date), 12, 15)

MsgBox "Period Start: " & periodStart & " Period End: " & periodEnd

SQL = "UPDATE EmpTime SET EmpTime.beginning = " & periodStart & " & EmpTime.ending = " & periodEnd & ";"
DoCmd.RunSQL SQL

The above code gives me a message box that shows me the periodStart and periodEnd variables are being built properly but then when i look to the table, the information is not the same as the Message box.

MsgBox

Table

Why is this happening and what can I do to fix it/avoid it ?

Upvotes: 0

Views: 69

Answers (2)

user8221156
user8221156

Reputation: 

SQL = "UPDATE EmpTime SET EmpTime.beginning = #" & periodStart & "#, EmpTime.ending = #" & periodEnd & "#"

Upvotes: 0

JNevill
JNevill

Reputation: 50009

What I think is happening here is that your SQL is shaking out to be:

UPDATE EmpTime SET EmpTime.beginning = 12/1/2019, EmpTime.ending = 12/15/2019;

Access is not super amazing at guessing your intentions when you just send it math problems like this. Because it doesn't recognize your first date as a properly formatted string (12/01/2019 would be more appropriate) it is making the educated guess that you literally wanted to divide 12 by 1 by 2019. Which results in a decimal, or a very early time of the first date that MS Access can record: 12/30/1899 (like 12:05am, but there is no time dimension in play so it's dropped).

Instead try:

UPDATE EmpTime SET EmpTime.beginning = #" & Format(periodStart, "mm/dd/yyyy") & "# & EmpTime.ending = #" & Format(periodEnd, "mm/dd/yyyy") & "#;"

This does two things:

  1. It formats (using the Format() function) your date into something access will recognize on its own.
  2. It surrounds the date in # which is the microsoft office-y way of saying "This is explicitly a date, treat it as such or throw an error". Which is a much better scenario then "Guess what I meant when I send you this math/date"

Lastly, as Gordon mentions, and I also HIGHLY recommend is to switch this code over to use parameterized inputs in your SQL. here is a good write up of what that looks like. This solves two issues in your current code

  1. Your malformed date would most likely error on being assigned to the correctly typed parameter before the SQL was executed alerting you that you have a bad date. (no guessing what went wrong and no bad data hitting your database)
  2. You are protected from SQL Injection by users of your workbook. I assume this is not a super important facet of your workbook/application though since this is probably an internal company or personal thing and everyone using it can be trusted, but I am always in favor of hardening your code as best as possible since it's just good practice.

Upvotes: 2

Related Questions