SQL statement help

[mat41]

Having toruble doing this in access 2003:

SELECT......., IIf([Days Opened]>[SLA Agreement],"Yes","No") AS [Has Meet SLA]
FROM tbleName.....

Specifically my issue is with:

IIf([Days Opened]>[SLA Agreement],"Yes","No") AS [Has Meet SLA]

I am getting an 'out of disk space in temp location error' I know I am not out of disk space. Does it matter if there are no values in some of the 'Days Opened' or 'SLA Agreement' fields? Any assitance getting this working while allowing empty field values would be great. NOTE they are both number data types.

TYIA

 

[softhemc]

You could try wrapping [Days Opened] and [SLA Agreement] in the NZ function e.g.

[tt]
IIf(Nz([Days Opened],0)>Nz([SLA Agreement],0),"Yes","No") AS [Has Meet SLA]
[/tt]

 

[mat41]

Excellent that did it, thanking you very much. I am now using:

IIf(Nz([Days Opened],0)<=Nz([SLA Agreement],0),"Yes","No") AS [Has Meet SLA]

One more question if I may? How can I make sure if:

if Days Opened >= SLA Agreement say "Yes" (this is working)
if Days Opened > SLA Agreement say "No" (this is working)
If SLA Agreement does not have a value say "N/A"

TYIA

 

[mat41]

correction I should have said:

if Days Opened <= SLA Agreement say "Yes" (this is working)
if Days Opened > SLA Agreement say "No" (this is working)
If SLA Agreement does not have a value say "N/A

 

[softhemc]

Replace "No" with another IIf ...
[tt]
, IIf(Nz(Days Opened], 0) > Nz([SLA Agreement], 0), "No", "N/A"))
[/tt]

 

[mat41]

I ended up getting this to work:

SWITCH
(
[Week Days Opened] <= TBL_SLA_Activity.[Number of Days] , 'YES',
[Week Days Opened] > TBL_SLA_Activity.[Number of Days] , 'NO',
TRUE, 'N/A'
) AS [Has Meet SLA]

Thank you so much for your time, very much appriciated!