Inconsistent results - append query

[Chace]

I am baffled by this problem. When I execute an append query in vba by the
db.execute strSQL, dbfailonerror command I am only getting 76 out of the
expected 81 records dumped to the destination table. I debug.printed the
strSQL statement and pasted it in the SQL query design window and viewed it
in design view. Looked great. When I run it from there I get all 81 records
as expected! I deleted the records from the destination table and tried
again….same problem. I have done this multiple times, compiled the vba code,
compacted the database multiple times, but problem persists. Any idea what
could be going on?

Thanks,
Chace

 

[Jerry Whittle]

Please post the VBA code and SQL statement.

 

[Chace]

The vba code is as follows:
db.Execute CurrentDb.QueryDefs("qapnd_OrderandWTOs").SQL, dbFailOnError

The Select Query’s SQL that is used in the above Append query is as follows:
SELECT dbo_so_dtl_tbl.sa_created_by, dbo_so_dtl_tbl.sa_modified_by,
dbo_so_dtl_tbl.so_dtl_key, dbo_so_dtl_tbl.so_dtl_stats,
dbo_so_prod_tbl.im_pack_key, dbo_so_dtl_tbl.so_dtl_cpono,
dbo_so_dtl_tbl.so_hdr_key, dbo_so_dtl_tbl.so_dtl_shpws,
dbo_en_ship_tbl.en_ship_name, dbo_so_dtl_tbl.so_dtl_shsdt,
[dbo_en_item_tbl].[en_item_key] & " " & [dbo_en_item_tbl].[en_item_desc] AS
Item, dbo_en_item_tbl.en_item_key, dbo_en_item_tbl.en_item_desc,
-Sum(IIf([dbo_so_prod_tbl].[im_pack_key]="
",1,[dbo_en_itmpk_tbl].[en_itmpk_filqt])*[so_dtl_ordqt]) AS Amount,
dbo_en_item_tbl.en_class_key
FROM (((dbo_so_dtl_tbl INNER JOIN dbo_so_prod_tbl ON
dbo_so_dtl_tbl.in_prod_key = dbo_so_prod_tbl.so_prod_key) LEFT JOIN
dbo_en_itmpk_tbl ON (dbo_so_prod_tbl.im_pack_key =
dbo_en_itmpk_tbl.im_pack_key) AND (dbo_so_prod_tbl.in_item_key =
dbo_en_itmpk_tbl.en_item_key)) LEFT JOIN dbo_en_item_tbl ON
dbo_so_prod_tbl.in_item_key = dbo_en_item_tbl.en_item_key) LEFT JOIN
dbo_en_ship_tbl ON dbo_so_dtl_tbl.ar_ship_key = dbo_en_ship_tbl.en_ship_key
WHERE (((dbo_so_dtl_tbl.so_dtl_shpdt) Is Null) AND
((dbo_so_dtl_tbl.so_dtl_stats) Not Like 'C*') AND
((dbo_so_dtl_tbl.gl_cmp_key)='SR'))
GROUP BY dbo_so_dtl_tbl.sa_created_by, dbo_so_dtl_tbl.sa_modified_by,
dbo_so_dtl_tbl.so_dtl_key, dbo_so_dtl_tbl.so_dtl_stats,
dbo_so_prod_tbl.im_pack_key, dbo_so_dtl_tbl.so_dtl_cpono,
dbo_so_dtl_tbl.so_hdr_key, dbo_so_dtl_tbl.so_dtl_shpws,
dbo_en_ship_tbl.en_ship_name, dbo_so_dtl_tbl.so_dtl_shsdt,
[dbo_en_item_tbl].[en_item_key] & " " & [dbo_en_item_tbl].[en_item_desc],
dbo_en_item_tbl.en_item_key, dbo_en_item_tbl.en_item_desc,
dbo_en_item_tbl.en_class_key;

I did find that by adding a criteria for the en_class_key solved the
problem, but I still don’t know why I was getting the inconsistent results to
begin with. So for now I seem to have resolved the problem, but if you have
any insight into the root of the issue I would appreciate it.

Thanks,
Chace