Conditon in Query

[thompson.yip]

SELECT SBCD.[Client Name], SBCD.[SRF#], SBCD.[Product Code],
SBCD.[AM#], SBCD.[Resp Transit], SBCD.[Existing Amount], SBCD.[Amount
Request], [Existing Amount]+[Amount Request] AS [Total Request],
SBCD.[Gate Date], SBCD.[Gate Time], SBCD.[Opening Date Finish],
SBCD.[Opening Time Finish], SBCD.[Opener Initial], SBCD.[Lending Date
Finish], SBCD.[Lending Time Finish], [Gate Date] & " " & [Gate Time] AS
[G Date], [Opening Date Finish] & " " & [Opening Time Finish] AS [OF
Date], [Lending Date Finish] & " " & [Lending Time Finish] AS [LF
Date], IIf([Lending Date Finish] Is Not Null,IIf(DateDiff("n",[G
Date],[LF Date])>0,1,0),0) AS Decision
FROM SBCD
WHERE (((SBCD.[Product Code])="I" Or (SBCD.[Product Code])="N") AND
((SBCD.[Gate Date]) Is Not Null) AND ((SBCD.[Gate Time]) Is Not Null)
AND ((SBCD.[Opening Date Finish])>Now()-7));

For above query, how can I display records with condition "Decision =
0"? I always get the "type mismatch" message when I use "0" as criteria
under "decision".

[Keith]

th**********@rbc.com wrote:

For above query, how can I display records with condition "Decision =
0"? I always get the "type mismatch" message when I use "0" as criteria
under "decision".

Is the 'Decision' field a number or text data type? If it's a text type
then enclose the 0 in quotation marks:

Decision = "0"

HTH - Keith.
www.keithwilby.com

[deko]

> SELECT SBCD.[Client Name], SBCD.[SRF#], SBCD.[Product Code],

SBCD.[AM#], SBCD.[Resp Transit], SBCD.[Existing Amount], SBCD.[Amount
Request], [Existing Amount]+[Amount Request] AS [Total Request],
SBCD.[Gate Date], SBCD.[Gate Time], SBCD.[Opening Date Finish],
SBCD.[Opening Time Finish], SBCD.[Opener Initial], SBCD.[Lending Date
Finish], SBCD.[Lending Time Finish], [Gate Date] & " " & [Gate Time] AS
[G Date], [Opening Date Finish] & " " & [Opening Time Finish] AS [OF
Date], [Lending Date Finish] & " " & [Lending Time Finish] AS [LF
Date], IIf([Lending Date Finish] Is Not Null,IIf(DateDiff("n",[G
Date],[LF Date])>0,1,0),0) AS Decision
FROM SBCD
WHERE (((SBCD.[Product Code])="I" Or (SBCD.[Product Code])="N") AND
((SBCD.[Gate Date]) Is Not Null) AND ((SBCD.[Gate Time]) Is Not Null)
AND ((SBCD.[Opening Date Finish])>Now()-7));

For above query, how can I display records with condition "Decision =
0"? I always get the "type mismatch" message when I use "0" as criteria
under "decision".

What you could do is use another query - select * from qryThisQry where
Decision = 0

[thompson.yip]

Keith wrote:

th**********@rbc.com wrote:

For above query, how can I display records with condition "Decision = 0"? I always get the "type mismatch" message when I use "0" as criteria under "decision".
Is the 'Decision' field a number or text data type? If it's a text

type then enclose the 0 in quotation marks:

Decision = "0"

HTH - Keith.
www.keithwilby.com

Doesn't matter what data type I set to, I still receive the same error
message.

[thompson.yip]

IIf([Lending Date Finish] Is Not Null,IIf(DateDiff("n",[G
Date],[LF Date])>0,1,0),0) AS Decision
There is a condition clause in parameter "Decision"

[deko]

> There is a condition clause in parameter "Decision"

does that make a difference with a second, separate query that selects from
the first?

[Gordon Youd]

Why not use > 0 it works for me, anything in the field greater than 0
returns a list of those meeting the criteria.

Hope this helps

Kind regards, Gordon.

--------------------------------------------------------------------
"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:rI*****************@newssvr13.news.prodigy.co m...

There is a condition clause in parameter "Decision"

does that make a difference with a second, separate query that selects
from
the first?

[Gordon Youd]

Sorry folks, I read the post the wrong way round, Doh!

Kind regards, Gordon.

--------------------------------------------------------------------------

"Gordon Youd" <go****@Zgyoud.demon.co.uk> wrote in message
news:cr*******************@news.demon.co.uk...

Why not use > 0 it works for me, anything in the field greater than 0
returns a list of those meeting the criteria.

Hope this helps

Kind regards, Gordon.

--------------------------------------------------------------------
"deko" <www.clearpointsystems.com@use_contact_form.com> wrote in message
news:rI*****************@newssvr13.news.prodigy.co m...

There is a condition clause in parameter "Decision"

does that make a difference with a second, separate query that selects
from
the first?

[deko]

> There is a condition clause in parameter "Decision"

IMHO, I think you're overboard on the immediate if statements. I'd split it
in two for that reason alone