Question Getting error with Nz function in Access 2002

[KAdamsbaum]

Hi all,
Hoping someone can explain why I am getting an error with the Nz function in Access 2002. The code below is the same for each example, just showing the different samples (sorry for all the code). Each line is a subquery and the same, the only difference is the change of the date. There is more to the query (other fields) but that all works fine, only the part highlighted is the questionable part. In Example 1, you will see that the Nz function is on line 1 (subquery 1) but not on line 2 and this works (field for line 2 is returned null since Nz is not used). Note: all other lines work fine. In Example 2, this is switched, line one does not have Nz function (field for line 1 is returned null since Nz is not used), but line 2 does and this works. Note: all other lines work fine. In Example 3, both line 1 and 2 have function Nz and does not work, produces error: Data Type Mismatch in Criteria Expression. I have no idea as to why this is happening. Logically this should work, in reality, it is not working. WHY???? :banghead: Like I stated earlier, the only thing that changes from line to line is the date. The lines 3 – 12 have no bearing on the first 2. If I remove lines 3 – 12 and only worked with first 2, same situation occurs. It just makes no sense to me

Another quick question relating to same. The source field is formatted as number (right justified), but when the result is returned, it is a string (left justified). Is there a way to correct / fix this so it is a number in the result not a string? Why does Nz convert result to string if it starts as number and most times source is populate, so would expect to still come out as a number not a string.

Thanks in advance for any help you can give.
Kenny



Example 1
This works
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #1/1/2010#),0) AS [201001 TOTAL],
(Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #2/1/2010#) AS [201002 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #3/1/2010#),0) AS [201003 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #4/1/2010#),0) AS [201004 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #5/1/2010#),0) AS [201005 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #6/1/2010#),0) AS [201006 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #7/1/2010#),0) AS [201007 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #8/1/2010#),0) AS [201008 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #9/1/2010#),0) AS [201009 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #10/1/2010#),0) AS [201010 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #11/1/2010#),0) AS [201011 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #12/1/2010#),0) AS [201012 TOTAL],


Example 2
This works
(Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #1/1/2010#) AS [201001 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #2/1/2010#),0) AS [201002 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #3/1/2010#),0) AS [201003 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #4/1/2010#),0) AS [201004 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #5/1/2010#),0) AS [201005 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #6/1/2010#),0) AS [201006 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #7/1/2010#),0) AS [201007 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #8/1/2010#),0) AS [201008 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #9/1/2010#),0) AS [201009 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #10/1/2010#),0) AS [201010 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #11/1/2010#),0) AS [201011 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #12/1/2010#),0) AS [201012 TOTAL],


Example 3
This Does Not Work – Error is: Data Type Mismatch in Criteria Expression
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #1/1/2010#),0) AS [201001 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #2/1/2010#),0) AS [201002 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #3/1/2010#),0) AS [201003 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #4/1/2010#),0) AS [201004 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #5/1/2010#),0) AS [201005 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #6/1/2010#),0) AS [201006 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #7/1/2010#),0) AS [201007 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #8/1/2010#),0) AS [201008 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #9/1/2010#),0) AS [201009 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #10/1/2010#),0) AS [201010 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #11/1/2010#),0) AS [201011 TOTAL],
Nz((Select Sum( [Client Amt].[Renewal Prem]) From [Client Amt] Where [ Amt].HICN = [Client Amt].[RET HICN] and [Client Amt].[Eff Date] = #12/1/2010#),0) AS [201012 TOTAL],

 

[Galaxiom]

That is not a good way to query.
Put Group By on the EffDate field.

Select Sum( [Client Amt].[Renewal Prem]) 
From [Client Amt] 
Where [Client Amt].HICN = [Client Amt].[RET HICN] 
And [Client Amt].[Eff Date] Between #1/1/2010 And #12/1/2010#
Group By [Eff Date]

However there is something weird about a [ amt] in your query. I assumed it was [Client Amt]

This will return a record for each date in the range. Then use a pivot to get the results into a single row

 

[KAdamsbaum]

Hi Galaxiom,
What I did is correct for what I am doing. I need to know why is the problem happening with the Nz function as I have explained it.

Thanks

Kenny

 

[Galaxiom]

It is certainly odd that the same lines work either way but not together. I can't see any difference.

Maybe I can help witth the other issue.
Use a function to force the output to a number.
CCurr() will make it currency. CLng() for Long. CDbl() for Double.

Still curious about the [ Amt] though.

 

[JHB]

Do you have some sample data we can play with, (Excel-sheet or database) and of cause the whole query-string?

 

[KAdamsbaum]

Thanks for response,
To Galaxiom - The Amt you are questioning is actually another table name, but I removed the table name because it is the abbreviated company for privacy. To JBH - sorry can not supply any data due to the fact that company I work for is goveren by HIPPA rules so that would be a breech of security and privacy. I do not believe the data is the issue. Each of the lines draws it's data from exactly the same field, just looking for different date. It just makes no sense why the error happens. Still open to a reason why and how to fix / correct.

Thanks
Kenny

 

[Brianwarnock]

I'm not clever enough to write an example to prove or disprove this but how about a wild guess.

The sample that fails has NZ on ALL sub queries therefore all return a string, perhaps the fact that one doesn't is what allows what ever else is going on to work.

You could try wrapping one, or each, in Clng.

brian

 

[Brianwarnock]

I have now noticed that Galaxiom has already suggested this, did you try it?

Brian

 

[spikepl]

sorry can not supply any data due to the fact that company I work for is goveren by HIPPA rules so that would be a breech of security and privacy. I do not believe the data is the issue.

So if the data is not the issue then what prevents you from making up enough artificial data to demonstrate the problem and supplying it for the AWF-flock to play with?

 

[JHB]

Well - I will not waste time putting together a table with the necessary field names + invent sample data, so ... and the problem is not mine.
Good luck with your project.

 

[Brianwarnock]

So if the data is not the issue then what prevents you from making up enough artificial data to demonstrate the problem and supplying it for the AWF-flock to play with?

As an old timer, started programming in '62, I never cease to be amazed that people no longer test on designed test data, how do they know things really work when they just throw live data at it, usually in too large quantities to check properly.

Brian

 

[KAdamsbaum]

Hi folks,
What I am working with is not an application, it's just a query I put together to process data I get sent to me via Excel. It's a lot of fields, and currently don't have the time to make up enough data (I do understand that part as I was a mainframe programmer for many years and also do development in Access VBA). I am just trying to understand why a function works one way and not another, given each line is the same except for the date selecting. Does Nz have some strange issues?? If I were developing a system, of course I would build data for testing, but don't have that time right now. Plus I would have to explain each field etc. I do understand everyone's fustration with not having data to play with, but just trying to understand issue of the function itself. Logically should work.

 

[Brianwarnock]

Do you have a.comment about post #7

Brian

 

[GinaWhipp]

Just passing by...

Well, since logically, the Function should work and it two cases does work the assumption is it must be something with the data itself. The same way using a DLookup() for TEXT will fail if the field is NUMERIC or the way a GROUP and TOTALS query will fail on #ERROR. So, without some data there's not much anyone can do.

 

[KAdamsbaum]

I have re-worked my code and have it working now. To Galaxiom - using the CDbl function was helpful to force back to number - thanks