Date last updated

S

souchie40

I have a wee problem that I hope someone can help me with, I have a module
that runs a set of queries to update a table what I would like to do is be
able to show on the switchborad the date that this was last run any
ideas/suggestions or even a piece of code would be greatly recieved.

TIA
 
S

souchie40

Hi

I added this line

CurrentDb.Execute "INSERT INTO Planned Procurement (MyDate) SELECT Now() AS
LastUpdate;"

to my code assuming I needed to change Table1 and Expr1 to the relevant
names in my DB but on execute I get the following "Error 3134: Syntax error
in INSERT INTO statement"

Any Ideas?

TIA
 
R

Rick Brandt

souchie40 said:
Hi

I added this line

CurrentDb.Execute "INSERT INTO Planned Procurement (MyDate) SELECT
Now() AS LastUpdate;"

to my code assuming I needed to change Table1 and Expr1 to the
relevant names in my DB but on execute I get the following "Error
3134: Syntax error in INSERT INTO statement"

Any Ideas?
Click to expand...

Table or field names that include spaces (bad idea) need to be surrounded with
square brackets. You also cannot use SELECT unless you are pulling your value
FROM a table or query. You need to use the VALUES clause instead.

CurrentDb.Execute "INSERT INTO [Planned Procurement] (MyDate) VALUES (Now())"
 
A

Arvin Meyer [MVP]

I agree with the brackets on spaces but try:

INSERT INTO Table1 ( MyDate ) SELECT Now() AS Expr1;

Works just fine either as a query or with CurrentDB.Execute.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Rick Brandt said:
souchie40 said:
Hi

I added this line

CurrentDb.Execute "INSERT INTO Planned Procurement (MyDate) SELECT
Now() AS LastUpdate;"

to my code assuming I needed to change Table1 and Expr1 to the
relevant names in my DB but on execute I get the following "Error
3134: Syntax error in INSERT INTO statement"

Any Ideas?
Click to expand...

Table or field names that include spaces (bad idea) need to be surrounded with
square brackets. You also cannot use SELECT unless you are pulling your value
FROM a table or query. You need to use the VALUES clause instead.

CurrentDb.Execute "INSERT INTO [Planned Procurement] (MyDate) VALUES (Now())"
Click to expand...
 
R

Rick Brandt

Arvin said:
I agree with the brackets on spaces but try:

INSERT INTO Table1 ( MyDate ) SELECT Now() AS Expr1;

Works just fine either as a query or with CurrentDB.Execute.
Click to expand...

Hmm, perhaps I'm thinking of SQL Server or UDB400 SQL syntax. I'm certain that
I've run into cases where SELECT had to be followed by FROM. It was probably
UDB400. I know with simple SELECT queries it will not allow you to select a
literal without specifying a FROM table like SQL Server does.
 
S

souchie40

Hi,

Thanks guys that works a treat what I should of done was changed MyDate to
the field name. this gives it fot the table, what would I need to do for each
record, this is along the lines would be better for querying later but not
essential if to differcult.

MTIA

Arvin Meyer said:
I agree with the brackets on spaces but try:

INSERT INTO Table1 ( MyDate ) SELECT Now() AS Expr1;

Works just fine either as a query or with CurrentDB.Execute.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads
http://www.datastrat.com
http://www.mvps.org/access

Rick Brandt said:
souchie40 said:
Hi

I added this line

CurrentDb.Execute "INSERT INTO Planned Procurement (MyDate) SELECT
Now() AS LastUpdate;"

to my code assuming I needed to change Table1 and Expr1 to the
relevant names in my DB but on execute I get the following "Error
3134: Syntax error in INSERT INTO statement"

Any Ideas?
Click to expand...

Table or field names that include spaces (bad idea) need to be surrounded with
square brackets. You also cannot use SELECT unless you are pulling your value
FROM a table or query. You need to use the VALUES clause instead.

CurrentDb.Execute "INSERT INTO [Planned Procurement] (MyDate) VALUES (Now())"
Click to expand...
Click to expand...
 
Top