Enter one or many criteria

[JasonP]

I have a query that runs the criteria [Enter Part Number] for the part number
field which allows me to pull all of the records for whichever part number I
enter. Is there a way to set this up so that if I want to search more than
one part number? The query is activated from a command button on a form
which brings up the search box to "Enter Part Number." For example, I can
search for just P/N 400 or just P/N 500, but sometimes I need to see both
queries in the same table. I would like to be able to enter some variation
of "400 or 500" in the same search box to get this result. Suggestions?

 

[Dirk Goldgar]

I have a query that runs the criteria [Enter Part Number] for the part
number
field which allows me to pull all of the records for whichever part number
I
enter. Is there a way to set this up so that if I want to search more
than
one part number? The query is activated from a command button on a form
which brings up the search box to "Enter Part Number." For example, I can
search for just P/N 400 or just P/N 500, but sometimes I need to see both
queries in the same table. I would like to be able to enter some
variation
of "400 or 500" in the same search box to get this result. Suggestions?

I would normally not open a query as a datasheet, but rather open a form --
maybe a datasheet form -- that displays the data. With that arrangement, I
could have the command button prompt the user for the criteria, then apply
them as a filter string when I open the form. Something like this:

'---- start of example code -----
Private Sub cmdShowParts_Click()

On Error GoTo Err_Handler

Dim strCriteria As String

Prompt:
strCriteria = InputBox("What part(s)?")

If Len(strCriteria) > 0 Then
strCriteria = Application.BuildCriteria("PartNumber", dbLong,
strCriteria)
End If

DoCmd.OpenForm "frmPartsList", WhereCondition:=strCriteria

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2432 Then
If MsgBox( _
"Sorry, I didn't understand that. Please enter part numbers
" & _
"in one of these formats:" & vbCr & vbCr & _
" ###" & vbCr & _
" ### or ### {...}" & vbCr & _
" In (###, ###, ### {, ...})" & vbCr & _
vbCr & "or just press enter for all.", _
vbExclamation + vbOKCancel, _
"Not Understood") _
= vbOK _
Then
Resume Prompt
Else
Resume Exit_Point
End If
End If
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'---- end of example code -----

Of course, this could get a lot fancier.

 

[KARL DEWEY]

One way is to add a field in the query design view grid like this --
Expr1: [Enter P/Ns separated by spaces]

In the criteria row put this --
Like "*" & [Your_PN_Field] & "*"

 

[JasonP]

I feel like an idiot, but I am not familiar with code. I try to stick with
the easy pre-set options in the query design view or macros and expressions.
I tried to implement that code, but I don't know what needs to be replaced
and it keeps giving me errors. Also, I am not sure how to set up the
datasheet form you mentioned. I have it come up as a datasheet since there
are typically several records associated with one part number.

This database is a call record for tech services calls. I need to be able
to track what kinds of calls we are getting for each part number. Sometimes
two part numbers are associated and I need to view all of those records. I'm
usually looking for something specific, a trend, so I need to be able to scan
through them. I'm not sure a form view would be the best option.

I have a query that runs the criteria [Enter Part Number] for the part
number
field which allows me to pull all of the records for whichever part number
I
enter. Is there a way to set this up so that if I want to search more
than
one part number? The query is activated from a command button on a form
which brings up the search box to "Enter Part Number." For example, I can
search for just P/N 400 or just P/N 500, but sometimes I need to see both
queries in the same table. I would like to be able to enter some
variation
of "400 or 500" in the same search box to get this result. Suggestions?

I would normally not open a query as a datasheet, but rather open a form --
maybe a datasheet form -- that displays the data. With that arrangement, I
could have the command button prompt the user for the criteria, then apply
them as a filter string when I open the form. Something like this:

'---- start of example code -----
Private Sub cmdShowParts_Click()

On Error GoTo Err_Handler

Dim strCriteria As String

Prompt:
strCriteria = InputBox("What part(s)?")

If Len(strCriteria) > 0 Then
strCriteria = Application.BuildCriteria("PartNumber", dbLong,
strCriteria)
End If

DoCmd.OpenForm "frmPartsList", WhereCondition:=strCriteria

Exit_Point:
Exit Sub

Err_Handler:
If Err.Number = 2432 Then
If MsgBox( _
"Sorry, I didn't understand that. Please enter part numbers
" & _
"in one of these formats:" & vbCr & vbCr & _
" ###" & vbCr & _
" ### or ### {...}" & vbCr & _
" In (###, ###, ### {, ...})" & vbCr & _
vbCr & "or just press enter for all.", _
vbExclamation + vbOKCancel, _
"Not Understood") _
= vbOK _
Then
Resume Prompt
Else
Resume Exit_Point
End If
End If
MsgBox Err.Description, vbExclamation, "Error " & Err.Number
Resume Exit_Point

End Sub
'---- end of example code -----

Of course, this could get a lot fancier.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[JasonP]

That worked great with one exception. I did a test query for P/N's DS-340
and 80. It came back with both part numbers, but also gave me records with
P/N 40 and some that had no part number entered. Any ideas?

One way is to add a field in the query design view grid like this --
Expr1: [Enter P/Ns separated by spaces]

In the criteria row put this --
Like "*" & [Your_PN_Field] & "*"

--
KARL DEWEY
Build a little - Test a little

I have a query that runs the criteria [Enter Part Number] for the part number
field which allows me to pull all of the records for whichever part number I
enter. Is there a way to set this up so that if I want to search more than
one part number? The query is activated from a command button on a form
which brings up the search box to "Enter Part Number." For example, I can
search for just P/N 400 or just P/N 500, but sometimes I need to see both
queries in the same table. I would like to be able to enter some variation
of "400 or 500" in the same search box to get this result. Suggestions?

 

[KARL DEWEY]

What I gave was quick and dirty.

Somewhere in my archives I have an old database where the developer build a
search form that in turn appended records to a scratch table that was used as
criteria. It kept appending records as long as data was input. When ENTER
was pressed without any data then it ran the query.

--
KARL DEWEY
Build a little - Test a little

That worked great with one exception. I did a test query for P/N's DS-340
and 80. It came back with both part numbers, but also gave me records with
P/N 40 and some that had no part number entered. Any ideas?

One way is to add a field in the query design view grid like this --
Expr1: [Enter P/Ns separated by spaces]

In the criteria row put this --
Like "*" & [Your_PN_Field] & "*"

--
KARL DEWEY
Build a little - Test a little

I have a query that runs the criteria [Enter Part Number] for the part number
field which allows me to pull all of the records for whichever part number I
enter. Is there a way to set this up so that if I want to search more than
one part number? The query is activated from a command button on a form
which brings up the search box to "Enter Part Number." For example, I can
search for just P/N 400 or just P/N 500, but sometimes I need to see both
queries in the same table. I would like to be able to enter some variation
of "400 or 500" in the same search box to get this result. Suggestions?