[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
Solved

# Excel formulas to access queries

Posted on 2008-10-09
Medium Priority
409 Views
IN the attached excel file columns J, K, and L are calculated, so far you all have helped me with column J and now I am struggling to finish the FRT Met or Overdue column, the excel formula is this =IF(OR(LEFT(M5,2)="CN",LEFT(M5,2)="KN"),"Met",IF(I5<=J5,"Met","Overdue")) and what I have come up with so far in access is this :

FRT Met or Overdue:
IIf(Left([WHY_MISS],2)="CN" Or

Left([WHY_MISS],2)="KN","Met",IIF([DUE_CMPLN_DATE]<=IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD],IIf([DATE_ESCALAT

ED]>[DUE_SCHED_DATE],DateAdd("d",3,[DATE_ESCALATED]),[DUE_SCHED_DATE])),"Met","Overdue")) the problem may be parenthesis related but I can't seem to decipher it maybe I have been looking too long.

Lastly I will need to write something in my access query for when (column "K" which is FRT Met or Overdue) scores "overdue" I would like the month only from column I which is due_cmpln_date to populate.

Thanks
snipet.xls
0
Question by:jclem1
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 10

Expert Comment

ID: 22683672
pardon me i lost it, can you please start with the excel question describing what is going on then saying what are you looking for ?

giving an example cell format would be helpful along with providing the formats and the files as you did.

0

LVL 1

Author Comment

ID: 22686183
If you open the snipet file what I need is the formulas in columns K and L coded into an access query.  The headers are FRT Met or Overdue and Overdue Closed in Month, you will notice overdue closed in month has no formula as currently it is a manual field but I would like it to populate with the "Month" value in column "I" only when column "K" scores overdue.
0

LVL 1

Accepted Solution

jclem1 earned 0 total points
ID: 22689102
I have found the issue, Thanks.

FRT Met or Overdue: IIf(Left([WHY_MISS],2)="CN" Or Left([WHY_MISS],2)="KN","Met",IIf([DUE_CMPLN_DATE]<=IIf([DATE_ESCALATED]<=[DESIRED_DD],[DESIRED_DD],IIf([DATE_ESCALATED]>[DUE_SCHED_DATE],DateAdd("d",3,[DATE_ESCALATED]),[DUE_SCHED_DATE])),"Met","Overdue"))

and

Overdue Closed in Month: Format([DUE_CMPLN_DATE],'mmmm')
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
###### Suggested Courses
Course of the Month15 days, left to enroll