INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

#### Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

# converting an excel formula to use in access

## converting an excel formula to use in access

(OP)
I have a table with a frequency (the value at which something will happen), frequnit (units at which it will happen), nextdate1 (next time we expect it to happen).
FREQUENCY FREQUNIT NEXTDATE1 nextdate2
2 MONTHS 1/2/2015
3 WEEKS 10/5/2015
30 DAYS 2/1/2015
1 YEARS 3/1/2015

I need to get the nextdate2 by multiplying the frequency and by the date.
Continue the same for nextdate3 and so on.

I can do this in Excel by using this formula...
=IF($AI20=0,"",IF($Q20="YEARS",DATE(YEAR(AK20)+($P20),MONTH(AK20),DAY(AK20)),IF($Q20="MONTHS",DATE(YEAR(AK20),MONTH(AK20)+($P20),DAY(AK20)),IF($Q20="WEEKS",DATE(YEAR(AK20),MONTH(AK20),DAY(AK20)+7*($P20)),IF($Q20="DAYS",DATE(YEAR(AK20),MONTH(AK20),DAY(AK20)+1*(\$P20)))))))
===================================
AK20= NEXTDATE1
Q20= FREQUNIT
P20= FREQUENCY
===================================
I would like to convert it to access and for cast it out for 12 months.

I tried to convert, but i keep getting errors.
NextDate2:
=IIF([FREQUNIT]='YEARS',YEAR([NEXTDATE1])+[FREQUENCY],MONTH([NEXTDATE1]),DAY([NEXTDATE1])),IIF([FREQUNIT]="MONTHS",YEAR([NEXTDATE1]),MONTH([NEXTDATE1])+([FREQUENCY]),DAY([NEXTDATE1])),IIF([FREQUNIT]="WEEKS",YEAR([NEXTDATE1]),MONTH([NEXTDATE1]),DAY([NEXTDATE1])+7*([FREQUENCY])),IIF([FREQUNIT]="DAYS",DATE(YEAR([NEXTDATE1]),MONTH([NEXTDATE1]),DAY([NEXTDATE1])+1*([FREQUENCY])))

Any help would be appreciated.
Thanks
tav

### RE: converting an excel formula to use in access

Hi,

Errors???

Exactly what errors?

Your Excel formula has 5 IF()

Your Access formula has 4 IIF() --- however your parentheses don't match!

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: converting an excel formula to use in access

I would build this UDF. You could shorten the function if in your table you added the interval values
Months = m
Days = d
Weeks = ww
Years = yyyy

That would allow you to remove the select case.

#### CODE

Public Function NextDate(theNumber As Integer, interval As String, theDate As Date, Optional howMany As Integer = 1) As Date
Dim I As Integer
Select Case interval
Case "Days"
interval = "d"
Case "Weeks"
interval = "ww"
Case "Months"
interval = "m"
Case "Years"
interval = "yyyy"
End Select

For I = 1 To howMany
theDate = NextDate
Next I
End Function 

To use
=NextDate([Frequency],[Frequnit],[NextDate1],1)

You could then find nextdate2,3,4 by changing the last parameter howMany. HowMany means how many times to repeat the patterm

### RE: converting an excel formula to use in access

"FREQUENCY FREQUNIT NEXTDATE1 nextdate2"
"Continue the same for nextdate3 and so on."

It is just my opinion, but this structure does not look like normalized way of keeping the data in the relational data base.

FREQUENCY FREQUNIT EVENT_DATE

and go down the records with the dates.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

### RE: converting an excel formula to use in access

(OP)
Andrzejek-
So the folling data is in an oracle database.
PMNUM FREQUENCY FREQUNIT NEXTDATE1
PM1 1 MONTHS 10/12/2015
PM2 2 WEEKS 10/12/2015
PM3 30 DAYS 10/12/2015
PM4 1 YEARS 10/12/2015

Every week a script runs to see if anything falls within this week and if it does it produces a workorder.
The script multiplys the frequency by the nextdate and over-writes the nextdate fileld in oracle with the new nextdate.

I am using access as a reporting tool to pull forcasted data and populate a schedule for a whole year to show when those potential workorders will come out.

The formulas just plots the dates so we can share with schedulers when to expect us for that Area/Location.
I would love to figure out how to display the data in the report the next PM# and the date to expect it vertically like this-
PMNUM FREQUENCY FREQUNIT NEXTDATE1
PM1 1 MONTHS 10/12/2015
PM2 2 WEEKS 10/12/2015
PM3 30 DAYS 10/12/2015
PM4 1 YEARS 10/12/2015
PM2 2 WEEKS 10/26/2015
PM2 2 WEEKS 11/09/2015
PM3 30 DAYS 11/11/2015
PM1 1 MONTHS 11/12/2015
PM2 2 WEEKS 11/23/2015
PM2 2 WEEKS 12/07/2015
PM3 30 DAYS 12/11/2015
PM1 1 MONTHS 12/12/2015
PM2 2 WEEKS 12/21/2015

