Count partcode total in query

[KevinSlater]

hello, im trying to find out the total number of times a particular record (one with a particular partcode called 2358NXA) appears in a query. Ive tried the following:

23PARTCODETOTAL: DSum("[STK_PART_CODE]","EXT-STK_PARTS","[STK_PART_CODE]= '2358NXA'")
but this doesnt seem to work

Anyone have any suggestions on how to fix this?

 

[DCrake]

When you say it does not work. What does not work?
Is the results zero?
Is there a syntax error?
Is it the wrong number?
What kind of query is it looking at?

Could be anything, more info needed.

David

 

[KevinSlater]

When i try to run the query i get the attached error message

i then click ok and no results are shown in the 23PARTCODETOTAL field. The rest of the query works fine and data is shown in the other fields.

The query is looking at 1 linked oracle table and 2 access tables. THe partcode number is correct, theres a number of records within the query with this partcode.

 

[DCrake]

As this is oracle based table I can olny assume that the field you are interrogating has some erronous data in it or null fields so the sum command cannot cope with this. Try adding IS Not Null into the equasion.

David

 

[KevinSlater]

i tried adding: Is Not Null into the criteria under the 23PARTCODETOTAL: field but when running the query no results are displayed.

EXT-STK_PARTS is the name of the oracle table

 

[DCrake]

As a matter of interest create a new query and include the oracle table. Bring down the prod code field and group by this then sum the value field and see what happens. If ok then apply a filter based on the 23 field and see what happens.

David

 

[KevinSlater]

ok "create a new query and include the oracle table. Bring down the prod code field and group by this then sum the value field and see what happens."

This works ok - lots of records are displayed with all the diffrent part codes

"If ok then apply a filter based on the 23 field and see what happens."
I done this by filtering on partcode 2358NXA and the query results showed just the one record with this partcode.

So these tests appear to work ok.

 

[namliam]

Using DSum in general is not a good idea, atleast not in a query.

Moreso since yuor summong a field which is a text field ... you cannot sum a text field

 

[KevinSlater]

Ok, is there a better method that you would suggest doing intead of using Dsum?

 

[namliam]

A normal 'proper' group by query with a count should do the trick...
Possibly using a IIF construct ...

Or perhaps a crosstab query if you are looking to get totals for different postal codes....

 

[KevinSlater]

i tried grouping all the fileds aprat from the part code field which i set to count and in the query results it just shows: 1 for all the count of part code records.

 

[namliam]

sum(iif(yorufield= 'your code';1;0))

or somehting alike will do the trick..

alternatively try a crosstab..