Filtering reports using forms

[domibud]

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?

 

[Duane Hookom]

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

 

[domibud]

Thanks for your help.
These last few days, I tried your advice, but I can't get it to work.
I can only open the report, without any record on it.
In the filter form, the user will input their name and date range. From your
code, I implied that it will filter the date range only.

I tried using query for the report, and it work just fine. But the user will
have to enter their criteria with 3 different step.
If I use form, they just have to do it in one step.

Any other advice on how to filter the user name and the date range for the
record?

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

--
Duane Hookom
Microsoft Access MVP

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?

 

[Duane Hookom]

Have you created the form with the controls for entering the dates and name?
What are the names of the controls and what are the names and data types of
the corresponding fields?

Did you remove the criteria from the report's record source?

--
Duane Hookom
Microsoft Access MVP

Thanks for your help.
These last few days, I tried your advice, but I can't get it to work.
I can only open the report, without any record on it.
In the filter form, the user will input their name and date range. From your
code, I implied that it will filter the date range only.

I tried using query for the report, and it work just fine. But the user will
have to enter their criteria with 3 different step.
If I use form, they just have to do it in one step.

Any other advice on how to filter the user name and the date range for the
record?

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

--
Duane Hookom
Microsoft Access MVP

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?

 

[domibud]

The form controls are:
1. StartDate
2. EndDate
3. EmployeeName

and the corresponding fields are:
1. Name with data types text
2. DateUse with data type date

Ya, I did remove the criteria from the record source.

For problems like this, which is more appropriate, Query or Table as the
report's record source?
Since at the beginning I design the report using query as the recordsource,
and the form as the user interface to pass on the criteria to the query.

Thanks for your time.

Regards,

domibud

Have you created the form with the controls for entering the dates and name?
What are the names of the controls and what are the names and data types of
the corresponding fields?

Did you remove the criteria from the report's record source?

--
Duane Hookom
Microsoft Access MVP

Thanks for your help.
These last few days, I tried your advice, but I can't get it to work.
I can only open the report, without any record on it.
In the filter form, the user will input their name and date range. From your
code, I implied that it will filter the date range only.

I tried using query for the report, and it work just fine. But the user will
have to enter their criteria with 3 different step.
If I use form, they just have to do it in one step.

Any other advice on how to filter the user name and the date range for the
record?

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

--
Duane Hookom
Microsoft Access MVP


:

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?

 

[Duane Hookom]

