Append Statement Fields

[DMT Dave]

Hi Guy's, i am trying to run an Append statement, this is teh easy part

Some Field names don't match in the destination table, is there an easy method to change my code here ?

I can of course use rs update or list go through all field names in the destination table, just would be rather la lenghy stsment in stead of kept short and sweet

the error i am getting is can't find field name hence the above information

sSQL4 = "INSERT INTO tblHold ( Status ) " _        
& "SELECT tblShipmentIn.*, tblShipmentIn.Status " _
        & "From tblShipmentIn " _
        & "WHERE (((tblShipmentIn.Status)= '" & sHold & "'));"
              
         DoCmd.SetWarnings False
            DoCmd.RunSQL sSQL4
        DoCmd.SetWarnings True

[Welshgasman]

I tell you this all the time...
DEbug.Print your sql and see what you have , NOT what you think you have.

How can you insert into one field from all fields in another table?

[orange]

Dave,

WHAT exactly are you trying to achieve in plain English? As Welshgasman said - your SQL syntax is wrong.
It says

INSERT tblShipmentIn.*, tblShipmentIn.Status into field Status

 a bunch of values into 1 field?????

You might want to show readers the structure of tblHold and a few records from tblShipmentIn

[DMT Dave]

Thanks guys. select (append) all to another table where status has a criteria (sHold), the destination table some fields are not the same name.

@WGM, i did debug.print everything, commented out because after testing some statements

'Debug.Print strDay & " " & dtShp & vbCrLf & "New Records In: " & iRecsAdded & vbCrLf & "Records To Update: "; iRecsUpdate & _            vbCrLf & "Delete " & iRecsAdded & vbCrLf & _
            "Append " & iRecsUpdate & " From Shipment In"

i didnt think debug.Print was relevant as it wasn't record check i was doing, it's field name differences i was trying to rule out in the destination table

@Orange, thank you, i was trying to update field names that match source fields and destination fields instead of the command finding field names that don't match

Hope i have put this across correctly |

[Welshgasman]

Fieldnames do not have to match, but MUST be in the same order.
I meant show the debug.print of the sql statement.

[DMT Dave]

Thanks WGM, yes understood, maybe better for me to update via looping through recordset 1 with a criteria to add data the the relevant fields in recordset 2

I guess this way, im only updating the required fields.

Thanks though for both yours and orange input

[DMT Dave]

Thanks WGM unserstood, maybe if i ceate a query on sorce table, add dest table then change field source, i would think i get expression where fields don't exist then remove them from the query, once i have the correct fields in the select query then use the SQL as an append

[Welshgasman]

Why do you feel you need to automate this?
We have one member who wants to reame first field in tables??

All stuff that begs the question .....WHY?

[orange]

Dave,

I don't understand your requirement. Can you mock up an example showing:
-this is before
and
-this I what I need in the output (tblHold).

[DMT Dave]

Hello Oragne, thank you, i have worked out a solujtion, instead of selecting * from Source Table and trying to append to Dest table, just created an append query.

pulled in all fields

removed fields i didn't need to update to minimize

copeid the SQL and run rhe SQL code

sorry i haven't replied, we have a family bereavment that we are sorting out so responses slow

I will makr as solved and ratehr than posting the statement, the fix was:

instead of Select *, added all fields then removed the fields not reuired for append

Thanks again to you and WGM

[Welshgasman]

Sorry to hear of your loss.

[DMT Dave]

Thank you WGM, totally aprpeciate your kind comment

yeh as above, created query, removed field that don't require update and just copied SQL and run it in the code, works fine, as per you suggest though run the sql into immerdiate window, use that a lot now then exit sub to halt and see results.

ideal to stop me messing real data up

[Welshgasman]

I tend to run any query as a select, to see what I get is what I want, then convert to update/append/delete query in qbe window.