help with append query in VBA

[mahhogany]

I have an excel file which I have linked to my access dabatase. I would like to link the tables to a table in my database using VBA so I can apply the code to a button and run it automatically as it is quite tedious having to do it every two weeks which is when the data is required.

Most of the columns are easy enough to append however there is a snag because there is a column that i want to append to that should only take data from several different columns based on the criteria. The code is below.

Dim SQL As String

SQL = "INSERT INTO BCV Contract register1 ( Contract no, Old Contract, " & _
"Contract Description, Supplier Name, Start Date, End Date," & _
"Original Price, Current Price, Direct Agreement, Framework, Buyer's Name )" & _
"SELECT BCV Contract register Ellipse DL.Contract #," & _
"Code DL.Old Contract #," & _
"Code DL.Contract Description," & _
"Code DL.Supplier Name, " & _
"Code DL.Actual Start, " & _
"Code DL.Ammend# Completion," & _
"Code DL.Original Price," & _
"Code DL.Current Price," & _
"Code DL.Direct Agreement, " & _
"Code DL.Framework," & _
"Code DL.Employee Name 1, Or " & _
"Code DL.Employee Name 2, Or " & _
"Code DL.Employee Name 3, Or " & _
"Code DL.Employee Name 4 Or " & _
"Code DL Requested By Name " & _
"FROM Code DL " & _
"WHERE (Code DL. Employee Type 1 = PA or Code DL. Employee Type 2= PA OR Code DL. Employee Type 3 OR Code DL. Employee Type 4")


End Sub

Please help. I was wondering if I could use an If look to insert names into the buyer's name field any ideas? How does SQL select values based on two or more criteria

 

[namliam]

You can use an IIF statement ....

 

[mahhogany]

You can use an IIF statement ....

Thanks, will that work in VBA...sorry for being such an nunce but I am quite new to this. What would the layout be like?

 

[namliam]

IIF is not VBA, it is default part of any query you make...

Syntax is something like:
IIF(Criteria;Return value if true; return value if false)

and you can nest them if you like...
IIF(Criteria;Return value if true; IIF(Criteria;Return value if true; return value if false))

Good luck, post back here if you have more questions