mySql insert statement using date

[Mark]

Hi All,

Having a syntax error when using the update statement to a linked table.
Getting a 3155 error in mySQL. Any help is appreciated.

vSQL = "insert into audit_history (auditor_id, audit_date, audit_shift_code,
associate_id, wave, dept_id, error_date, error_shift_code, task_error_id,
qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod)" & _
"select [cbAudAuditor], [txAudDate], [cbAudShift], [cbAudEmp],
[txAudWave], [cbAudDept], [txAudErrDate], [cbAudErrShift], [cbAudErrID],
[txAudQty], [txAudErrQty], [txAudDesc], [txCreModDate], [txCreModDate],
[txUserLoggedIn]"

 

[Daryl S]

Mark -

You have no FROM clause in your SELECT statement - where will Access get all
those values?

You need to add the FROM [yourtablename] to the end of your SQL.

 

[Mark]

I'm getting it from text field on a form.

Mark -

You have no FROM clause in your SELECT statement - where will Access get all
those values?

You need to add the FROM [yourtablename] to the end of your SQL.

--
Daryl S

Hi All,

Having a syntax error when using the update statement to a linked table.
Getting a 3155 error in mySQL. Any help is appreciated.

vSQL = "insert into audit_history (auditor_id, audit_date, audit_shift_code,
associate_id, wave, dept_id, error_date, error_shift_code, task_error_id,
qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod)" & _
"select [cbAudAuditor], [txAudDate], [cbAudShift], [cbAudEmp],
[txAudWave], [cbAudDept], [txAudErrDate], [cbAudErrShift], [cbAudErrID],
[txAudQty], [txAudErrQty], [txAudDesc], [txCreModDate], [txCreModDate],
[txUserLoggedIn]"

 

[Daryl S]

Mark -

Then you need to pass these in as values in a way they can be evaluated
outside the double-quotes, and include the commas and delimiters within the
double-quotes. Take what I have below, but you will need to verify the field
types so the correct delimiters can be added on either side of each control
(# for dates, ' for strings assuming no apostrophes in the strings, and no
delimiter needed for numerics):

vSQL = "insert into audit_history (auditor_id, audit_date,
audit_shift_code," & _
" associate_id, wave, dept_id, error_date, error_shift_code,
task_error_id," & _
" qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod) Values (" & _
Me.[cbAudAuditor] & ",#" & Me.[txAudDate] & "#,'" & _
Me.[cbAudShift] & "'," & Me.[cbAudEmp] & ",'" & _
Me.[txAudWave] & "'," & Me.[cbAudDept] & ",#" & Me.[txAudErrDate] & "#," & _
Me.[cbAudErrShift] & "," & Me.[cbAudErrID] & "'," & _
Me.[txAudQty], Me.[txAudErrQty], Me.[txAudDesc], Me.[txCreModDate],
Me.[txCreModDate],
Me.[txUserLoggedIn] & "')"


Note that the Me. will work if this code is behind the form where the
controls are. If the code is on another form or in a report, then each 'Me'
will need to be replaced by 'Forms!<your form name>'.

--
Daryl S

I'm getting it from text field on a form.

Mark -

You have no FROM clause in your SELECT statement - where will Access get all
those values?

You need to add the FROM [yourtablename] to the end of your SQL.

--
Daryl S

Hi All,

Having a syntax error when using the update statement to a linked table.
Getting a 3155 error in mySQL. Any help is appreciated.

vSQL = "insert into audit_history (auditor_id, audit_date, audit_shift_code,
associate_id, wave, dept_id, error_date, error_shift_code, task_error_id,
qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod)" & _
"select [cbAudAuditor], [txAudDate], [cbAudShift], [cbAudEmp],
[txAudWave], [cbAudDept], [txAudErrDate], [cbAudErrShift], [cbAudErrID],
[txAudQty], [txAudErrQty], [txAudDesc], [txCreModDate], [txCreModDate],
[txUserLoggedIn]"

 

[John W. Vinson]

I'm getting it from text field on a form.

If you're inserting one record from a form, use the VALUES operator rather
than a SELECT:

vSQL = "insert into audit_history (auditor_id, audit_date, audit_shift_code,
associate_id, wave, dept_id, error_date, error_shift_code, task_error_id,
qty_aud, qty_err, comments, dtimecre, dtimemod, usrmod)" & _
"VALUES(" & Me![cbAudAuditor] & ",#" & Me![txAudDate] & #," &

etc.

This will need some tweaking - you'll need to build up the SQL string by
concatenating the values in the form controls (rather than the names of the
controls), with appropriate delimiters: commas separating the values, #
delimiters around date/time values, quotemarks ' or " around Text fields.