Query - Microsoft Access Forum - Computer Learning Zone


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Query

Taruna Matharu

4 years ago

Hello Experts,

I have a continuous form that enter dates and a query that gets date calculation result in another column. Everything is working fine except in the continuous form it add an extra row an the column that has this query displays #ERROR

The query is mentioned below.

SELECT PIFNewProjectTBL.ProjectID, PIFNewProjectTBL.PDocumentType, PIFNewProjectTBL.PDocuTypeRev, PIFNewProjectTBL.EOIDate, PIFNewProjectTBL.EOIDueDate, PIFNewProjectTBL.EOIReceivedDate, IIf([EOIDate] Is Not Null,Switch([EOIReceivedDate]<=[EOIDueDate],"On Time",[EOIReceivedDate]>[EOIDueDate],"Late",[EOIReceivedDate] Is Null And [EOIDueDate]Date(),"Not Due"),"") AS PIFRespStat
FROM PIFNewProjectTBL;

Please guide.

Thanks
Taruna

	Taruna Matharu OP @Reply 4 years ago

	Kevin Robertson @Reply 4 years ago
It looks like you are getting the error because EOIDueDate is null. You are checking for a null value in EOIDate, but not EOIDueDate which your Switch function appears to be based on.

	Alex Hedley @Reply 4 years ago
Is `EOIDate` Null for a new record? `IIf([EOIDate] Is Not Null,Switch([EOIReceivedDate]<=[EOIDueDate],"On Time",[EOIReceivedDate]>[EOIDueDate],"Late",[EOIReceivedDate] Is Null And [EOIDueDate]Date(),"Not Due"),"") AS PIFRespStat`

	Alex Hedley @Reply 4 years ago
Should this have a condition? `And [EOIDueDate]Date()`

	Taruna Matharu OP @Reply 4 years ago
What I am looking for is the query to perform the SWITCH only if (Expression of Interests Issued Date) EOIDate is not empty. EOI DUE DATE is auto calculated once we enter EOIDATE. Once user receives EOI he enters the Received Date. PIF Resp Status - The status of ON Time, Late, Over Due, is then bases on that or if Received Date if empty then to use the Date().

	Taruna Matharu OP @Reply 4 years ago
Kevin and Alex, Still getting the ERROR in the new blank row. Please guide PIFRespStat: IIf([EOIDate] Is Not Null,Switch([EOIReceivedDate]<=[EOIDueDate],"On Time",[EOIReceivedDate]>[EOIDueDate],"Late",[EOIReceivedDate] Is Null And [EOIDueDate]<Date(),"Over Due",[EOIDueDate]>Date(),"Not Due")," ")

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View