I'm trying to figure out how to filter the customers list sheet by using the following expression:
Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]
Should this be applied to the query table?
Filter Customer Details
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter Customer Details
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Customer Details
You can use code like this (for example in the On Click code of a command button on the form):
Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
Me.FilterOn = True
Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
Me.FilterOn = True
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter Customer Details
When tried as
I'm getting the message in the attached image?
Code: Select all
Private Sub Search_Click()
Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
Me.FilterOn = True
End Sub
You do not have the required permissions to view the files attached to this post.
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Customer Details
Sorry, I forgot to include the field name. Assuming that you want to filter on Date Reg, change
Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
to
Me.Filter = "[Date Reg] Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
If you want to filter on Date of Birth, use that instead of Date Reg.
Me.Filter = "Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
to
Me.Filter = "[Date Reg] Between [Forms]![Customers List]![txtStartDate] And [Forms]![ Customers List]![txtEndDate]"
If you want to filter on Date of Birth, use that instead of Date Reg.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter Customer Details
In the hope to print either all or filtered records I've modified the code given by you in post 25451 and the modification is as follows.
The whole intention is to open up the report and display records whether the Customers List is filtered or in whole list. But the modification shows blank report is open unfiltered and when filtered even with either three rows or four rows it shows only the top row.
What may be the reason for this?
Note: report showing blank when the data sheet is not filtered is because the code is designed to show filtered records. Right?
Code: Select all
Private Sub cmdReport_Click()
On Error GoTo ErrHandler
' Get out if Serial is blank
If IsNull(Me.Serial) Then
MsgBox "There is no current order!", vbExclamation
Exit Sub
End If
' Save the record if it has been modified
If Me.Dirty Then
RunCommand acCmdSaveRecord
End If
' Open the report for the current order
DoCmd.OpenReport ReportName:="Customers", View:=acViewPreview, _
WhereCondition:="Serial=" & Me.Serial
Exit Sub
ErrHandler:
If Err = 2501 Then
' Report canceled, no need to do anything
Else
' Display error message
MsgBox Err.Description, vbExclamation
End If
End Sub
What may be the reason for this?
Note: report showing blank when the data sheet is not filtered is because the code is designed to show filtered records. Right?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Customer Details
This code is intended to open the report with only the current record from the form. The reason for this is that you explicitly asked for it. You cannot expect the code to transform itself magically if you want something else than you asked previously.
If I understand you correctly, you want the report to show the same records as the form. That means that you have to apply the same filter to the report as has been applied to the form. This code should do that:
If I understand you correctly, you want the report to show the same records as the form. That means that you have to apply the same filter to the report as has been applied to the form. This code should do that:
Code: Select all
Private Sub cmdReport_Click()
Dim strFilter As String
On Error GoTo ErrHandler
' Save the record if it has been modified
If Me.Dirty Then
RunCommand acCmdSaveRecord
End If
' Get filter
If Me.FilterOn Then
strFilter = Me.Filter
End If
' Open the report
DoCmd.OpenReport ReportName:="Customers", View:=acViewPreview, _
WhereCondition:=strFilter
Exit Sub
ErrHandler:
If Err = 2501 Then
' Report canceled, no need to do anything
Else
' Display error message
MsgBox Err.Description, vbExclamation
End If
End Sub
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter Customer Details
Yes Hans. You understood me correctly. The modified code has done the job.
Best Regards,
Adam
Adam
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter Customer Details
In the customers list the data rows get added as the last row on top instead of the last record being aged to the last row.
But in the customers table the rows get added accordingly, meaning the last record gets added to the last row and so.
How could the same be made in the form customers list?
But in the customers table the rows get added accordingly, meaning the last record gets added to the last row and so.
How could the same be made in the form customers list?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Customer Details
You have specified that the record source of the form, tblCustomers Query, is sorted ascending by Customer Name. If you want it (and hence the form) to be sorted another way, open the query in design view and specify the sort order that you want.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07
Re: Filter Customer Details
Hans. I'm trying to sort by the "RegDate". Meaning the last date on the bottom and the first date on top?
Best Regards,
Adam
Adam
-
- Administrator
- Posts: 78608
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Filter Customer Details
Clear the sort order from Customer Name.
Add the Reg Date field to the query grid.
Set the sort order for this field to ascending.
Clear the Show check box for this field to prevent it from being displayed twice (it is already included in tblCustomers.*)
Add the Reg Date field to the query grid.
Set the sort order for this field to ascending.
Clear the Show check box for this field to prevent it from being displayed twice (it is already included in tblCustomers.*)
Best wishes,
Hans
Hans
-
- SilverLounger
- Posts: 2347
- Joined: 23 Feb 2010, 12:07