Form filter- numbers rather than text

S

sooz9

Hello. I followed Allen Browne 2006 example on filtering a form using
criteria and it works great with one problem.

For my critiera I have the user select from a drop down list (4 different
ones) with the exception of a date range. When I filter by any of my critera
using the drop down list the information is filtered correctly. However...

The problem is that the filtered infomation shows up as the correspoding
"autonumber" to the drop down word that was selected. For example, the Shift
selected is "Day", the filter shows "1" ....the autonumber for Day. If Night
is selected, it shows a "2"

I attached the code below:

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.

'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.
'***********************************************************************

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterRunType) Then
strWhere = strWhere & "([RunType] = " & Me.cboFilterRunType & ") AND "
End If

If Not IsNull(Me.cboFilterBLPrimaryReason) Then
strWhere = strWhere & "([BLPrimaryReason] = " &
Me.cboFilterBLPrimaryReason & ") AND "
End If

If Not IsNull(Me.cboFilterSecondaryReason) Then
strWhere = strWhere & "([SecondaryReason] = " &
Me.cboFilterSecondaryReason & ") AND "
End If

If Not IsNull(Me.cboFilterShift) Then
strWhere = strWhere & "([Shift] = " & Me.cboFilterShift & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & 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 & "([EnteredOn] < " & 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
 
J

Jeanette Cunningham

Hi sooz9,
the secret to this is to put the field for Shift in the query that is the
recordsource of the form as well as the ShiftID.
You may have the shift in a separate table. Just add it to the query for the
form's recordsource.
Add a control for Shift to your form.
When user selects Day shift or night shift, the 1 or 2 is used to filter the
form, but the word Day or Night will show on your form instead of 1 or 2.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sooz9 said:
Hello. I followed Allen Browne 2006 example on filtering a form using
criteria and it works great with one problem.

For my critiera I have the user select from a drop down list (4 different
ones) with the exception of a date range. When I filter by any of my
critera
using the drop down list the information is filtered correctly. However...

The problem is that the filtered infomation shows up as the correspoding
"autonumber" to the drop down word that was selected. For example, the
Shift
selected is "Day", the filter shows "1" ....the autonumber for Day. If
Night
is selected, it shows a "2"

I attached the code below:

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.


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterRunType) Then
strWhere = strWhere & "([RunType] = " & Me.cboFilterRunType & ")
AND "
End If

If Not IsNull(Me.cboFilterBLPrimaryReason) Then
strWhere = strWhere & "([BLPrimaryReason] = " &
Me.cboFilterBLPrimaryReason & ") AND "
End If

If Not IsNull(Me.cboFilterSecondaryReason) Then
strWhere = strWhere & "([SecondaryReason] = " &
Me.cboFilterSecondaryReason & ") AND "
End If

If Not IsNull(Me.cboFilterShift) Then
strWhere = strWhere & "([Shift] = " & Me.cboFilterShift & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & 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 & "([EnteredOn] < " & 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
Click to expand...
 
S

sooz9

Thank you. I won't have a chance to try this until Friday night PST. So
please look to see if I repy with a another question. Have a good Valentines
Day :)

Jeanette Cunningham said:
Hi sooz9,
the secret to this is to put the field for Shift in the query that is the
recordsource of the form as well as the ShiftID.
You may have the shift in a separate table. Just add it to the query for the
form's recordsource.
Add a control for Shift to your form.
When user selects Day shift or night shift, the 1 or 2 is used to filter the
form, but the word Day or Night will show on your form instead of 1 or 2.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


sooz9 said:
Hello. I followed Allen Browne 2006 example on filtering a form using
criteria and it works great with one problem.

For my critiera I have the user select from a drop down list (4 different
ones) with the exception of a date range. When I filter by any of my
critera
using the drop down list the information is filtered correctly. However...

The problem is that the filtered infomation shows up as the correspoding
"autonumber" to the drop down word that was selected. For example, the
Shift
selected is "Day", the filter shows "1" ....the autonumber for Day. If
Night
is selected, it shows a "2"

I attached the code below:

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.


'***********************************************************************
'Look at each search box, and build up the criteria string from the
non-blank ones.

'***********************************************************************

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboFilterRunType) Then
strWhere = strWhere & "([RunType] = " & Me.cboFilterRunType & ")
AND "
End If

If Not IsNull(Me.cboFilterBLPrimaryReason) Then
strWhere = strWhere & "([BLPrimaryReason] = " &
Me.cboFilterBLPrimaryReason & ") AND "
End If

If Not IsNull(Me.cboFilterSecondaryReason) Then
strWhere = strWhere & "([SecondaryReason] = " &
Me.cboFilterSecondaryReason & ") AND "
End If

If Not IsNull(Me.cboFilterShift) Then
strWhere = strWhere & "([Shift] = " & Me.cboFilterShift & ") AND "
End If

'Date field example. Use the format string to add the # delimiters and
get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([EnteredOn] >= " & 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 & "([EnteredOn] < " & 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
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Multiselect List Box 4
MultiList Select Box Search Form 7
Search working in one form but not another 4
Sorting Allen Browne's Search Form 2
Type mismatch 2
Update All Using a Command Button 3
Help with a Query running off of VBA 4
How to Search Multiple Values in a Filter 5

Top