Skip- the excel was just checking to see if the next date was null, if so do nothing.
The error is "The expression you entered has a funtion containing the wrong number of arguments".

MajP- I'm not sure how to use that VBA in access?

Thanks all
tav

### RE: converting an excel formula to use in access

"The error is "The expression you entered has a funtion containing the wrong number of arguments"

Hence my comment, "your parentheses don't match!"

Skip,

Just traded in my OLD subtlety...
for a NUance!

### RE: converting an excel formula to use in access

How many next dates do you want to show. If it is a few you can do a series of queries. If a lot I would write to a table.

### RE: converting an excel formula to use in access

I would think this would be easier in Excel. Here is one way to do it in code
Assuming your table is called "tblPM" and your field names are as described.
If you call the procedure testNext and pass it the value of 10
testNext(10)
It will run 10 iterations. It reads the existing records and then inserts the new ones back into your original table.
Sort by NextDate1 once finished

#### CODE

Public Function GetNextDate(theNumber As Integer, ByVal interval As String, theDate As Date, Optional ByVal howMany As Integer = 1) As Date
Dim I As Integer
Select Case interval
Case "Days"
interval = "d"
Case "Weeks"
interval = "ww"
Case "Months"
interval = "m"
Case "Years"
interval = "yyyy"
End Select
For I = 1 To howMany
theDate = GetNextDate
Next I
End Function

Public Sub TestNext(Optional howMany As Integer = 1)
Const tableName = "tblPM"
Dim rs As DAO.Recordset
Dim strSql As String
Dim recordCount As Integer
Dim PM As String
Dim Frequency As Integer
Dim Frequnit As String
Dim StartingDate As Date
Dim NextDate As Date
Dim I As Integer
Dim J As Integer
'Find out how many existing records in the table
recordCount = DCount("*", tableName)
Set rs = CurrentDb.OpenRecordset(tableName)
For I = 1 To recordCount
PM = rs!PMNum
Frequency = rs!Frequency
Frequnit = rs!Frequnit
StartingDate = rs!NextDate1
For J = 1 To howMany
NextDate = GetNextDate(Frequency, Frequnit, StartingDate, J)
strSql = "Insert into " & tableName & " (PMNum,Frequency,Frequnit,NextDate1) values ('" & PM & "', " & Frequency & ", '" & Frequnit & "', #" & NextDate & "#)"
CurrentDb.Execute strSql
Next J
rs.MoveNext
Next I
End Sub 

results

#### CODE

PMNum   Frequency    Frequnit     NextDate1
PM1       1              Months  10/12/2015
PM2       2              Weeks    10/12/2015
PM3       30           Days        10/12/2015
PM4       1              Years       10/12/2015
PM2       2              Weeks    10/26/2015
PM2       2              Weeks    11/9/2015
PM3       30           Days         11/11/2015
PM1       1              Months  11/12/2015
PM2       2              Weeks    11/23/2015
PM2       2              Weeks    12/7/2015
PM3       30           Days         12/11/2015
PM1       1              Months   12/12/2015
PM2       2              Weeks     12/21/2015
PM2       2              Weeks     1/4/2016
PM3       30           Days          1/10/2016
PM1       1              Months    1/12/2016
PM2       2              Weeks      1/18/2016
PM2       2              Weeks      2/1/2016
PM3       30           Days           2/9/2016
PM1       1              Months    2/12/2016
PM2       2              Weeks     2/15/2016
PM2       2              Weeks     2/29/2016
PM3       30           Days          3/10/2016
PM1       1              Months    3/12/2016
PM3       30           Days          4/9/2016
PM1       1              Months    4/12/2016
PM3       30           Days          5/9/2016
PM1       1              Months   5/12/2016
PM3       30           Days         6/8/2016
PM1       1              Months   6/12/2016
PM3       30           Days         7/8/2016
PM1       1              Months  7/12/2016
PM3       30           Days         8/7/2016
PM1       1              Months  8/12/2016
PM4       1              Years       10/12/2016
PM4       1              Years       10/12/2017
PM4       1              Years       10/12/2018
PM4       1              Years       10/12/2019
PM4       1              Years       10/12/2020
PM4       1              Years       10/12/2021
PM4       1              Years       10/12/2022
PM4       1              Years       10/12/2023
PM4       1              Years       10/12/2024
PM4       1              Years       10/12/2025 

### RE: converting an excel formula to use in access

(OP)
MajP,
Did you run this in Excel?
or access?
Where do I add the code?
I need some steps as to where to add the code and how to run it.
tav

#### Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

#### Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Close Box

# Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!