Calculated Field

[linda55]

I am a self-taught Access97 worker with gaps in my learning, so please have mercy! I have a query based on a crosstab query. In the query I have a calculated field
Tot:[Jul]+ [Aug]
Works fine unless Jul or Aug is blank, then returns nothing; that is, won't return the Aug amount. Jul and Aug are formatted as currency. Could someone point me in the direction of a solution?

 

[linda55]

The SQL for my crosstab query is as follows
TRANSFORM Sum([Daily Entries].Credit) AS [The Value]
SELECT [Daily Entries].RevNo, Sum([Daily Entries].Credit) AS [Total Of Credit]
FROM [Daily Entries]
WHERE ((([Daily Entries].AcctNo)=".08&quot)
GROUP BY [Daily Entries].RevNo, [Daily Entries].AcctNo
PIVOT Format([Date],"mmm&quot In ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"

How and where do I insert the NZ to get the query to return zeros where there is no data to add. My knowledge of SQL is very limited.

 

[KyleS]

linda,

It's pretty simple,

Just use this function:

Tot:NZ([Jul],0)+ NZ([Aug],0)

That way if Jul is null you'll just get Aug, and vice versa, if both are null you'll get 0

Kyle

 

[linda55]

Thanks, KyleS, you were kind to reply to my call for help.

 

[KyleS]

Linda,
No problem, that's what we're here for! Kyle