nulls

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

i have a crosstabe query and the data looks something like this
name period 1 period 2 period 3
joe smith 2 1
john frank 3 4 1
tom smith 1 2

where there is a blank i would like it to fill a zero how can i do this
i thought with nz function but it doesnt return a 0 does this mean its
not null
 
If you are using NZ, you must be using it in the correct place for it to
work and, as you have observed, the value you are testing must be null.
Without seeing your SQL or knowing something about your fields, this
question is very difficult to answer.

Transform Val(NZ(Max([SomeField),0)) as PeriodValue
....

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message
 
cant i just do it in select query some how?

John Spencer said:
If you are using NZ, you must be using it in the correct place for it to
work and, as you have observed, the value you are testing must be null.
Without seeing your SQL or knowing something about your fields, this
question is very difficult to answer.

Transform Val(NZ(Max([SomeField),0)) as PeriodValue
....

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


dlb1228 said:
i have a crosstabe query and the data looks something like this
name period 1 period 2 period 3
joe smith 2 1
john frank 3 4 1
tom smith 1 2

where there is a blank i would like it to fill a zero how can i do
this
i thought with nz function but it doesnt return a 0 does this mean its
not null
Click to expand...
Click to expand...
 
SELECT QCFinalTable.SDLNAM, QCFinalTable.[1], QCFinalTable.[2],
QCFinalTable.[3]
FROM QCFinalTable;


John Spencer said:
If you are using NZ, you must be using it in the correct place for it to
work and, as you have observed, the value you are testing must be null.
Without seeing your SQL or knowing something about your fields, this
question is very difficult to answer.

Transform Val(NZ(Max([SomeField),0)) as PeriodValue
....

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


dlb1228 said:
i have a crosstabe query and the data looks something like this
name period 1 period 2 period 3
joe smith 2 1
john frank 3 4 1
tom smith 1 2

where there is a blank i would like it to fill a zero how can i do
this
i thought with nz function but it doesnt return a 0 does this mean its
not null
Click to expand...
Click to expand...
 
SELECT QCFinalTable.SDLNAM,
NZ(QCFinalTable.[1],0) as Period1,
NZ(QCFinalTable.[2],0) as Period2,
NZ(QCFinalTable.[3],0) as Period3
FROM QCFinalTable;

If all you need is the ZERO to appear in a report or on a form, you can use
the format property of the control.
Set the format property to: 0;-0;0;0
That will show zero for Null values.

dlb1228 said:
SELECT QCFinalTable.SDLNAM, QCFinalTable.[1], QCFinalTable.[2],
QCFinalTable.[3]
FROM QCFinalTable;


John Spencer said:
If you are using NZ, you must be using it in the correct place for it to
work and, as you have observed, the value you are testing must be null.
Without seeing your SQL or knowing something about your fields, this
question is very difficult to answer.

Transform Val(NZ(Max([SomeField),0)) as PeriodValue
....

Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


dlb1228 said:
i have a crosstabe query and the data looks something like this
name period 1 period 2 period 3
joe smith 2 1
john frank 3 4 1
tom smith 1 2

where there is a blank i would like it to fill a zero how can i do
this
i thought with nz function but it doesnt return a 0 does this mean
its
not null
Click to expand...
Click to expand...
Click to expand...
 
Back
Top