Converting an excel formula into access query formula (1 Viewer)

[yaoi28lover]

Hello!


I am having a problem converting an excel formula, I already have converted some of it but an error always shows up. If anyone could help me with this one it is very much appreciated.


This is the excel formula:

(I'm not the one who made this but I can understand the flow of the formula. Sadly my co-worker (an OJT student) is not here anymore and I can't ask him about it.

Title: Days Left Before Due

=IF(COUNTA(N1214)=1,-50000,IF(COUNTA(L1214)=1,-50000,IF(I1214-'Date Now'!A1237<-1,(ROUNDDOWN(I1214-'Date Now'!A1237,0)),(IF(I1214-'Date Now'!A1237<0,ROUNDDOWN(I1214-'Date Now'!A1237,0),(ROUNDUP(I1214-'Date Now'!A1237,0)))))))

*Note:

N1214= Auditee's Reply
L1214= Auditees's Reply
I1214= Due Date
A1237= from the "Date Now" sheet calling position number


and this is my Access Formula:

Days Left Before Due: IIf([Auditee's Reply]=1,0 And [Auditee's Reply]=1,0) IIf[Due Date]-Now( ), 1), (Round ([Due Date]-Now( )= 0,0))

I tried to create this but I am having a problem with the commas and parentheses because I am totally a beginner with Access 2003. :banghead:

Pls. help me out guys. I really need this within a week so that I can finish up my database.

 

[yaoi28lover]

I forgot the output should be in real number .

 

[yaoi28lover]

I really need some help about this. Can anyone explain to me how this will work?

 

[Brianwarnock]

I'm glad that you understand the excel formula,but I'm puzzled, Counta is used to count the number of cells in a range that are not empty, so is he using it to check that the one cell is not empty? Most would use IsBlank or IsEmpty depending on what they are checking for.

You are checking the same field twice, or attempting to, I believe that this is because you are trying to do a straight conversion without fully understanding wht is going on.

You should start a new thread stating what you have got and what you want to achieve but in English not as an Excel formula. Excel and Access are different.

Brian

 

[Brianwarnock]

For what it's worth, since you won't be back for a while, I am going to guess that what you need is

Days_Left_Before_Due: iif(auditeereply=1,0,Datediff('d',Date(),DateDue))

I removed all blanks and special characters as except for_ they are a recipe for syntax errors when used in object names.

Brian

 

[yaoi28lover]

Hi Brian,

I understand what you mean by checking the same field twice and using the "COUNTA" BUT as far as I knew my questions are correct because I'm already using the excel formulas and are working just fine and actually there's still another formula which is the continuation of this one that I am going to asked again.

 

[Brianwarnock]

I did not doubt that the Excel formula worked just the coding after all he checks that a given condition is less than minus one, if it isn't he checks if it is less than zero , but his action is the same in both cases so the fist check is redundant as anything <-1 is also <0.

He has 2 cells containing Auditee's reply but you only quoted one field in Access so your formula needs only one test.

I stand by my comment that you should start a new thread stating in English what you have and what you require to achieve.

Brian

 

[yaoi28lover]

Can't I just explain it here? what do you mean by English? As in I'm going to explain it in a sentence is that what you mean by English? And now that I think about it you will really not going to understand it because I left out some details. :banghead: I'll upload the excel file when I have 10 posts.

 

[yaoi28lover]

This is what I understand based on my learning ability of the excel data and formulas.

I have 14 columns which consists of 14 Titles:

1. No. => The corresponding Number of the data. It's in a chronological order but the numbers corresponds to "Date Now" sheet. I mean you can create many sheets in one excel right?
2. Division => Company Divisions
3. Category => Audit, etc.
4. Control => Control Number from different divisions.
5. Week No. Received => How many weeks since they received the files.
6. Date Received => The date when they received the files.
7. Finding => The report that comes from different divisions.
8. Remarks => usually like something amiss on the reports.
9. Due Date => This is from the files that they received that have a due date.
10. Days Left Before Due => The countdown of days that before and after they due. (The <-1 that you mentioned) It counts the positive and negative real number of days.
11. Status => It has 3 functions: "Open", "Closed" and "Overdue". (This is the other one that I am also going to ask since it is connected to "Days Left Before Due".)
12. Auditee's Reply => you already know what it means.
13. Auditor's Response => same with #12.
14. Auditee's Reply => In Access 2003 I changed it into "Auditee's Reply 2" because when I tried to input data in #12 the output is just the same.

 

[yaoi28lover]

I don't understand some parts of the formula so pls. bear with me:

=IF(COUNTA(N1214)=1,-50000,IF(COUNTA(L1214)=1,-50000,IF(I1214-'Date Now'!A1237<-1,(ROUNDDOWN(I1214-'Date Now'!A1237,0)),(IF(I1214-'Date Now'!A1237<0,ROUNDDOWN(I1214-'Date Now'!A1237,0),(ROUNDUP(I1214-'Date Now'!A1237,0)))))))

