SQL expression INSERT INTO

[cihan]

I am using the following code to insert a table, but INSERT INTO doesnt work.
If I enter a number instead of parameter AUFNR --> ("INSERT INTO RESULTATO (Field1) VALUES ('AUFNR') "), then it inserts into the table.
Field1 and AUFNR are both defined as Double...
AUFNR is found correctly and is not null.

The following message appears
"Micrsoft office cant append all the records in the append query.....due to type conversion problem."

Any help appreciated.
----------------


Dim AUFNR As Double
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb


strSQL = ("SELECT [AUFNR] " _
& "FROM AFXX LEFT JOIN VBAP " _
& "ON VBAP.[KDAUF] = AFXX.[KDAUF] " _
& "ORDER BY [AUFNR];")

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
AUFNR = rst![AUFNR]
DoCmd.RunSQL ("INSERT INTO RESULTATO (Field1) VALUES ('AUFNR') ")

rst.Close
Set rst = Nothing

[pbaldy]

...values(" & aufnr & ")"

[ssanfu]

I am using the following code to insert a table, but INSERT INTO doesnt work.
If I enter a number instead of parameter AUFNR --> ("INSERT INTO RESULTATO (Field1) VALUES ('AUFNR') "), then it inserts into the table.
Field1 and AUFNR are both defined as Double...
AUFNR is found correctly and is not null.

The following message appears
"Micrsoft office cant append all the records in the append query.....due to type conversion problem."

Any help appreciated.
----------------
Dim AUFNR As Double
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb


strSQL = ("SELECT [AUFNR] " _
& "FROM AFXX LEFT JOIN VBAP " _
& "ON VBAP.[KDAUF] = AFXX.[KDAUF] " _
& "ORDER BY [AUFNR];")

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst
AUFNR = rst![AUFNR]
DoCmd.RunSQL ("INSERT INTO RESULTATO (Field1) VALUES ('AUFNR') ")

rst.Close
Set rst = Nothing

1) "AUFNR" is a number (double) so it doesn't require delimiters.
2) You have to concantate the variable into the SQL string.

The red are my changes.......

Dim dAUFNR As Double
Dim strSQL As String
Dim rst As Recordset
Dim dbs As Database
Set dbs = CurrentDb
       
       
strSQL = "SELECT [AUFNR]" 
       strSQL =strSQL & " FROM AFXX LEFT JOIN VBAP"
       strSQL =strSQL & " ON VBAP.[KDAUF] = AFXX.[KDAUF]" 
       strSQL =strSQL & " ORDER BY [AUFNR];"

Set rst = dbs.OpenRecordset(strSQL)
rst.MoveFirst

'you should check to ensure there is a record
dAUFNR = rst![AUFNR]

strSQL = "INSERT INTO RESULTATO (Field1) VALUES (" & AUFNR & ");"
dbs.Execute strSQL, dbFailOnError

rst.Close
Set rst = Nothing

[cihan]

Thank you pbaldy
It is solved.

[pbaldy]

Happy to help.