Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Totals Query (2000)

    I have a Totals Query that has the following calculation in it:
    Status: Last(IIf([EnrollDt]=[Start date],"Dropped","New"))
    Start Date is a parameter that the person fills in. If they pick 10/1/03 (which they will) and that is the start date in the database it is ok but if the start date in the database is say 10/2/03 that doesn't match and it doesn't work. I tried EnrollDt > than Start date or enrollDt = Start date but of course that doesn't work either. How would I construct this calculation so that the person can put in 10/1/03 as a start date even though the date in the database could be anything between 10/1/03 and 10/31/03 for example. Hope this isn't too confusing.

  2. #2
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    If I get your drift... wouldn't '>=' address your issue... as this would give you anything on a date or in the future...

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    That doesn't work because Then whatever the end date is is greater than too so I get all "dropped". Maybe this can't be done the way I think.

  4. #4
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Can you nest the If statement to incorporate you end date as well then?

    Status: Last(IIf([EnrollDt]=[Start date],IIf([start date]>[End Date],"whatever","Dropped"),"New"))

  5. #5
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Given that you want (in this case) the "Dropped" value to apply for any provided date between 10/1/03 and 10/31/03, inclusive, I think the following should work:

    Last(IIf([Start date]>=#10/1/03# and [Start date]<=#10/31/03#,"Dropped","New"))

    If you need the 'EnrollDt' to be a variable, rather than a constant, then you'll need a starting date and ending date variable rather than just the single date.

    Alternatively, you could get a bit more fancy and use some of the date functions to extract the month and year (mm/yyyy) from [EnrollDt], then construct the two limiting dates in full mm/dd/yyyy format with the extracted information.
    -- Jim

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    This is the SQL that I am using presently:
    PARAMETERS [Start Date] DateTime, [End Date] DateTime;
    SELECT Compare2.ClNum, First(Compare2.[Client Name]) AS [FirstOfClient Name], Count(Compare2.ClNum) AS COUNTOFCLNUM, First(Compare2.EnrollDt) AS FirstOfEnrollDt, Last(IIf([EnrollDt]=[Start date],"Dropped","New")) AS Status
    FROM Compare2
    WHERE (((Compare2.EnrollDt) Between [Start Date] And [End Date]))
    GROUP BY Compare2.ClNum
    HAVING (((Count(Compare2.ClNum))=1));

    Could you tell me how to use some of the date functions to extract the month and year then construct the two limiting dates in full mm/dd/yyyy format with the extracted information. I think I can extract the month and year by: DT: DatePart("mm/yyyy",[EnrollDt]) but it is the "construct the two limiting dates in full mm/dd/yyyy format with the extracted information" that I don't understand.

  7. #7
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Dim intMonth as integer
    Dim intYear as integer
    Dim dtMonthStart, dtMonthEnd as date
    intMonth = Month(EnrollDt)
    intYear = Year(EnrollDt)
    dtMonthStart = DateSerial(intYear,intMonth,1)
    Select Case intMonth
    Case 4, 6, 9, 11
    dtMonthEnd=DateSerial(intYear,intMonth,30)
    Case 1, 3, 5, 7, 8, 10, 12
    dtMonthEnd=DateSerial(intYear,intMonth,31)
    Case Else
    dtMonthEnd=DateSerial(intYear,intMonth,28)
    End Select

    With a bit more cleverness, you can also adjust the Feb. calculation to handle leap years...
    -- Jim

  8. #8
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Thanks Jim but where do I put this code?

  9. #9
    Star Lounger
    Join Date
    Jan 2003
    Location
    Grass Valley, California, USA
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    Linda,

    I think you led me to believe that StartDate was provided by the user, most likely via a textbox control. If that assumption is correct, then you could put this code in the textbox field exit event.
    Alternatively, if the actual query is formulated as the result of a later click on a command button, the click event for the button could interrogate the textbox for the StartDate and perform the MonthStart and MonthEnd calculations then (before initiating the query).

    Without seeing your entire layout, it's difficult for me to be more specific. I hope the above provides some direction.
    -- Jim

  10. #10
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Totals Query (2000)

    The database is just for running reports. A monthly file is sent to me that was created from another report generating program that accesses our main Oracle database. I get the report in Excel format and then I export it into this database and use it to create different reports. I use a switchboard and different forms that have option buttons on them to select the different reports. The reports are controlled by a macro. This way the users of the database can see the data in different scenarios and don't have to be too computer literate. I just export a new refreshed Excel report on a monthly basis.

    The Start Date is just from a parameter query that I use for a report. Sorry if I didn't make that clear.

Posting Permissions

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