If
the count of "Auditee's Reply 2 (N1214)" is 1 then the value is true and will range to -50000.

Else If
the count of "Auditee's Reply (L1214)" is 1 then the value is true and will range to -50000

Else If
"Due Date (I1214)" minus "Date Now"! (A1237 is the position number that's been called to "Date Now") is less than -1 (The range of negative for overdue it will still continue to count even after the due date passed by.)

Rounds the answer of "Due Date (I1214)" minus "Date Now"! to zero (0) decimal places.

Else If
"Due Date (I1214)" minus "Date Now"! is less than zero (0). Rounds the answer of "Due Date (I1214)" minus "Date Now"! down to zero (0) decimal places. And then rounds the answer of "Due Date (I1214)" minus "Date Now"! up to zero decimal places.

I think I get what you mean by redundant but that's how the formula work because in excel if any of those have been cut off an error will occur.

So that's how I understand it. And I don't know how to create the formula for Access 2003 because I still don't know some of the functions. And I really needed it within this week.

 

[yaoi28lover]

This is my excel, it's fully working and I erased some data but the titles and formulas are still the same. This is just an example but same format of what I am doing.

 

[Brianwarnock]

why does the worksheet 'date due' have 1250 rows =NOW()

why is the time important, it is not carried in the due date and means that the result is different depending on the time of day that the workbook is opened.

The reason that I have suggested that you start a new thread with a new title of course is that this thread requires us to first understand the Excel workbook and then translate that into Access for an unknown database. this requires the poster to do a lot of work in two disciplines. I do not have Access anymore and cannot write and test any formulae. Others who are excellent at Access may have little or no experience with Excel and therefore not even attempt to answer.

I will say again Excel and Access are different it is rare that a formula translates from one to the other.

My advice is to start a new thread, attach a sample MDB and put into words your requirement, you will get more responses.

Brian

PS I changed your formula in col J to

=IF(COUNTA(N1214)=1,-50000,IF(COUNTA(L1214)=1,-50000,I1214-TODAY()))
and got the same answers.
Am I missing something or did your student pal just stumble around/

 

[yaoi28lover]

I thank you Sir very much. Okay I'll make a new thread and this time directly using the Access. I'm not going to convert the excel formula per se instead I'll do another formula that will output just the same in my excel. Can you help me how to do that? So that they can understand it and answer me immediately. Thank you very much for helping me. I'll wait for your response about creating a new thread.

You are so amazing Mr. Brian you have my respect. Pls. continue to help me out until I finish this project. I am working alone on creating this database and attempting to create it via learning to the internet BUT since I don't have anyone to ask and learn about this personally I hope that this forum can help me to complete my project.

P.S.

Your formula really works much better. Its really simple and easy to learn. Can you also do the same to "Status (col K)" because it is also complicated to understand maybe you can also simplify it to a much better and understandable formula.

And also how did he get the formula from col E "Week No. Received"? I haven't ventured that area of "ISERR" and "TRUNC" I don't know how did he get it. :banghead:

 

[Brianwarnock]

I cannot help with the formula in col K, it is straight foreward nested Ifs testing cell values in order of relevance I presume. However I do ot know were the 5000 comes from.

=IF(COUNTA(N1214)=1,"Closed",IF(COUNTA(M1214)=1,"Open",IF(COUNTA(L1214)=1,"Closed",IF(J1214=0,"Due Today",IF(J1214<0,(IF(J1214<-5000,"Open","Overdue")),"Open")))))

one point to be aware of when looking at the spread sheet is that col J has conditional formatting applied that blots out numbers <-41010 by making their font colour the same as the background.

Brian

looking at E

 

[Brianwarnock]

I have simplified the formula in E
From
=IF(ISERR(VALUE(IF(COUNTA(F1047)=0,"",VALUE(TRUNC((F1047-'Date Now'!$C$5)/7)+1))))," ",VALUE(IF(COUNTA(F1047)=0,"",VALUE(TRUNC((F1047-'Date Now'!$C$5)/7)+1))))

to

=IF(COUNTA(F1009)=0,"",IF(ISERR(TRUNC((F1009-'Date Now'!$C$5)/7)+1)," ",TRUNC((F1009-'Date Now'!$C$5)/7+1)))

The first IF checks to see if there is a value in colF if there is, the false, he calculates the week number based on a value in Date Now sheet.
The iserr is to test if the calculation returns an error and if so place a Space in col F else do the calculation and put the result in Col F, I realise it seems odd having to do the calculation twice but once for the True and once for the False part of the IF.

The Trunc is to remove decimal places, the Value was not required as he was already dealing with numbers.

Not sure how any of this helps with the conversion of this to an ACCESS project

Brian

 

[Brianwarnock]

One other thing, the spreadsheet has merged cells , these always cause confusion and in some cases problems. they can usually be avoided and here seemed to serve no useful purpose.

Brian