converting an excel formula to use in access

[tav1035]

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

 

[SkipVought]

Hi,

Errors???

Exactly what errors?

Your Excel formula has 5 IF()

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

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.

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

 

[Andrzejek]

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.

 

[tav1035]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[MajP]

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.

 

[MajP]

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

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

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

 

[tav1035]

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