INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • 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.

Jobs

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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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
      NextDate = DateAdd(interval, theNumber, theDate)
      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.

How about just those fields:
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,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

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
   GetNextDate = DateAdd(interval, theNumber, theDate)
   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
  'before adding records
  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.
thanks for your time
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.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

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:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close