HappyLadyToo
03-16-2015, 07:49 AM
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. I will happily use VBA if that's what needed.
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,
IIf([CBKO Qty]>0,1,IIf([PO_Due_Date]<Now(),2,"")) AS Priority,
[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]);
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. I will happily use VBA if that's what needed.
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,
IIf([CBKO Qty]>0,1,IIf([PO_Due_Date]<Now(),2,"")) AS Priority,
[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]);