INSERT QUERY 3

[manny1234]

I need to be able to insert a date into a table with a VB script in access like so...

DoCmd.RunSQL "INSERT INTO History (MID,Date) SELECT " & Me![Merchant ID Number] & ",Date()"

Not sure how to set up the syntax

 

[Remou]

How about:
[tt]DoCmd.RunSQL "INSERT INTO History (MID,Date) SELECT " & Me![Merchant ID Number] & ", #" & Date() & "#"[/tt]

 

[RoyVidar]

Try the following string:

[tt]"INSERT INTO History ([MID],[Date]) Values (" & Me![Merchant ID Number] & ",Date())"[/tt]

Note - both mid and date are reserved words, they are both functions, and shouldn't be used as names of objects. Using bracketes might be a sufficient workaround.

Roy-Vidar

 

[Golom]

You are attempting to INSERT from variables in your system ... not from a table as implied by SELECT ...

DoCmd.RunSQL "INSERT INTO History (MID,Date) " & _
" VALUES(" & Me![Merchant ID Number] & ", #" & Date() & "#)"

 

[manny1234]

excellent thanks!

DoCmd.RunSQL "INSERT INTO History ([MID],[Date]) VALUES (" & Me![Merchant ID Number] & ",#" & Date & "#)"

... did the trick

 

[RoyVidar]

Then you're probably on US, settings? Be aware that Jet expects dates in US/unambiguous format, for instance using ISO 8601

[tt]...#" & format$(date, "yyyy-mm-dd") & "#)"[/tt]

See for instance

http://allenbrowne.com/ser-36.html

or

http://www.mvps.org/access/datetime/date0005.htm

Roy-Vidar