using multiple parameters in append query in Access 2010

Question

I have been trying to get an append query to work but I keep getting an error stating that 0 rows are being appended whenever I use more than 1 parameter in the query. This is for a

The table in question has 1 PK which is a GUID [which is generating values with newid()] and one required field (Historical) which I am explictly defining in the query.

INSERT INTO dbo_sales_quotas ( salesrep_id
, [year]
, territory_id
, sales_quota
, profit_quota
, product_super_group_uid
, product_super_group_desc
, class_9
, Historical
, sales_quotas_UID )

SELECT dbo_sales_quotas.salesrep_id
, dbo_sales_quotas.Year
, dbo_sales_quotas.territory_id
, dbo_sales_quotas.sales_quota
, dbo_sales_quotas.profit_quota
, dbo_sales_quotas.product_super_group_uid
, dbo_sales_quotas.product_super_group_desc
, dbo_sales_quotas.class_9
, dbo_sales_quotas.Historical
, dbo_sales_quotas.sales_quotas_UID

FROM dbo_sales_quotas
WHERE (((dbo_sales_quotas.salesrep_id)=[cboSalesRepID]) 
AND ((dbo_sales_quotas.Year)=[txtYear]) 
AND ((dbo_sales_quotas.territory_id)=[txtTerritoryID]) 
AND ((dbo_sales_quotas.sales_quota)=[txtSalesQuota]) 
AND ((dbo_sales_quotas.profit_quota)=[txtProfitQuota]) 
AND ((dbo_sales_quotas.product_super_group_uid)=[cboProdSuperGroup]) 
AND ((dbo_sales_quotas.product_super_group_desc)=[txtProductSuperGroupDesc]) 
AND ((dbo_sales_quotas.class_9)=[cboClass9]) 
AND ((dbo_sales_quotas.Historical)='No') 
AND ((dbo_sales_quotas.sales_quotas_UID)='newid()'));

Even if I assign specific values, I still get a 0 rows error except when I reduce the number of parameters to 1 (which it then works perfectly regardless of which parameter) I have verified that the parameters have the correct formats.

Can anyone tell me what I'm doing wrong?

Maybe it's a simple case of no matching rows.

Dan Bracuk
– Dan Bracuk

2013-07-05 17:11:03 +00:00
Commented Jul 5, 2013 at 17:11 — Dan Bracuk
– Dan Bracuk, Commented Jul 5, 2013 at 17:11

HansUp · Accepted Answer · 2013-07-05 17:17:00Z

1

Break out the SELECT part of your query and examine it separately. I'll suggest a simplified version which may be easier to study ...

SELECT 
    dsq.salesrep_id,
    dsq.Year,
    dsq.territory_id,
    dsq.sales_quota,
    dsq.profit_quota,
    dsq.product_super_group_uid,
    dsq.product_super_group_desc,
    dsq.class_9,
    dsq.Historical,
    dsq.sales_quotas_UID
FROM dbo_sales_quotas AS dsq
WHERE
        dsq.salesrep_id=[cboSalesRepID]
    AND dsq.Year=[txtYear]
    AND dsq.territory_id=[txtTerritoryID]
    AND dsq.sales_quota=[txtSalesQuota]
    AND dsq.profit_quota=[txtProfitQuota]
    AND dsq.product_super_group_uid=[cboProdSuperGroup]
    AND dsq.product_super_group_desc=[txtProductSuperGroupDesc]
    AND dsq.class_9=[cboClass9]
    AND dsq.Historical='No'
    AND dsq.sales_quotas_UID='newid()';

I wonder about the last 2 conditions in the WHERE clause. Is the Historical field type bit instead of text? Does the string 'newid()' match sales_quotas_UID in any rows in the table?

answered Jul 5, 2013 at 17:17

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2554496 Over a year ago

Historical is a 3 character text holding either Yes or No. newid() should generate a random unique uid. I get the same response regardless of how many parameters I use as long as it is more than 1.

Collectives™ on Stack Overflow

using multiple parameters in append query in Access 2010

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related