HELP! please (the null values & the crosstable query)

[SteelDeer]

I wounder if anyone PLEASE could help me with a
problem that I am facing with MS.Access query.

the problem is that I made a query a crosstable
query months in the rows and years in the columns. and parameters startdate and
enddate

so whene I do a query with a date rang between
[startdate] and [enddate]
it gives me as expected null values in some fields.
What I need now is to convert the null value to
zeros (only the null values within the date range), and to keep the null values out of the range as they
are.

the SQL statement is:

PARAMETERS StartDate DateTime, EndDate DateTime;
TRANSFORM Count(GNRLFRM.MRN) AS CountOfMRN
SELECT Format([OVADATE],"mmm") AS Expr1
FROM GNRLFRM
WHERE (((GNRLFRM.OVADATE) Between [StartDate] And
[endDate]))
GROUP BY (Month([OVADATE])*12+Month([OVADATE])-1),
Format([OVADATE],"mmm")
PIVOT (Year([OVADATE]));


Please Help me.
and thank you very much in advance.

 

[Matthew Snook]

There is a considerable amount of help in Access Help files on this. Look up "About working with blank fields in queries" in Access help for examples of the "Nz" function. It should do what you want.

Matt

 

[SteelDeer]

I tried both the NZ and IIf .... but it needs more than that ... it needs a genius....