I would expect the code would look something like:

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
strWhere = "1=1 "
If Not IsNull(Me.StartDate) Then
strWhere = strWhere & " AND [DateUse] >=#" & _
Me.StartDate & "# "
End If
If Not IsNull(Me.EndDate) Then
strWhere = strWhere & " AND [DateUse] <=#" & _
Me.EndDate & "# "
End If
If Not IsNull(Me.EmployeeName) Then
strWhere = strWhere & " AND [Name] =""" & _
Me.EmployeeName & """ "
End If
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

BTW: Name is a reserved word and should be avoided as the name of a field.
--
Duane Hookom
Microsoft Access MVP

The form controls are:
1. StartDate
2. EndDate
3. EmployeeName

and the corresponding fields are:
1. Name with data types text
2. DateUse with data type date

Ya, I did remove the criteria from the record source.

For problems like this, which is more appropriate, Query or Table as the
report's record source?
Since at the beginning I design the report using query as the recordsource,
and the form as the user interface to pass on the criteria to the query.

Thanks for your time.

Regards,

domibud

Have you created the form with the controls for entering the dates and name?
What are the names of the controls and what are the names and data types of
the corresponding fields?

Did you remove the criteria from the report's record source?

--
Duane Hookom
Microsoft Access MVP

Thanks for your help.
These last few days, I tried your advice, but I can't get it to work.
I can only open the report, without any record on it.
In the filter form, the user will input their name and date range. From your
code, I implied that it will filter the date range only.

I tried using query for the report, and it work just fine. But the user will
have to enter their criteria with 3 different step.
If I use form, they just have to do it in one step.

Any other advice on how to filter the user name and the date range for the
record?


:

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

--
Duane Hookom
Microsoft Access MVP


:

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?

 

[domibud]

Thanks a lot for your help.
Now the filter process works.

I would expect the code would look something like:

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
strWhere = "1=1 "
If Not IsNull(Me.StartDate) Then
strWhere = strWhere & " AND [DateUse] >=#" & _
Me.StartDate & "# "
End If
If Not IsNull(Me.EndDate) Then
strWhere = strWhere & " AND [DateUse] <=#" & _
Me.EndDate & "# "
End If
If Not IsNull(Me.EmployeeName) Then
strWhere = strWhere & " AND [Name] =""" & _
Me.EmployeeName & """ "
End If
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

BTW: Name is a reserved word and should be avoided as the name of a field.
--
Duane Hookom
Microsoft Access MVP

The form controls are:
1. StartDate
2. EndDate
3. EmployeeName

and the corresponding fields are:
1. Name with data types text
2. DateUse with data type date

Ya, I did remove the criteria from the record source.

For problems like this, which is more appropriate, Query or Table as the
report's record source?
Since at the beginning I design the report using query as the recordsource,
and the form as the user interface to pass on the criteria to the query.

Thanks for your time.

Regards,

domibud

Have you created the form with the controls for entering the dates and name?
What are the names of the controls and what are the names and data types of
the corresponding fields?

Did you remove the criteria from the report's record source?

--
Duane Hookom
Microsoft Access MVP


:

Thanks for your help.
These last few days, I tried your advice, but I can't get it to work.
I can only open the report, without any record on it.
In the filter form, the user will input their name and date range. From your
code, I implied that it will filter the date range only.

I tried using query for the report, and it work just fine. But the user will
have to enter their criteria with 3 different step.
If I use form, they just have to do it in one step.

Any other advice on how to filter the user name and the date range for the
record?


:

You can't change a property of the report that isn't open.

Can you leave the record source of the report unfiltered. Then modify your
code to use a Where Condition in the DoCmd.OpenReport method.

Private Sub CreateSalesReport_Click()
Dim StrWhere As String
Dim StartDate As String
Dim EndDate As String
StrWhere = "DateUse Between #" & StartDate & _
"# And #" & EndDate & "#"
DoCmd.OpenReport "SalesTaxiExpenseReport", , , strWhere
End Sub

--
Duane Hookom
Microsoft Access MVP


:

Hi... I'm trying to make a database for employees transport/taxi expenses.
This is the first time I'm making a database and I'm new to VBA language.
I'm using Ms. Access 2003 and this database will be in a multi-user
environment.
The output is employee's expense report.

I made 1 report in Access, and I want the user to be able to enter certain
criteria/filter for his/her report.

For entering those criteria/filter I made a form with 3 text box:
1. for entering employee's name
2 & 3. for entering StartDate and EndDate

From that form, I made a button to open the report.
Here's the code for that button:

Private Sub CreateSalesReport_Click()
Dim StrSql As String
Dim YourName As String
Dim StartDate As String
Dim EndDate As String
StrSql = "SELECT [VoucherReleaseTest].[#2-#8],
VoucherReleaseTest].Requester, [VoucherDetailed].DateUse, " _
& "[VoucherDetailed].Fare, [VoucherDetailed].DepartAddr,
[VoucherDetailed].DestAddr, " _
& "[VoucherDetailed].DestCo, [VoucherDetailed].Remark " _
& "FROM [VoucherReleaseTest] INNER JOIN [VoucherDetailed] " _
& "ON [VoucherReleaseTest].[#2-#8] = [VoucherDetailed].[#2-#8] " _
& "WHERE ((([VoucherReleaseTest].Requester)= YourName) AND " _
& "(([VoucherDetailed].DateUse) Between #" & StartDate & "# And
#" & EndDate & "#));"
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
DoCmd.OpenReport ("SalesTaxiExpenseReport")
End Sub

When I run this, I've an error message (run-time error 2451):
The report name 'SalesTaxiExpenseReport' you entered is misspelled and
refers to a report that isn't open or doesn't exist.

When I debug, the error seems to be in this line:
Application.Reports("SalesTaxiExpenseReport").RecordSource = StrSql
I already double checked the report's name, and I've got it right.

When I change above code to:
Application.Reports(0).RecordSource = StrSql

I've got an error message (run-time error 2457):
The number you used to refer to the report is invalid

I only have 1 report in this database, and from access help, I know that the
report index start from 0.

Can anyone help me to the right direction?