Nz function question

[smeghead67]

Hello,



I have a select query where I'm trying to get it to return a 0 if the sum is null

SELECT DISTINCTROW Sum(Nz([partstore Query].qty, 0)) AS Inventory
FROM [partstore Query]
WHERE ((([partstore Query].[partstore].[binid]) Like "I*"));

when I run it i get no value in the result.

Smeghead67

[ssanfu]

The text in RED shouldn't be there.

SELECT DISTINCTROW Sum(Nz([partstore Query].qty, 0)) AS Inventory 
FROM [partstore Query] 
WHERE ((([partstore Query].[partstore].[binid]) Like "I*"));

Also, is binid a text field???

Are records returned if you execute this query?

SELECT DISTINCTROW Nz([partstore Query].qty, 0) AS Inventory 
FROM [partstore Query] 
WHERE ((([partstore Query].[binid]) Like "I*"));

[alansidman]

I am having a difficult time understanding your Where clause. You have referenced a field (partstore) in the query (partstore Query) but what is binid? Is it another field in the partstore query?

Alan

Edit: Steve types faster. Same issues.

[smeghead67]

ssanfu,

well I removed the extra text and it did not make a diffrence.

The second code did not work either

[alansidman]

Please explain what you are specifically trying to do. Perhaps, if we understand your intentions, then a good solution will result.

[smeghead67]

alansidman,

the text in red was not supposed to be there. binid is a text field in the partstore query. i have 6 seprate storage areas where parts could be in bins. I decided to give each room a identifying letter to start it. eg L01-01-01 is at the lodge storage area rack 1, shelf 1 down from the top and bin 1 from the left. i could have the same part in more than one bin in one area. when the partstore query is ran, it selects all parts from that table filtered by the part id. then i use this query to give me a quick total of how many parts i have in a specific storage area.

Ted

[alansidman]

What are hoping to achieve using the DistinctRow SQL function. What happens when you run it as a select query without the DistinctRow? Without seeing your db, it is a case of guessing. If you want to upload a copy (sanitized for confidential material) it would help. Make sure to run a compact and repair and if possible save as an AC2003 version (.mdb) to allow maximum viewership.

[ssanfu]

First thing is to get the [partstore Query] to return records.

Then check for records using:

SELECT [partstore Query].qty, AS Inventory  
FROM [partstore Query]  
WHERE ((([partstore Query].[binid]) Like "I*"));

After that, try:

SELECT Nz([partstore Query].qty, 0) AS Inventory  
FROM [partstore Query]  
WHERE ((([partstore Query].[binid]) Like "I*"));

Alan: I really type slower... I just have to start sooner......

[smeghead67]

Alan,

I removed distintrow and it still works. the query wizard put it in there.

I clicked compact database, now it will not let me save a copy of the database. umm, if that screwed it up, im not going to be happy. i have let it sit for 20 minutes now. there is no real data in the database just 2 make believe parts i made to test my concept.

Ted

[smeghead67]

Steve,

in your first query the comma caused an error I fixed that and it gave me the usual no value.

The second one gave me a table but it had the asterisk to the left as if it was going to start data into a new table.

[smeghead67]

I tried it in both 03 and 07 formats and it is too big. 545kb and 640kb respectively. i dont know how to get it any smaller.

[RuralGuy]

Now zip the db and it will post.

[smeghead67]

Ok,

Here it is. I also sent this to the forms area. being a noob means listening to you guy's good advice.

Slot.zip