Search working in one form but not another

[davethewelder]

Hi, I have a development dbase which I have designed the forms to search for
the no of Assessments completed and the number of Reviews completed. In this
dbase the Assessment table has 524 rows and the review table has 524 rows.

I have used the same calendar to populate the unbound textboxes e.g Allen
Browne's, and the same code for the filter in the On Click Event.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Review Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

The search on the Assessment form works but the Review form does not.
On the Calendar selection button the On Click code is
"=CalendarFor([txtStartDate],"Set the start date")".

I cannot see any difference in the code except the field name.
Can anyone throw any light on this?

Any help appreciated.

Davie

 

[Jeanette Cunningham]

Hi davethewelder,
when you say it doesn't work, do you mean that nothing happens after you
hit the search button?
Does the form show any records when it first opens?
Have you uncommented the line-->
'Debug.Print strWhere
and checked what access shows for strWhere.
Does the code compile?
Have you stepped through the filter code to check that each criteria string
is valid and returns records?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi, I have a development dbase which I have designed the forms to search
for
the no of Assessments completed and the number of Reviews completed. In
this
dbase the Assessment table has 524 rows and the review table has 524 rows.

I have used the same calendar to populate the unbound textboxes e.g Allen
Browne's, and the same code for the filter in the On Click Event.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Review Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

The search on the Assessment form works but the Review form does not.
On the Calendar selection button the On Click code is
"=CalendarFor([txtStartDate],"Set the start date")".

I cannot see any difference in the code except the field name.
Can anyone throw any light on this?

Any help appreciated.

Davie

 

[davethewelder]

Hi davethewelder,
when you say it doesn't work, do you mean that nothing happens after you
hit the search button?
Does the form show any records when it first opens?
Have you uncommented the line-->
'Debug.Print strWhere
and checked what access shows for strWhere.
Does the code compile?
Have you stepped through the filter code to check that each criteria string
is valid and returns records?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi, I have a development dbase which I have designed the forms to search
for
the no of Assessments completed and the number of Reviews completed. In
this
dbase the Assessment table has 524 rows and the review table has 524 rows.

I have used the same calendar to populate the unbound textboxes e.g Allen
Browne's, and the same code for the filter in the On Click Event.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Review Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

The search on the Assessment form works but the Review form does not.
On the Calendar selection button the On Click code is
"=CalendarFor([txtStartDate],"Set the start date")".

I cannot see any difference in the code except the field name.
Can anyone throw any light on this?

Any help appreciated.

Davie

Hi Jeanette, thanks for replying.

When i set the filter it returns a random number of records. When it
originally opens it returns the correct number of records in the table.
I have uncommented the line you suggested and the immediate window returns
the line below. The number of records and the dates do not match the
selection.

