Sorting Data by a Range of Dates

[Paul Hill]

Preface this question with the info that I'm a novice when it comes to
Access. I've had the program for years but have never had the opportunity to
use it until recently.

I know that my question has a very simple solution. However, keeping in
mind my novice status, I'd ask that you be very specific in your reply.

I created a database that has 5 fields. One of the fields is DATE. I have
about 5 months worth of data in the file. I now have to sort this
information by the client field and a range of dates. For example, I need to
find out how many visits were made to ABC Company from 1/1/2008 to 1/31/2008.
I need to be able to break this databse down to the individual clients and a
specific range of date.

How the hell do I do it????? I've been trying to figure it out for the past
two weeks and I've been very unsuccessful. I did learn about 500 ways that
you CAN'T do it.

Again, please be very specific from the very beginning of the process to the
end of it.

Thank you,

Paul Hill

 

[Duane Hookom]

I don't know what you have tried. I expect you want some user interface that
allows you to filter your report based on date range and client. The first
task is to create a form with a combo box to select the primary key value of
your client record. Name this combo box "cboClientID". Then add two text
boxes "txtStart" and "txtEnd".

Use the command button wizard to create a button that opens your report.
Then modify the code in the On Click event of the button to be something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "1 = 1 "
stDocName = "YourReportNameHere"
If Not IsNull(Me.cboClientID) Then
strWhere = strWhere & " AND [ClientID]=" & _
Me.cboClientID
' if ClientID is a text field then use
' strWhere = strWhere & " AND [ClientID]=""" & _
Me.cboClientID & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [Date]>=#" & _
txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [Date]<=#" & _
txtEnd & "# "
End If

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdRptCustLabels_Click:
Exit Sub

Err_cmdRptCustLabels_Click:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_cmdRptCustLabels_Click

End Sub

 

[Paul Hill]

Duane:

Thank you for the information but it's not really what I'm looking for.
Maybe I screwed up and didn't explain it clear enough. Bear with me while I
try it again.

I have a database with 5 fields. All I want to do is sort the info in the
database by cient and a range of dates. For example, I want to sort this
database for our client Stone Ridge. First field to sort by is Stone Ridge.
Then I want to see how many contact were made between 1/1/2008 and 1/31/2008.

Basically, I want to sort the database by individual client and then by a
range of dates. I'm somewhat familiar with Lotus Approach and it's a fairly
simple operation to sort the info but my company uses Access and I don't have
a clue as to how to do this simple sort.

Thanks for your help,

Paul Hill

I don't know what you have tried. I expect you want some user interface that
allows you to filter your report based on date range and client. The first
task is to create a form with a combo box to select the primary key value of
your client record. Name this combo box "cboClientID". Then add two text
boxes "txtStart" and "txtEnd".

Use the command button wizard to create a button that opens your report.
Then modify the code in the On Click event of the button to be something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "1 = 1 "
stDocName = "YourReportNameHere"
If Not IsNull(Me.cboClientID) Then
strWhere = strWhere & " AND [ClientID]=" & _
Me.cboClientID
' if ClientID is a text field then use
' strWhere = strWhere & " AND [ClientID]=""" & _
Me.cboClientID & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [Date]>=#" & _
txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [Date]<=#" & _
txtEnd & "# "
End If

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdRptCustLabels_Click:
Exit Sub

Err_cmdRptCustLabels_Click:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_cmdRptCustLabels_Click

End Sub
--
Duane Hookom
Microsoft Access MVP

Preface this question with the info that I'm a novice when it comes to
Access. I've had the program for years but have never had the opportunity to
use it until recently.

I know that my question has a very simple solution. However, keeping in
mind my novice status, I'd ask that you be very specific in your reply.

I created a database that has 5 fields. One of the fields is DATE. I have
about 5 months worth of data in the file. I now have to sort this
information by the client field and a range of dates. For example, I need to
find out how many visits were made to ABC Company from 1/1/2008 to 1/31/2008.
I need to be able to break this databse down to the individual clients and a
specific range of date.

How the hell do I do it????? I've been trying to figure it out for the past
two weeks and I've been very unsuccessful. I did learn about 500 ways that
you CAN'T do it.

Again, please be very specific from the very beginning of the process to the
end of it.

Thank you,

Paul Hill

 

[Duane Hookom]

A couple symantic comments. Use "table" or "query" rather than "database".
Your "database" contains tables, queries, forms, reports, code, etc. Tables
contain records.

"Sort" means to place the records in a specific order. I think you are
attempting to do is "filter" the records that display in your report.
Filtering a report is what I described how to do. If you really want to sort,
use the Sorting and Grouping dialog in the report design.

My suggested solution allows you to make a selection and a couple entries on
a form to filter the results/records that are displayed in a report.

--
Duane Hookom
Microsoft Access MVP

Duane:

Thank you for the information but it's not really what I'm looking for.
Maybe I screwed up and didn't explain it clear enough. Bear with me while I
try it again.

I have a database with 5 fields. All I want to do is sort the info in the
database by cient and a range of dates. For example, I want to sort this
database for our client Stone Ridge. First field to sort by is Stone Ridge.
Then I want to see how many contact were made between 1/1/2008 and 1/31/2008.

Basically, I want to sort the database by individual client and then by a
range of dates. I'm somewhat familiar with Lotus Approach and it's a fairly
simple operation to sort the info but my company uses Access and I don't have
a clue as to how to do this simple sort.

Thanks for your help,

Paul Hill

I don't know what you have tried. I expect you want some user interface that
allows you to filter your report based on date range and client. The first
task is to create a form with a combo box to select the primary key value of
your client record. Name this combo box "cboClientID". Then add two text
boxes "txtStart" and "txtEnd".

Use the command button wizard to create a button that opens your report.
Then modify the code in the On Click event of the button to be something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

Dim stDocName As String
Dim strWhere As String
strWhere = "1 = 1 "
stDocName = "YourReportNameHere"
If Not IsNull(Me.cboClientID) Then
strWhere = strWhere & " AND [ClientID]=" & _
Me.cboClientID
' if ClientID is a text field then use
' strWhere = strWhere & " AND [ClientID]=""" & _
Me.cboClientID & """ "
End If
If Not IsNull(Me.txtStart) Then
strWhere = strWhere & " AND [Date]>=#" & _
txtStart & "# "
End If
If Not IsNull(Me.txtEnd) Then
strWhere = strWhere & " AND [Date]<=#" & _
txtEnd & "# "
End If

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdRptCustLabels_Click:
Exit Sub

Err_cmdRptCustLabels_Click:
MsgBox Err.Number & vbCrLf & Err.Description
Resume Exit_cmdRptCustLabels_Click

End Sub
--
Duane Hookom
Microsoft Access MVP

Preface this question with the info that I'm a novice when it comes to
Access. I've had the program for years but have never had the opportunity to
use it until recently.

I know that my question has a very simple solution. However, keeping in
mind my novice status, I'd ask that you be very specific in your reply.

I created a database that has 5 fields. One of the fields is DATE. I have
about 5 months worth of data in the file. I now have to sort this
information by the client field and a range of dates. For example, I need to
find out how many visits were made to ABC Company from 1/1/2008 to 1/31/2008.
I need to be able to break this databse down to the individual clients and a
specific range of date.

How the hell do I do it????? I've been trying to figure it out for the past
two weeks and I've been very unsuccessful. I did learn about 500 ways that
you CAN'T do it.

Again, please be very specific from the very beginning of the process to the
end of it.

Thank you,

Paul Hill