Results 1 to 12 of 12
  1. #1
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query records that fall within a date range (2000 SR-1)

    Hans,

    I am hoping you would be willing to shed some light on a report I am trying to create. I need to show all records in a table that have a SampleDueDate OR a LaunchDate that falls within the following two dates: "less than 2 months ago" and "less than 14 months in the future". In other words, here's a natural language way to describe it:

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query records that fall within a date range (2000 SR-1)

    You need to change it to a boolean expression in the criteria row for LaunchDate something like:
    <font color=blue><font face="Georgia">>Date()-60 And < Date()+420</font face=georgia></font color=blue>
    You could also use the Between syntax which would be
    <font color=blue><font face="Georgia">Between Date()-60 And Date()+420</font face=georgia></font color=blue>
    Note that both of these take advantage of the fact that dates are stored as floating point numbers where the integer portion represents the number of days since 12/31/1899. If you want to be really accurate, you could use DateAdd and specify the period in months instead of days. Note that I used Date() instead of Now() - the latter includes the current time and can cause dates to not show up when they should or vice versa. Hope this makes sense and solves your problem.
    Wendell

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

    Re: Query records that fall within a date range (2000 SR-1)

    WendellB has already given you a complete answer.

    Just as an addition to his post: if you use Between ... And ..., the start and end date are included. So [LaunchDate] Between Date()-60 And Date()+420 is in fact equivalent to [LaunchDate] > = Date()-60 And [LaunchDate] < = Date()+420.

    Using DateAdd with months, the expression would become [LaunchDate] > DateAdd("m", -2, Date()) And [LaunchDate] < DateAdd("m", 14, Date())

  4. #4
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query records that fall within a date range (2000 SR-1)

    Thanks for the help so far.

    Can anyone advise me on the next stage of this little report. I have been asked to create a report that formats
    the results of the former query into a table structure such as that shown below:

    <table border=1><td>Part Number</td><td>2MonthsAgo</td><td>1MonthsAgo</td><td>ThisMonth</td><td>1MonthsAway</td><td>2MonthsAway</td><td>828222</td><td> </td><td>Sample</td><td> </td><td> </td><td>Launch</td><td>548771</td><td>Sample </td><td> </td><td> </td><td>Launch</td><td> </td></table>

    Basically, for each record returned in the query, tabulate it such that the SampleDate interval is shown as a word "SAMPLE"
    under the appropriate column and the LaunchDate interval is shown as a word "LAUNCH" under the appropriate column. I
    hope that is clear. Essentially it might be compared loosely to a Gantt Chart.

    I was imagining a case statement for each record returned in which the appropriate textbox, in the report detail would contain
    the associated keyword under the appropriate date column. So far I've constructed a report that has a row of unbound
    textboxes for the records and the header that are associated with the appropriate interval from DATE(). That being, 2 months
    ago, 1 month ago, now, 1 month away, etc.

    Any advise you can provide would be most helpful!!

    Drew

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

    Re: Query records that fall within a date range (2000 SR-1)

    What do you mean by the "SampleDate interval"? In other words, how do I determine if SampleDate is "1MonthsAgo"? If it is exactly one month ago, or what?

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query records that fall within a date range (2000 SR-1)

    To do what you show in the table will probably require calculating the difference between the Sample Date and the current date (Date()) and the same for the Launch Date. You can do that with the function DateDiff() - see Access help on doing date calculations - and you can do it in terms of months, though as Hans points out, the boundary question needs to be answered. Ths is, what constitutes ThisMonth? Is it 15 days either way from the current date, or is it anything within the current month, etc.? Once you have those calculations, you can create a new set of Calulated Fields that are done with IIF statements to set the value to Null or one of the two words, Sample or Launch. On question is what happens if the Sample and Launch dates are both in the same month?
    Wendell

  7. #7
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query records that fall within a date range (2000 SR-1)

    It's unlikely at best that the Sample date and the Launch date would ever fall in teh same month as the time period between these two developments would be at least 2 -6 months. However, that is a good point. Perhaps I'll just refer to days instead of months. So, using that as an example, I would likely use:
    <pre> DateDiff("d", SampleDate, Date()) </pre>


    However, I have tried to use this type of statement and I keep getting an error about attempting to assign a value of "Null" to a variable. I find the help included in Access to be limited at best. There is usually only one example for each command and that example only shows the few lines of code without showing how the variables are declared, etc. etc. This is where I need the help. I am not sure how to reference the textboxes on the form. I thought I would use something like the following:


    <pre>Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim iSampleDueDiff As Integer
    Dim iSampleDueDate As Date
    Dim iLaunchDate As Date

    iSampleDueDiff = DateDiff("d", Date, SampleDate)
    </pre>


    where the variable SampleDate is one of the fields in the Access database which contains the date. That's where the code fails. How do I correctly refer to the SampleDate field in each record? What happens if there is no date in that field? How do I trap/skip over that error should this formula encounter an empty cell?

    Drew (confused!)

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

    Re: Query records that fall within a date range (2000 SR-1)

    You cannot refer directly to a field in the record source of a report. There must be a control bound to the field, but this control can be hidden (Visible set to No) if necessary. You can refer to a control on a report just by its name, or as Me.ControlName or Me!ControlName.

    To avoid problems with empty (null) values, you can use the Nz function to replace them with a non-null value. In this case, you could use a date that won't occur in your data, for instance 1/1/100:

    DateDiff("d", Nz(Me.SampleDate, #1/1/100#), Date)

  9. #9
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query records that fall within a date range (2000 SR-1)

    (Edited by HansV to prevent horizontal scrolling.)

    OK. I am going crazy. I have a query that returns the appropriate records from a table based on some mathematical criteria (i.e. the dates that I've already mentioned). I still can't get the report formatted to do what I want. I've tried many different ideas and I either get errors or it just doesn't return any data. Basically, I don't know how I should be even approaching this task. Should I build a module, a function, an event procedure that runs when the report is loaded? I have no idea.

    Here's what I got and I know it isn't right, but perhaps you can see what incorrect assumptions I am making: (BTW, I have only tried to accomplish the first
    step of assigning the "SAMPLE" keyword under the appropriate time period column. I will also need the LAUNCH keyword under the appropriate column once I get this part figured out.)
    <pre>Option Compare Database

    Public Sub DeliveryForecastReport()

    Dim db1 As Database
    Dim rs1 As Recordset
    Dim iSampleDueDiff As Long
    Dim rd1 As Recordset

    Set db1 = CurrentDb

    'I know the statement below is wrong, but I don't know
    'how to work with the results of the query
    'The field name SampleDueDue is actually correct. The original developer used it

    Set rd1 = db1.OpenRecordset("Select SampleDueDue From [qry,DeliveryForecast]")

    With rd1

    Do Until .EOF

    iSampleDueDiff = DateDiff("d", Nz(SampleDueDue, #1/1/100#), Date)

    Select Case iSampleDueDiff

    Case -60 To -30
    lblDaysAgo60.Caption = "SAMPLE"
    Case -30 To 0
    lblDaysAgo30.Caption = "SAMPLE"
    Case 0 To 30
    lblDaysAgoCurrent.Caption = "SAMPLE"
    Case 30 To 60
    lblDaysFuture30.Caption = "SAMPLE"
    Case 60 To 90
    lblDaysFuture60.Caption = "SAMPLE"
    Case 90 To 120
    lblDaysFuture90.Caption = "SAMPLE"
    Case 120 To 150
    lblDaysFuture120.Caption = "SAMPLE"
    Case 150 To 180
    lblDaysFuture150.Caption = "SAMPLE"
    Case 180 To 210
    lblDaysFuture180.Caption = "SAMPLE"
    Case 210 To 240
    lblDaysFuture210.Caption = "SAMPLE"
    Case 240 To 270
    lblDaysFuture240.Caption = "SAMPLE"
    Case 270 To 300
    lblDaysFuture270.Caption = "SAMPLE"
    Case 300 To 330
    lblDaysFuture300.Caption = "SAMPLE"
    Case 330 To 360
    lblDaysFuture330.Caption = "SAMPLE"
    Case 360 To 390
    lblDaysFuture360.Caption = "SAMPLE"
    End Select
    Loop
    End With

    End Sub</pre>


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

    Re: Query records that fall within a date range (2000 SR-1)

    I'll take a look at it later.

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

    Re: Query records that fall within a date range (2000 SR-1)

    I think the attached database will do what you want. Instead of code, I used:
    <UL><LI>A table containing the boundaries of the 30 day periods: -60, -30, , ..., 390.
    <LI>A query calculating the DateDiff bewtween today and SampleDate / LaunchDate
    <LI>A crosstab query whose value field is -1 if the SampleDate falls within a 30 day period, +1 if the LaunchDate falls within the period, 0 otherwise.
    <LI>A report based on the crosstab query. Text instead of numbers is displayed by setting the Format property of the text boxes to "Launch","Sample","".[/list]HTH
    Attached Files Attached Files

  12. #12
    Star Lounger
    Join Date
    Feb 2001
    Location
    Ontario, Canada
    Posts
    80
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query records that fall within a date range (2000 SR-1)

    Thanks for all your help. I did actually look at the reply above and fit it into our database and it worked great!! Sorry for not thanking you earlier, I just got swamped and neglected to go back to offer my thanks.

    Drew

Posting Permissions

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