Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Calculating a date (Access 2000)

    Database table gathers information about reports filed. Information gathered includes FilingID, CommitteeID, ElectionDate, ElectionYear (y/n), ReportDue, ReportDescription, DateReceived, HowFiled, etc.

    I need ReportDue to be calculated based on ElectionDate, ElectionYear and ReportDescription.

    ReportDescription is a lookup to another table - each description has an ID
    ID - Description
    1 - Jan 19 of the next calendar year
    2 - May 19
    3 - July 19
    4 - Oct 19
    5 - Five Days Before Election
    6 - First of Month After Election
    7 - Final Report

    If it's an election year, reports due would be #2, 3, 5, 6, 1.

    If it's a non-election year, there's no election date and reports due would be #1 and 4.

    I have minimal SQL and no VBA experience, although I've been using Access for years for pretty simple projects. I appreciate any help I can get on this! <img src=/S/crossfingers.gif border=0 alt=crossfingers width=17 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating a date (Access 2000)

    It's not clear to me what you want to be calculated. Do you want to return multiple values for ReportDue, and if so, where do these multiple values go? If not, what do you want?

  3. #3
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date (Access 2000)

    Sorry I was not clear. <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15>
    Right now the ReportDue field is just set up as a regular date field. I would like to be able to populate it with a date that is based on whatever ReportDescription is selected. For most selections it is pretty straight forward (July 19 or May 19 of the current year), but in the case of "Five Days Before Election" or "First of Month After Election" those are based on the ReportDescription selected and ElectionDate that is entered.

    I should add that the ReportDue then is reviewed to determine if the report is late and if a fine is imposed. But this can be determined by the person reviewing it.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating a date (Access 2000)

    Strictly speaking, ReportDue should not be a field in your table, because it is derived information. You can calculate it in a query, see below. I used the Switch function; it allows you to handle a series of options without nesting IIf's and without VBA (although VBA would be more elegant.)

    ReportDue: Switch([ReportDescription]=1,DateSerial(Year(Date())+1,1,19),[ReportDescription]=2,DateSerial(Year(Date()),5,19),[ReportDescription]=3,DateSerial(Year(Date()),7,19),[ReportDescription]=4,DateSerial(Year(Date()),10,19),[ReportDescription]=5,[ElectionDate]-5,[ReportDescription]=6,DateSerial(Year([ElectionDate]),Month([ElectionDate])+1,1),[ReportDescription]=7,DateSerial(2009,12,31))

    Note: you haven't indicated how ReportDue should be calculated if ReportDescription is 7 (Final Report). I just used a dummy date; you can replace it with a more appropriate one.

  5. #5
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date (Access 2000)

    Thanks so much! I will try it out.

  6. #6
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date (Access 2000)

    Another question...using the info you provided, how do I tell my query or my form to open without needing the ReportDescription first? When I try to open the query in Datasheet View, it asks for Parameter Value. Here's the SQL as it stands now. I'm sure I'm missing something, just don't know enough to know what!
    Thanks once more...

    SELECT TBLfilinginformation.CommitteeID, TBLorganizationinformation.CommitteeName, TBLfilinginformation.YearID, TBLfilinginformation.ElectionDate, TBLfilinginformation.ElectionYear, TBLfilinginformation.ReportID, TBLfilinginformation.HowFiledID, TBLfilinginformation.DateReceived, TBLfilinginformation.USPostmark, TBLfilinginformation.FiledTimely, TBLfilinginformation.PastDueLetter, TBLfilinginformation.CPAmountID, TBLfilinginformation.CivilPenaltyLetter, TBLfilinginformation.CPDisposition, TBLfilinginformation.ReferredDate, TBLfilinginformation.ReferralReason, TBLfilinginformation.Comments, TBLfilinginformation.WaiverRequested, TBLfilinginformation.BoardMeeting, TBLfilinginformation.ActionTaken,

    Switch([ReportDescription]=1,DateSerial(Year(Date())+1,1,19),[ReportDescription]=2,DateSerial(Year(Date()),5,19),[ReportDescription]=3,DateSerial(Year(Date()),7,19),[ReportDescription]=4,DateSerial(Year(Date()),10,19),[ReportDescription]=5,[ElectionDate]-5,[ReportDescription]=6,DateSerial(Year([ElectionDate]),Month([ElectionDate])+1,1),[ReportDescription]=7,DateSerial(2009,12,31)) AS ReportDue

    FROM TBLorganizationinformation INNER JOIN TBLfilinginformation ON TBLorganizationinformation.CommitteeID = TBLfilinginformation.CommitteeID;

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Calculating a date (Access 2000)

    In your first post in this thread, you wrote that ReportDescription was among the fields of the table. If so, you shouldn't get a parameter prompt. Or is ReportID the actual name of the field? If so, replace ReportDescription with ReportID in the Switch function (7 times)

  8. #8
    New Lounger
    Join Date
    Nov 2003
    Location
    Iowa, USA
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Calculating a date (Access 2000)

    Thanks Hans for all your help. I had to do a couple other changes, but it's working now. Couldn't have come even close without your help!

Posting Permissions

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