VBA-using different data types in an iif statement

[HappyLadyToo]

Good Morning,

I've built a query in Access and am have a data type mismatch in an IIF statement. I know where the problem is but I don't know how to fix. Last night, two of us worked on for 4 hours. Ugh!

When I use each part of the IIF statement separately, they work. I cannot put them together or the column errors out. What I want to do is this:

The query needs to find and count higher than 0 in the CBKO Qty column first. If the count is more than zero, the priority column will read 1 for that product. Should that be false, the next step is to compare the PO Due Date to today. If the PO Due Date is prior to today, the priority column will read 2 for that product. If neither of theses conditions are true, the column will be blank.

The error is either #ERROR or is a date to a Julian number. Please help!

SELECT 
[Part Detail].[Report Date], 
[zzz Short PO to Product PO].DC_CODE, 
[zzz Short PO to Product PO].PART_NUMBER, 
[Part Detail].[CBKO Qty], 
[Part Detail].[FBKO Qty],
 [zzz Short PO to Product PO].PO, 
[zzz Short PO to Product PO].[PO Date], 
[zzz Short PO to Product PO].PO_Due_Date,
 [zzz Short PO to Product PO].ITEM_CODE, 
[zzz Short PO to Product PO].QTY_ORDERED, 
[zzz Short PO to Product PO].QTY_RECEIVED,
 [B]IIf([CBKO Qty]>0,1,IIf([PO_Due_Date]<Now(),2,"")) AS Priority,[/B] 
[zzz Short PO to Product PO].VENDOR_NAME


FROM 
[zzz Short PO to Product PO] LEFT JOIN [Part Detail] ON ([zzz Short PO to Product PO].LINE_CODE = [Part Detail].[Line Code]) AND ([zzz Short PO to Product PO].ITEM_CODE = [Part Detail].[Item Code]) AND ([zzz Short PO to Product PO].DC_CODE = [Part Detail].[DC Name]);

 

[HappyLadyToo]

Please disregard this post. Someone was able to help me.

Not sure how to delete it or post as solved.

 

[Joe4]

Not sure how to delete it or post as solved.​

The best thing to do is to post the solution (so that others can see what the problem was and how it was corrected). You may be able to help/educate others!

 

[HappyLadyToo]

The problem is that I was missing single quotes around the numbers in my IIF statement. Something so simple was causing a lot of grief.

It looks like my IIF statement was missing some data in my post.

Good Morning,

I've built a query in Access and am have a data type mismatch in an IIF statement. I know where the problem is but I don't know how to fix. Last night, two of us worked on for 4 hours. Ugh!

When I use each part of the IIF statement separately, they work. I cannot put them together or the column errors out. What I want to do is this:

The query needs to find and count higher than 0 in the CBKO Qty column first. If the count is more than zero, the priority column will read 1 for that product. Should that be false, the next step is to compare the PO Due Date to today. If the PO Due Date is prior to today, the priority column will read 2 for that product. If neither of theses conditions are true, the column will be blank.

The error is either #ERROR or is a date to a Julian number. Please help!

SELECT 
[Part Detail].[Report Date], 
[zzz Short PO to Product PO].DC_CODE, 
[zzz Short PO to Product PO].PART_NUMBER, 
[Part Detail].[CBKO Qty], 
[Part Detail].[FBKO Qty],
 [zzz Short PO to Product PO].PO, 
[zzz Short PO to Product PO].[PO Date], 
[zzz Short PO to Product PO].PO_Due_Date,
 [zzz Short PO to Product PO].ITEM_CODE, 
[zzz Short PO to Product PO].QTY_ORDERED, 
[zzz Short PO to Product PO].QTY_RECEIVED,
 [B]IIf([CBKO Qty]>0,[U]'1',[/U]IIf([PO_Due_Date]<now(),2,"")) as="" priority,<="" strong=""> < NOW(), [U]'2',[/U]"")),
[zzz Short PO to Product PO].VENDOR_NAME


FROM 
[zzz Short PO to Product PO] LEFT JOIN [Part Detail] ON ([zzz Short PO to Product PO].LINE_CODE = [Part Detail].[Line Code]) AND ([zzz Short PO to Product PO].ITEM_CODE = [Part Detail].[Item Code]) AND ([zzz Short PO to Product PO].DC_CODE = [Part Detail].[DC Name]);

</now(),2,""))>[/B]

 

[HappyLadyToo]

All set...

thanks