sqlvbadatems-access

Date/Time Format VBA Access Query

I am new to Access and I have a code here below that is supposed to insert the current month to a table through the current date I have in my pc subtracted with 1 hour:

strSQL = "INSERT INTO [mytable] ([monthColumn]) "
strSQL = strSQL & "VALUES ("
strSQL = strSQL & "#" & Format(Now - (1 / 24), "mmmm") & "#"
strSQL = strSQL & ");"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

I also tried this:

strSQL = "INSERT INTO [mytable] ([monthColumn]) VALUES (#" & Format(Now - (1 / 24), "mmmm") & "#);"

DoCmd.SetWarnings False
DoCmd.RunSQL (strSQL)
DoCmd.SetWarnings True

For both codes, I am getting this error:

Image1

I only derived the codes above from a working code below:

strSQL = "INSERT INTO [mytable] ([monthColumn]) VALUES (#" & Now() & "#);"

What could be missing? Thanks in advance.

Solution

  • A column of 'Date' data type holds a valid date with its constituents: 'day', 'month' and 'year'. A month name is not a valid date, it is just 'text'. You can't store just text (even if it is an abbreviation of a month name) in a date column. Store it in a column with type: text.