dsum error message

  • Thread starter Thread starter JohnE
  • Start date Start date
J

JohnE

Hello. I am using the following dsum but keep running into an error 2471
about the expression entered as a query parameter produced 'BusinessType'. I
can not figure out what the issue is.

material_qty = "" & DSum("Quantity", "SalesOrderItems", "Quantity > 0 And
SONumber = Forms!SalesOrders.SONumber And BusinessType Like
Forms!SalesOrders.BusinessType And SOItemCategory Like 'Materials'")

Quantity is a number.
SONumber is a number.
BusinessType is text.
SOItemCategory is text.
Spelling is all correct that I can see.
Can someone please take a look at this and maybe see what I'm not seeing?
Thanks.
John
 
JohnE said:
Hello. I am using the following dsum but keep running into an error 2471
about the expression entered as a query parameter produced 'BusinessType'.
I
can not figure out what the issue is.

material_qty = "" & DSum("Quantity", "SalesOrderItems", "Quantity > 0 And
SONumber = Forms!SalesOrders.SONumber And BusinessType Like
Forms!SalesOrders.BusinessType And SOItemCategory Like 'Materials'")

Quantity is a number.
SONumber is a number.
BusinessType is text.
SOItemCategory is text.
Spelling is all correct that I can see.
Can someone please take a look at this and maybe see what I'm not seeing?
Thanks.
John
Click to expand...

material_qty = "" & DSum("Quantity", "SalesOrderItems", "Quantity > 0 And
SONumber =" & Forms!SalesOrders.SONumber & " And BusinessType Like "
& Forms!SalesOrders.BusinessType & "And SOItemCategory Like 'Materials'")

What I've done is to take the form object references out of the string.
(watch out for word wrap - should be all one line)
 
Stuart McCall said:
material_qty = "" & DSum("Quantity", "SalesOrderItems", "Quantity > 0 And
SONumber =" & Forms!SalesOrders.SONumber & " And BusinessType Like "
& Forms!SalesOrders.BusinessType & "And SOItemCategory Like 'Materials'")

What I've done is to take the form object references out of the string.
(watch out for word wrap - should be all one line)
Click to expand...

Oops. Slight mistake. There's a space char missing before the last And.
Here's the code corrected:

material_qty = "" & DSum("Quantity", "SalesOrderItems", "Quantity > 0 And
SONumber =" & Forms!SalesOrders.SONumber & " And BusinessType Like "
& Forms!SalesOrders.BusinessType & " And SOItemCategory Like 'Materials'")
 
Oops. Slight mistake. There's a space char missing before the last And.
Here's the code corrected:

material_qty = "" & DSum("Quantity", "SalesOrderItems", "Quantity > 0 And
SONumber =" & Forms!SalesOrders.SONumber & " And BusinessType Like "
& Forms!SalesOrders.BusinessType & " And SOItemCategory Like 'Materials'")
Click to expand...

I noticed a couple more possible problems, Stuart (not yours!); if SONumber or
BusinessType are Text fields you need quotemarks around the criterion, and
there's no wildcard in the LIKE 'Materials' criterion - so it will only
retrieve items where SOItemCategory is the exact string Materials. If that's
what's wanted you can use = instead of LIKE; if you want all categories
starting with the word Materials then it should be

And SOItemCategory Like 'Materials*'
 
When you are referencing form controls in the criteria
of a domain function like this, those references need to
be outside of the quotes;

DSum("Quantity", "SalesOrderItems", "Quantity > 0 And _
SONumber =" & Forms!SalesOrders.SONumber & " And BusinessType Like '" _
& Forms!SalesOrders.BusinessType & "' And SOItemCategory Like 'Materials'")
 
I noticed a couple more possible problems

You're right of course, John. Thanks.
 
Back
Top