runSql

[gebuh]

I have a table with one entry- just a date that holds the last day of the month for a function that reminds the user to do something. I have some code that's supposed to change this entry via an update query- but it's not working- literally, no errors, just nothing. I tried running it in an actual query- but still nothing.
The query should update the table endCurrMonth to the last day of the month. I've tried hardcoding various dates- still nothing

DoCmd.RunSQL ("UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()), Month(Date()) + 1, 0)")

anyone know what I'm doing wrong?
thanx

 

[KenHigg]

You need to break your function stuff out of the string and concatenate them all back together again:

dim mySQLString as string
mySQLString = "UPDATE tableMetrics SET tableMetrics.endCurrMonth = " & DateSerial(Year(Date()), Month(Date()) + 1, 0) & ";")

DoCmd.RunSQL (mySQLString)

I'm not sure this is entirely correct but you get the idea...

 

[gebuh]

dim'ing the query as a string is good coding practice- but doesn't affect it working. I also created an access query from scratch and ran it with the same result- when I selected SQL view of the query it was:

UPDATE tableMetrics SET tableMetrics.endCurrMonth = DateSerial(Year(Date()),Month(Date())+1,0);

I got the same result- no change to the table.
thanx though

You need to break your function stuff out of the string and concatenate them all back together again:

dim mySQLString as string
mySQLString = "UPDATE tableMetrics SET tableMetrics.endCurrMonth = " & DateSerial(Year(Date()), Month(Date()) + 1, 0) & ";")

DoCmd.RunSQL (mySQLString)

I'm not sure this is entirely correct but you get the idea...

 

[Matt Greatorex]

I could be wrong, but I think dates have to be within ## signs?

Dim mySQLString as string
mySQLString = "UPDATE tableMetrics SET tableMetrics.endCurrMonth = #" & DateSerial(Year(Date()), Month(Date()) + 1, 0) & "#;")

DoCmd.RunSQL (mySQLString)

 

[gebuh]

I figured it out- whether you want to or not, you learn something new everyday.
I had a default value set in the table- it was defaulted to the last day of the month, when I got rid of this the query worked.

 

[KenHigg]

dim'ing the query as a string is good coding practice-

The crux of my suggestion was to parse out the function stuff...

Glad you got it working