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

Topic

New Reply

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:

Reply | Quote

Replies

You need to change it to a boolean expression in the criteria row for LaunchDate something like:
>Date()-60 And < Date()+420
You could also use the Between syntax which would be
Between Date()-60 And Date()+420
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.

Reply | Quote

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:

Part Number	2MonthsAgo	1MonthsAgo	ThisMonth	1MonthsAway	2MonthsAway
828222		Sample			Launch
548771	Sample			Launch

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

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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?

Reply | Quote

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:

 DateDiff("d", SampleDate, Date())  

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:

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) 

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!)

Reply | Quote

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)

Reply | Quote

(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.)

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

Reply | Quote

I’ll take a look at it later.

Reply | Quote

I’ll take a look at it later.

Reply | Quote

I think the attached database will do what you want. Instead of code, I used:

• A table containing the boundaries of the 30 day periods: -60, -30, , …, 390.
• A query calculating the DateDiff bewtween today and SampleDate / LaunchDate
• 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.
• 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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

I think the attached database will do what you want. Instead of code, I used:

• A table containing the boundaries of the 30 day periods: -60, -30, , …, 390.
• A query calculating the DateDiff bewtween today and SampleDate / LaunchDate
• 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.
• 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

Reply | Quote

(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.)

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

Reply | Quote

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)

Reply | Quote

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:

 DateDiff("d", SampleDate, Date())  

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:

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) 

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!)

Reply | Quote

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?

Reply | Quote

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:

Part Number	2MonthsAgo	1MonthsAgo	ThisMonth	1MonthsAway	2MonthsAway
828222		Sample			Launch
548771	Sample			Launch

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

Reply | Quote

You need to change it to a boolean expression in the criteria row for LaunchDate something like:
>Date()-60 And < Date()+420
You could also use the Between syntax which would be
Between Date()-60 And Date()+420
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.

Reply | Quote

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] DateAdd(“m”, -2, Date()) And [LaunchDate] < DateAdd("m", 14, Date())

Reply | Quote

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] DateAdd(“m”, -2, Date()) And [LaunchDate] < DateAdd("m", 14, Date())

Reply | Quote