'Insert Into' help needed

[Tim A.]

Previously posted as Set an open file flag...


I think I found the answer but I am having trouble with
the format. Can someone assist?? Thanks.


INSERT INTO Dispositions([Date, [EventID, [Disposition]]])
VALUES ([=DATE(), [Me.EventID, [Opened]]])


The table is called Dispositions, field 1 is Date, field 2
is EventID and field 3 is Disposition.

The values I want to add are:

field 1 Current date
field 2 EventID from the form being saved
field 3 The text 'Opened'


Thanks.

 

[Graham Mandeno]

Hi Tim

You have some very creative arrangements of square brackets here!

As you are referring to Me.EventID, I assume this is running from an event
procedure on your form. The SQL interpreter will have no idea what
"Me.EventID" means in the string, so you must substitute the actual value.
So, what you need is:

CurrentDb.Execute _
"INSERT INTO Dispositions([Date], EventID, Disposition) " _
& "VALUES (Date(), " _
& Me.EventID _
& ", 'Opened')"

If EventID is a text field then you must enclose the value in quotes, so the
penultimate line would read:
& "'" & Me.EventID & "'" _

The brackets around [Date] are there because Date is a reserved name. I
recomment you change the name od the field to EventDate or something else
appropriate, because it will only cause you pain further down the track.

 

[Paul Byford]

This has solved a similar problem I was having, however, the DATE() in
the Values portion of the statement does not work. Access 97 kicks out
the brackets following DATE, therefor the date inserted is incorrect.

Any ideas how one might correct the date?

Would there be a correct solution such as:

Dim InsertDate as string

InsertDate = DATE()



Thanks.

Actually...let me rewrite this as if you would be executing this from code:

Private Sub MyProcedureName
Dim sSQL as string

sSQL = "INSERT INTO Dispositions([Date], [EventID], [Disposition]) " & _
"VALUES (" & DATE() & ", " & Me.EventID & ", 'Opened');"

CurrentDb.Execute sSQL

End Sub


:

Previously posted as Set an open file flag...


I think I found the answer but I am having trouble with
the format. Can someone assist?? Thanks.


INSERT INTO Dispositions([Date, [EventID, [Disposition]]])
VALUES ([=DATE(), [Me.EventID, [Opened]]])


The table is called Dispositions, field 1 is Date, field 2
is EventID and field 3 is Disposition.

The values I want to add are:

field 1 Current date
field 2 EventID from the form being saved
field 3 The text 'Opened'


Thanks.