New to VBA trying to write an update query

[mshellay]

Hello,
I am new to VBA and I'm trying to write a query that will update a table with dates based on user input. For example a user will run data each Monday and that date will be day01. The date table has 28 days total and I need each day row to update with the next date i.e. day01 is 12/30, day02 is 12/31, day03 is 1/01 etc..I am having issues just running the update the query. I get a too few parameters error message on the strsql statement. There are only two columns in the table, order_day(date column) and date_value(text). I want to update order_day. I also need help with creating a loop so it knows to go back and add days to the other values.

Here is what I have:

Sub Update_Dates()

Dim rs As Recordset
Dim db As Database
Dim lmsg As String
Dim transactiondate As Date
Dim strsql As String


Set db = CurrentDb()


'Query user for Transcation date
   lmsg = "Enter the Run Date (Should be Monday) __/__/____"
   lmsg = lmsg & Chr(10) & Chr(10) & "Format date as: mm/dd/yyyy"
'prompt to enter day. This should be your Monday run date

transactiondate = InputBox(lmsg)
   
'change value in date_information to transactiondate
strsql = "UPDATE Date_information set [Order_Day]=transactiondate where[Day_Value]=day01;"
db.Execute strsql, dbFailOnError
MsgBox db.RecordsAffected & " record(s) were update."

Set db = Nothing



End Sub

 

[Minty]

You need to put the day01 value in single quotes as it is a string
WHERE [Day_Value] = 'day01';"

 

[mshellay]

Hi Minty,
Thanks for the quick reply. I still get an error with too few parameters. I'm sure it has something to do with how I set the value for the date. Any thoughts?

 

[JHB]

What field type is [Day_Value], (open the table and check)?

 

[Minty]

You are probably right - if [Order_Day] is a date field you will need to format it correctly as a date in the SQL string.

I can't test this right now but you probably need to put date delimiters around your date string - something like;
#" & transactiondate & "#

 

[mshellay]

What field type is [Day_Value], (open the table and check)?

The day_value is a text column.

 

[mshellay]

You are probably right - if [Order_Day] is a date field you will need to format it correctly as a date in the SQL string.

I can't test this right now but you probably need to put date delimiters around your date string - something like;
#" & transactiondate & "#

Awesome sauce! That worked. Thanks so much!