([Review Date] >= #02/01/2009#) AND ([Review Date] < #02/09/2009#)

Stepping through the code the the "strWhere " dimension reads "strWhere =
([Review Date] >#04/05/2009#) AND ([Review Date] <#04/13/2009#...
The [Review Date] does not show a value in the tooltip box, I don't know if
it should.

Hope this is clear enough.

Thanks again for replying.

Davie

 

[Jeanette Cunningham]

A couple of things to try.
Open the form in design view and clear any filters or order by text in the
form's property sheet.

Are you using A2007.
If yes, set the Filter on load property to no to see if this makes a
difference.

Check the query for the form's recordsource carefully to see if there is any
unintentional filtering happening there - check any criteria that may be in
the query to make sure that they are correct for this form. Check the query
for the correct fields to suit this form and not the previous form.


Put some more debug.print statements earlier in the code like this-->
and once again uncomment the line Debug.Print strWhere
Check that strWhere has the correct values for Review date as selected by
the user in txtStartDate and txtEndDate.


Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
Debug.Print Me.txtStartDate
strWhere = strWhere & "([Review Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
Debug.Print Me.txtEndDate
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
------------------------------------------------


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi davethewelder,
when you say it doesn't work, do you mean that nothing happens after you
hit the search button?
Does the form show any records when it first opens?
Have you uncommented the line-->
'Debug.Print strWhere
and checked what access shows for strWhere.
Does the code compile?
Have you stepped through the filter code to check that each criteria
string
is valid and returns records?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


message

Hi, I have a development dbase which I have designed the forms to
search
for
the no of Assessments completed and the number of Reviews completed.
In
this
dbase the Assessment table has 524 rows and the review table has 524
rows.

I have used the same calendar to populate the unbound textboxes e.g
Allen
Browne's, and the same code for the filter in the On Click Event.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates
in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Review Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate
+
1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

The search on the Assessment form works but the Review form does not.
On the Calendar selection button the On Click code is
"=CalendarFor([txtStartDate],"Set the start date")".

I cannot see any difference in the code except the field name.
Can anyone throw any light on this?

Any help appreciated.

Davie

Hi Jeanette, thanks for replying.

When i set the filter it returns a random number of records. When it
originally opens it returns the correct number of records in the table.
I have uncommented the line you suggested and the immediate window returns
the line below. The number of records and the dates do not match the
selection.

([Review Date] >= #02/01/2009#) AND ([Review Date] < #02/09/2009#)

Stepping through the code the the "strWhere " dimension reads "strWhere =
([Review Date] >#04/05/2009#) AND ([Review Date] <#04/13/2009#...
The [Review Date] does not show a value in the tooltip box, I don't know
if
it should.

Hope this is clear enough.

Thanks again for replying.

Davie

 

[davethewelder]

Jeanette, I was following your advice and was checking the properties of the
form when it occurred to me that I had not checked the design of the table.
I am embarrassed to say the Review Date field was a text field. After
changing this to a date field it worked perfectly.

Thanks for taking the time to reply. The help from the MVP community is
invaluable.

Davie.

PS. Melbourne is a lovely City. It is very much like Glasgow.

A couple of things to try.
Open the form in design view and clear any filters or order by text in the
form's property sheet.

Are you using A2007.
If yes, set the Filter on load property to no to see if this makes a
difference.

Check the query for the form's recordsource carefully to see if there is any
unintentional filtering happening there - check any criteria that may be in
the query to make sure that they are correct for this form. Check the query
for the correct fields to suit this form and not the previous form.


Put some more debug.print statements earlier in the code like this-->
and once again uncomment the line Debug.Print strWhere
Check that strWhere has the correct values for Review date as selected by
the user in txtStartDate and txtEndDate.


Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
Debug.Print Me.txtStartDate
strWhere = strWhere & "([Review Date] >= " & Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
Debug.Print Me.txtEndDate
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate +
1, conJetDate) & ") AND "
End If

'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's Filter.
'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub
------------------------------------------------


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi davethewelder,
when you say it doesn't work, do you mean that nothing happens after you
hit the search button?
Does the form show any records when it first opens?
Have you uncommented the line-->
'Debug.Print strWhere
and checked what access shows for strWhere.
Does the code compile?
Have you stepped through the filter code to check that each criteria
string
is valid and returns records?


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


message
Hi, I have a development dbase which I have designed the forms to
search
for
the no of Assessments completed and the number of Reviews completed.
In
this
dbase the Assessment table has 524 rows and the review table has 524
rows.

I have used the same calendar to populate the unbound textboxes e.g
Allen
Browne's, and the same code for the filter in the On Click Event.

Private Sub cmdFilter_Click()
'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can
easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards;
_
End date only = all dates up to (and including
this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string
to append to.
'Const conJetDate = "#mm/dd/yyyy#" 'The format expected for dates
in a
JET query string.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates
in a JET query string.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Review Date] >= " &
Format(Me.txtStartDate,
conJetDate) & ") AND "
End If

'Another date field example. Use "less than the next day" since this
field has times as well as dates.
If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Review Date] < " & Format(Me.txtEndDate
+
1, conJetDate) & ") AND "
End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ") to
remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the "
AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints
to
Immediate Window (Ctrl+G).
'Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

The search on the Assessment form works but the Review form does not.
On the Calendar selection button the On Click code is
"=CalendarFor([txtStartDate],"Set the start date")".

I cannot see any difference in the code except the field name.
Can anyone throw any light on this?

Any help appreciated.

Davie



Hi Jeanette, thanks for replying.

When i set the filter it returns a random number of records. When it
originally opens it returns the correct number of records in the table.
I have uncommented the line you suggested and the immediate window returns
the line below. The number of records and the dates do not match the
selection.

([Review Date] >= #02/01/2009#) AND ([Review Date] < #02/09/2009#)

Stepping through the code the the "strWhere " dimension reads "strWhere =
([Review Date] >#04/05/2009#) AND ([Review Date] <#04/13/2009#...
The [Review Date] does not show a value in the tooltip box, I don't know
if
it should.

Hope this is clear enough.

Thanks again for replying.

Davie