Creating a zero return

  • Thread starter Thread starter Mike Green
  • Start date Start date
M

Mike Green

Hi All
I am trying to get the SQL below to work. I need the time and Cost to
return 0 if there is no data found for this FdJobCardID.
If there are no NonVAT jobs on this job card then NonVatCost must show 0 or
ideally 0.00

Thanks in advance


************************************************
SELECT TblJobCardJobs.FdJobCardID, TblJobCardJobs.FdJobCardJobVAT,
nz([FdJobCardJobCost],0) AS cost, nz([FdJobTime],0) AS [time], [cost]*[Time]
AS NonVATCost
FROM TblJobCardJobs
WHERE (((TblJobCardJobs.FdJobCardJobVAT)=False));
 
Mike Green said:
I am trying to get the SQL below to work. I need the time and Cost to
return 0 if there is no data found for this FdJobCardID.
If there are no NonVAT jobs on this job card then NonVatCost must show 0
or ideally 0.00

Thanks in advance


************************************************
SELECT TblJobCardJobs.FdJobCardID, TblJobCardJobs.FdJobCardJobVAT,
nz([FdJobCardJobCost],0) AS cost, nz([FdJobTime],0) AS [time],
[cost]*[Time] AS NonVATCost
FROM TblJobCardJobs
WHERE (((TblJobCardJobs.FdJobCardJobVAT)=False));
Click to expand...

Are you saying NZ() is not working for you?

The only thing I can think might be wrong is
that NZ() is casting result in way you do not want.

If I run following from Immediate Window

CurrentDb.Execute "SELECT CCur(1.23) As cur, Nz(cur,0) As curNZ INTO
tblfieldtype FROM MSysObjects",dbFailOnError

field "curNZ" is type Text

even if I add "0.0" the field comes back type Text

CurrentDb.Execute "SELECT CCur(1.23) As cur, Nz(cur,0.0) As curNZ INTO
tblfieldtype2 FROM MSysObjects",dbFailOnError

maybe try?

SELECT
T.FdJobCardID,
T.FdJobCardJobVAT,
CCur(nz([FdJobCardJobCost],0)) AS cost,
CLng(nz([FdJobTime],0)) AS [time],
[cost]*[Time] AS NonVATCost
FROM
TblJobCardJobs As T
WHERE
T.FdJobCardJobVAT = False;
 
Thanks for your help Gary,
However I have now got to the root of the problem and I don't think it can
be handled by NZ. My problem is spelt out in a post later on "Creating a
zero return.

Gary Walter said:
Mike Green said:
I am trying to get the SQL below to work. I need the time and Cost to
return 0 if there is no data found for this FdJobCardID.
If there are no NonVAT jobs on this job card then NonVatCost must show 0
or ideally 0.00

Thanks in advance


************************************************
SELECT TblJobCardJobs.FdJobCardID, TblJobCardJobs.FdJobCardJobVAT,
nz([FdJobCardJobCost],0) AS cost, nz([FdJobTime],0) AS [time],
[cost]*[Time] AS NonVATCost
FROM TblJobCardJobs
WHERE (((TblJobCardJobs.FdJobCardJobVAT)=False));
Click to expand...

Are you saying NZ() is not working for you?

The only thing I can think might be wrong is
that NZ() is casting result in way you do not want.

If I run following from Immediate Window

CurrentDb.Execute "SELECT CCur(1.23) As cur, Nz(cur,0) As curNZ INTO
tblfieldtype FROM MSysObjects",dbFailOnError

field "curNZ" is type Text

even if I add "0.0" the field comes back type Text

CurrentDb.Execute "SELECT CCur(1.23) As cur, Nz(cur,0.0) As curNZ INTO
tblfieldtype2 FROM MSysObjects",dbFailOnError

maybe try?

SELECT
T.FdJobCardID,
T.FdJobCardJobVAT,
CCur(nz([FdJobCardJobCost],0)) AS cost,
CLng(nz([FdJobTime],0)) AS [time],
[cost]*[Time] AS NonVATCost
FROM
TblJobCardJobs As T
WHERE
T.FdJobCardJobVAT = False;
Click to expand...
 
Back
Top