dsum error message

[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

 

[Stuart McCall]

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

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]

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)

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'")

 

[John W. Vinson]

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'")

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*'

 

[Beetle]

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'")

 

[Stuart McCall]

I noticed a couple more possible problems

You're right of course, John. Thanks.