Due Date Query

[pinkapepink]

My table has fields for "Due Date" and "Project Completed Date".
I am trying to build a query that will display either a "Yes" or "No" in a column titled "Project Completed Before Due Date" to help determine whether projects are being completed on time or not.

I am new to Access, and I am learning something new everyday.
Please help!

Thank you in advance for your consideration!

 

[SamDeMan]

make a new field. YesNO: IIF([Date1]>=[Date2],"YES","NO")

good luck,

sam

 

[pinkapepink]

Thank you so much for the response! I tried your suggestion and when I check the datasheet, it says "Error#".

I have no idea what to do now.

 

[mresann]

You have to change the date fields Date1 and Date2 to the fields that correspond to your table or recordset you are querying.

 

[pinkapepink]

Yeah, I did that. Still getting the error.

 

[mresann]

Post the SQL query. Sam's answer is valid, so your fields are not matching.

 

[pinkapepink]

SELECT [Project Request Table].[Project Request Number], [Project Request Table].[Project Date], [Project Request Table].[Client Name], [Project Request Table].[Account #'s], [Project Request Table].[Sales Rep Name], [Project Request Table].[Due Date], [Project Request Table].[Date Approved], IIf([Due Date]>=[Date Approved],"YES","NO") AS [YesNO]
FROM [Project Request Table];

 

[mresann]

I had no problem with your query. I recreated all your fields, and table. I even changed the datatypes from date to text, still get no errors.

There is one more issue that may have an effect: Reference assignment.

Do the following sequence:

In the menu for the database, select Tools, then Macros, the Visual Basic Editor. A new window, the Code window, will open up. On THAT menu, select Tools, the References.

Make sure this item is checked: Visual Basic for Applications. If it is NOT checked, then scroll down to the V's and check the box. Click OK, then close the Code window.

Retry your query. If you are still getting an error, then zip your database, then post it in another message.

 

[pinkapepink]

I verified that Visual Basic Editor was checked. Query still will not work.

Please see attached zip file.

Thank you so much for your help! I really appreciate it!

 

[mresann]

The error is occuring because yoru field "Due Date" is a TEXT field, and "Date Approved" is a DATE/TIME field. If both fields were text, or both fields were date/time datatypes, then the function would work. However, when you are trying to perform a function on two different datatypes, you get the error.

To fix your problem, just change the datatype of Due Date from text to date/time, and problem is solved.

 

[pinkapepink]

That worked! Thank you so much for your help!