Update a date/time field?

  • Thread starter Thread starter John Keith
  • Start date Start date
J

John Keith

How do I make this work?

Dim SQL as String
SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = '#" & Now() & "#' " & _
"WHERE ProcessAnalysis.ID = 1"
SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = #4/2/2008 9:57:23 AM# " & _
"WHERE ProcessAnalysis.ID = 1"
DoCmd.RunSQL SQL

Both SQL assigns casue an enter paramter value dialog to popup??? why?

If I try putting #4/2/2008 9:57:23 AM# into the paramter box; I get run-time
error 3113 Cannot update 'Start'; field not updatable

What am I doing wrong?
 
John Keith said:
How do I make this work?

Dim SQL as String
SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = '#" & Now() & "#' " & _
"WHERE ProcessAnalysis.ID = 1"
SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = #4/2/2008 9:57:23 AM# " & _
"WHERE ProcessAnalysis.ID = 1"
DoCmd.RunSQL SQL

Both SQL assigns casue an enter paramter value dialog to popup??? why?

If I try putting #4/2/2008 9:57:23 AM# into the paramter box; I get
run-time
error 3113 Cannot update 'Start'; field not updatable

What am I doing wrong?
Click to expand...


So what is the name of the parameter being request by the parameter dialog?
You'll get a parameter dialog any time your SQL uses a name the the query
engine and expression service can't resolve. Maybe you misspelled the name
of the table or one of its fields.

This statement:
SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = '#" & Now() & "#' " & _
"WHERE ProcessAnalysis.ID = 1"
Click to expand...

is wrong because you put quotes around the date literal. It should be (at
least):

SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = #" & Now() & "# " & _
"WHERE ProcessAnalysis.ID = 1"

To avoid any locale-based ambiguity, you should explicitly format the date
literal:

SQL = "UPDATE ProcessAnalysis " & _
"SET ProcessAnalysis.Start = #" & _
Format(Now(), "mm/dd/yyyy hh:nn:ss ampm") & "# " & _
"WHERE ProcessAnalysis.ID = 1"

But I don't see why your second version of the statement doesn't work, if
all the field names are spelled properly.
 
The # on its own is the correct format. Otherwise, it doesn't recognize the
date as a date and pops up the dialog to ask for the value of what it thinks
is a paramenter.

As to the error you get when you use the correct syntax, is ProcessAnalysis
a table or a query? If it's a table, then perhaps your database is
read-only or you've locked the table/record in some fashion?

If it's a query, there are a number of possible causes including queries
that use GROUP BY, calculated fields sometimes, joins that are too complex
for Access to figure out, etc. If so, and you can't figure out the cause,
post the SQL of ProcessAnalysis.


Rob
 
Back
Top