RE: run append query from form

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

This helped for sure... but now I get an syntax error in the FROM clause:

any thoughts?

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, [" & tbl_new & "].PATIENT_SEX, [" & tbl_new & "].PATIENT_FIRST_NAME,
[" & tbl_new & "].PATIENT_LAST_NAME, [" & tbl_new & "].PATIENT_CITY, [" &
tbl_new & "].PATIENT_STATE_2, [" & tbl_new & "].MRN " & _
"FROM " & tbl_new & " " & _
"LEFT JOIN Table_Patient" & _
"ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE ((([" & tbl_new & "].PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL



End Sub



"Klatuu" wrote:

I did not scour the entire statement, but I do see one obvious error. As
written, you are putting qoutes around the name of the table in the SQL
statement. That is not correct. You only put qoutes around values you want
to insert into a text field.
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

Should be:
"FROM " & tbl_new & _
" LEFT JOIN Table_Patient ON [" & tbl_new & "].MRN = Table_Patient.MRN " & _
"WHERE (((" & tbl_new & ".PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"


--
Dave Hargis, Microsoft Access MVP


"newuser44" wrote:

I have a table to which I need to append data on a monthly basis from an
excel table that I import manually into Access. What I would like to do is
select the table on a form, click a button, and have only new records
appended to the main data table. I pretty much have all of this figured out,
but I can't seem to combine setting the table name in VBA, so that the SQL
insert query runs on the most current table I selected on the form.

Here is the VBA code I have been using:

Private Sub Command1_Click()

Dim strSQL As String
Dim tbl_new As String

tbl_new = Me![Combo2]

strSQL = "INSERT INTO Table_Patient ( DOB, PATIENT_SEX, PATIENT_FIRST_NAME,
PATIENT_LAST_NAME, PATIENT_CITY, PATIENT_STATE_2, MRN ) " & _
"SELECT DISTINCT
DateSerial(Right([PATIENT_DATE_OF_BIRTH],4),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],1,2),Left([PATIENT_DATE_OF_BIRTH],1)),IIf((Len([PATIENT_DATE_OF_BIRTH])=8),Mid([PATIENT_DATE_OF_BIRTH],3,2),Mid([PATIENT_DATE_OF_BIRTH],2,2)))
AS DOB, '" & tbl_new & "'.PATIENT_SEX, '" & tbl_new & "'.PATIENT_FIRST_NAME,
'" & tbl_new & "'.PATIENT_LAST_NAME, '" & tbl_new & "'.PATIENT_CITY, '" &
tbl_new & "'.PATIENT_STATE_2, '" & tbl_new & "'.MRN " & _
"FROM '" & tbl_new & "' " & _
"LEFT JOIN Table_Patient ON ['" & tbl_new & "'].MRN = Table_Patient.MRN " & _
"WHERE ((('" & tbl_new & "'.PATIENT_SEX) Is Not Null) And
((Table_Patient.MRN) Is Null))"

CurrentDb.Execute strSQL
End Sub



Here is the error I get:

run time error 3450
syntax error in query. incomplete query clause.


Can anybody help with this?

thanks!



.

Relevant Pages

  • Re: SQL Code
    ... As I look at it now, it looks like you have another table DEPARTURES. ... Then convert the above into a string variable and execute as below. ... Dim strSQL As String ... Private Sub CHECK OUT_AfterUpdate ...
    (microsoft.public.access.forms)
  • Re: Add a new record to a third table
    ... I'm still battleing with syntax errors in this insert into statement. ... >> Private Sub Form_AfterUpdate ... >> Dim strSql As String ...
    (microsoft.public.access.formscoding)
  • Re: SQL Code
    ... RESNO on form DEPARTURES should be available. ... Dim strSQL As String ... Private Sub CHECK OUT_AfterUpdate ...
    (microsoft.public.access.forms)
  • Filter report from pop up form
    ... know sample report collection. ... Private Sub Command28_Click ... Dim strSQL As String, intCounter As Integer ...
    (microsoft.public.access.queries)
  • Re: Find correct record after user using down arrow
    ... Syntax error in string in expression ... wizard to create following code. ... Private Sub cboFind_AfterUpdate ...
    (microsoft.public.access.forms)