Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    100

    Unanswered: Comparing fields from table and inserting data into another

    Here is my situation:

    My company uses very strange fiscal months that end only on Saturday and are either 28 or 35 days. These months often end in different calendar months. I will be importing specific work days from through SQL into an Access table (TimeEntryDate as seen below). On this same table I would like to have it fill in the appropriate fiscal month for the specific date (FiscalMonth field seen below). Here is a sample:

    The table I am trying to fill:

    TimeEntryDate..............FiscalMonth
    10/2/1991......................xxx
    10/7/1991......................xxx

    My source of information in a different table (dates that fall on or before FiscalMonthEnddate get assigned the prior month):

    FiscalMonthEnd..............Month
    8/3/1991......................August
    9/7/1991......................September
    10/5/1991......................October
    11/2/1991......................November
    12/7/1991......................December
    1/4/1992......................January
    2/1/1992......................February

    Let pretend I worked on 10/2/1991 as shown above. Then I would like the field FiscalMonth to display "September" or if I worked 10/7/1991 I would like the field FiscalMonth to display "October."


    I hope this makes sense! Thanks for your help. (I'm pretty new at this if you can't tell)

    Norm

  2. #2
    Join Date
    Jan 2004
    Posts
    4

    Re: Comparing fields from table and inserting data into another

    Originally posted by norm801
    Here is my situation:

    My company uses very strange fiscal months that end only on Saturday and are either 28 or 35 days. These months often end in different calendar months. I will be importing specific work days from through SQL into an Access table (TimeEntryDate as seen below). On this same table I would like to have it fill in the appropriate fiscal month for the specific date (FiscalMonth field seen below). Here is a sample:

    The table I am trying to fill:

    TimeEntryDate..............FiscalMonth
    10/2/1991......................xxx
    10/7/1991......................xxx

    My source of information in a different table (dates that fall on or before FiscalMonthEnddate get assigned the prior month):

    FiscalMonthEnd..............Month
    8/3/1991......................August
    9/7/1991......................September
    10/5/1991......................October
    11/2/1991......................November
    12/7/1991......................December
    1/4/1992......................January
    2/1/1992......................February

    Let pretend I worked on 10/2/1991 as shown above. Then I would like the field FiscalMonth to display "September" or if I worked 10/7/1991 I would like the field FiscalMonth to display "October."


    I hope this makes sense! Thanks for your help. (I'm pretty new at this if you can't tell)

    Norm
    Hi, I had a similar issue facing me a while back. What I ended up doing was using a Function within Access. This could then be used to match the specific date to a Fiscal Month.

    To do this, create a function. Below is the code I used, just customise it for your purpose:

    Function fnPeriod(transDate As Date) As Integer
    fnPeriod = IIf([transDate] < DateSerial(2002, 7, 1), 0, _
    IIf([transDate] < DateSerial(2002, 7, 29), 1, _
    IIf([transDate] < DateSerial(2002, 9, 2), 2, _
    IIf([transDate] < DateSerial(2002, 9, 13), 3.1, _
    IIf([transDate] < DateSerial(2002, 9, 23), 3.2, _
    IIf([transDate] < DateSerial(2002, 10, 28), 4, _
    IIf([transDate] < DateSerial(2002, 12, 2), 5, _
    IIf([transDate] < DateSerial(2002, 12, 30), 6, _
    IIf([transDate] < DateSerial(2003, 1, 27), 7, _
    IIf([transDate] < DateSerial(2003, 3, 3), 8, _
    IIf([transDate] < DateSerial(2003, 3, 31), 9, _
    IIf([transDate] < DateSerial(2003, 4, 28), 10, _
    IIf([transDate] < DateSerial(2003, 6, 2), 11, _
    IIf([transDate] < DateSerial(2003, 6, 30), 12, 13))))))))))))))
    End Function

    Then in a query you create a field with the following expression to get the fical period:

    FiscalDate: fnPeriod(Datevalue([Date]))

    This will then reference the date to the function to get the appropriate period.

    A note to remember is that when I use this function I have to set my global windows regional settings for short date format to dd/mm/yyyy

    Hope this works for you!

    Let me know!

  3. #3
    Join Date
    Jan 2004
    Posts
    100

    Re: Comparing fields from table and inserting data into another

    Originally posted by Gwolfe28
    Hi, I had a similar issue facing me a while back. What I ended up doing was using a Function within Access. This could then be used to match the specific date to a Fiscal Month.

    To do this, create a function. Below is the code I used, just customise it for your purpose:

    Function fnPeriod(transDate As Date) As Integer
    fnPeriod = IIf([transDate] < DateSerial(2002, 7, 1), 0, _
    IIf([transDate] < DateSerial(2002, 7, 29), 1, _
    IIf([transDate] < DateSerial(2002, 9, 2), 2, _
    IIf([transDate] < DateSerial(2002, 9, 13), 3.1, _
    IIf([transDate] < DateSerial(2002, 9, 23), 3.2, _
    IIf([transDate] < DateSerial(2002, 10, 28), 4, _
    IIf([transDate] < DateSerial(2002, 12, 2), 5, _
    IIf([transDate] < DateSerial(2002, 12, 30), 6, _
    IIf([transDate] < DateSerial(2003, 1, 27), 7, _
    IIf([transDate] < DateSerial(2003, 3, 3), 8, _
    IIf([transDate] < DateSerial(2003, 3, 31), 9, _
    IIf([transDate] < DateSerial(2003, 4, 28), 10, _
    IIf([transDate] < DateSerial(2003, 6, 2), 11, _
    IIf([transDate] < DateSerial(2003, 6, 30), 12, 13))))))))))))))
    End Function

    Then in a query you create a field with the following expression to get the fical period:

    FiscalDate: fnPeriod(Datevalue([Date]))

    This will then reference the date to the function to get the appropriate period.

    A note to remember is that when I use this function I have to set my global windows regional settings for short date format to dd/mm/yyyy

    Hope this works for you!

    Let me know!

    This was helpful and did work. The only problem is that I am dealing with about 30 years and each year is different. Is there a way to make this part of a larger piece that checks for the year first and then goes to the appropriate line within that year?

    Thanks!

    Norm

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •