Multiple Search Command criteria in a form


V

vaa571

Hi there,

I created a form with a search command that searchs my database "Open Order
Table" for a part number and I would like to add another search criteria to
the code below...
Open Orders Table has "Part Number" and "Lot#" field and i would like to be
able to search by the lot# or by the Part Number. If searching by lot# it
woud had to be exact match but in the part number I would like to avoid
typing the entiry entry.
the code below I have to enter the entire part number...

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Open Orders"

stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub
 
Ad

Advertisements

Ad

Advertisements

J

John W. Vinson

Hi there,

I created a form with a search command that searchs my database "Open Order
Table" for a part number and I would like to add another search criteria to
the code below...
Open Orders Table has "Part Number" and "Lot#" field and i would like to be
able to search by the lot# or by the Part Number. If searching by lot# it
woud had to be exact match but in the part number I would like to avoid
typing the entiry entry.
the code below I have to enter the entire part number...

Private Sub Command42_Click()
On Error GoTo Err_Command42_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Open Orders"

stLinkCriteria = "[PART NUMBER]=" & "'" & Me![PART NUMBER] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_Command42_Click:
Exit Sub

Err_Command42_Click:
MsgBox Err.Description
Resume Exit_Command42_Click

End Sub

To search by just the beginning of the part number use the LIKE operator with
an * wildcard:

stLinkCriteria = "[Part Number] LIKE """ & Me![Part Number] & "*"""

It's not clear what you want done with the lot number: do you want separate,
independent searches by the two fields, or to search on the part and lot
numbers together, or let Access determine whether the user has entered a
criteirion in one or the other control and run different searches on that
basis?
 

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

Top