Append Query Help

[benkingery]

I'm importing orders into a temporary table. There are two types of records that will be imported. 1) Header records (IH), 2) Detail records (ID). As soon as the records are into the temporary table, I am appending the Header records to the header table, and the detail records to the detail table.

Here's the problem. The source data file looks like this:

IH|AF245975|2006-06-05|Adam Reczkowski|3 Berk St||Boston|MA|02199|US|617-555-5552
ID|4056|4|55.50|Floor Lamp|Ground|Test Comment
ID|BC456|1|17.75|Hall Lamp|Ground|Test Comment 2

You can see that there is no foreign key reference on the detail records to tie them to he header record. This is a problem, because in their normalized tables, the detail table has a foreign key reference to the header record value in the second column ("AF245975" in this instance).

Each file that I import will have only 1 header record and the detail records that go with it, so there's no possibility of mixing up header and detail records. The append query to the header table works fine, but the append query to the child table is giving me problems when put into a VBA module. If I try just the SQL for the Detail append query, I have NO problems. Its only when the SQL is run from VBA that there is a problem. I suspect it has something to do with the ' and " around Expr 1.

Can anyone offer some syntax help here? I think thats the problem.

ApdCSN_OrderDetail = "INSERT INTO ORDERS_CSN_OrderDetail ( Record_Identifier, Item_Number, Quantity, Wholesale_Price, Item_Description, Ship_Method, Custom_Comments, PO_Number )"
ApdCSN_OrderDetail = ApdCSN_OrderDetail + " SELECT ORDERS_CSN_OrderBuffer.CSNOB_01, ORDERS_CSN_OrderBuffer.CSNOB_02, ORDERS_CSN_OrderBuffer.CSNOB_03, ORDERS_CSN_OrderBuffer.CSNOB_04, ORDERS_CSN_OrderBuffer.CSNOB_05, ORDERS_CSN_OrderBuffer.CSNOB_06, ORDERS_CSN_OrderBuffer.CSNOB_07,  DLookUp('[CSNOB_02]','ORDERS_CSN_OrderBuffer','[CSNOB_01]='IH'') AS Expr1"
ApdCSN_OrderDetail = ApdCSN_OrderDetail + " FROM ORDERS_CSN_OrderBuffer WHERE (((ORDERS_CSN_OrderBuffer.CSNOB_01)='ID'))"

FYI, the error I'm gettng reads: "Run-tim error '3075': Syntax error (Missing operator) in query expression 'DLookUp('[CSNOB_02]','ORDERS_CSN_OrderBuffer','[CSNOB_01]='IH''

 

[pbaldy]

Personally I probably would have processed the original file with VBA as suggested on your other thread, but I suspect your problem can be solved by concatenating in the DLookup value:

ApdCSN_OrderDetail = ApdCSN_OrderDetail + " SELECT ..." & DLookup(...) & "the rest of it"

and using normal quoting within the DLookup.

 

[benkingery]

Thanks for the reply. I'm not sure I'm following your syntax in your example. Is the Dlookup part of the actual Append query or something done after the fact?

Also, I chose not to do as the original post indicated because I will only have 1 header record with the detail records that go with it per order file. That makes running the VBA on each order file a little difficult. If you think I'm bad as Access VBA (you're right), then imagine how bad where I have ZERO experience. I wasn't even sure where to begin with that suggestion.

Anyway, thanks again for your help. I do appreciate it.

 

[pbaldy]

You have the DLookup itself as part of the SQL, and I'm suggesting inserting the resulting value into the SQL instead. You would also need single quotes around the value, since it's text. Try this line:

ApdCSN_OrderDetail = ApdCSN_OrderDetail + " SELECT ORDERS_CSN_OrderBuffer.CSNOB_01, ORDERS_CSN_OrderBuffer.CSNOB_02, ORDERS_CSN_OrderBuffer.CSNOB_03, ORDERS_CSN_OrderBuffer.CSNOB_04, ORDERS_CSN_OrderBuffer.CSNOB_05, ORDERS_CSN_OrderBuffer.CSNOB_06, ORDERS_CSN_OrderBuffer.CSNOB_07,  '" & DLookUp("[CSNOB_02]","ORDERS_CSN_OrderBuffer","[CSNOB_01]='IH'") & "' AS Expr1"

 

[boblarson]

ramson001 reported for spam

 

[benkingery]

You got it! This is exactly what I needed.

Thanks for the help.

 

[pbaldy]

No problemo!