Query "Parameter" issue I think!!

[bacalaoyalcapurria]

Hello, this is the problem I'm having;
I have a form that has a command button on it. When the command button is clicked, a
parameter box pops up asking for a whole or partial number. I created a
query using to tables. In one table I selected a field called Document_ID
along with other fields to be on the query and in the other table I selected
Referenced_ID and Reference_Type. The
Document_ID field, column contains just numbers. One Document ID has many other numbers which are (Reference_ID)
I set the parameter criteria at query design view section under the Document_ID column. Then I created a report using the query. When I go to the form
and click on the command button, it prompts me for a number or any number, I enter a whole
number or just a partial number, then it works good. It opens up the report and get the results
The whole point of doing this is that if a user don't know the whole number of a document, he/she can enter the first, second number, etc, then Access gives him/her a list of numbers starting with the first or second digit. But it 's only looking in one column. I'd like to be able to search in the
Document ID column and Reference_ID column at the same time. Remember those two fields are in separate tables. Can Access do something like this?

Here is an example of what trying to do

tblDocuments
Document_ID
300.00
200.00
450.00
320.00
360.00

tblReference
Reference_ID
3030000
3120000
3010000
2340980
4170000

For example, If I don't know the whole number to a document or reference, I'd like to be able to type in the number 30 and get the following results;

300.00 Document Type
3030000 Reference Type
3010000 Reference Type

or type in the number 3 and have Access give me the following results;

300.00 Document Type
320.00 Document Type
360.00 Document Type
3030000 Reference Type
3120000 Reference Type
3010000 Reference Type




I'd appreciate any help you can give me.

Rita

 

[dhookom]

You should consider upgrading your application a bit. You can place a text box (or combo box) on your form to allow the user to enter the "parameter" value. Then use code like and remove the parameter from the query.

Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtSearchID) Then
    strWhere = strWhere & _
    " AND Document_ID & '~' & Reference_ID Like '*" & _
        Me.txtSearchID & "*'"
End If 
DoCmd.OpenReport "rptDoc", acPreview, , strWhere

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bacalaoyalcapurria]

Thanks for your quick response, but I'm still not getting the appropriate results.
I created a form, added a text box, and a command button. I also created a query using two tables. I selected the fields tblDocument.Document_Id and tblReference.Reference_Document_Id.
On the command click event of the button, I added this code,

Private Sub Command15_Click()
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.txtSearchID) Then
strWhere = strWhere & _
" AND Document_Id & '~' & Referenced_Document_Id Like '*" & _
Me.txtSearchID & "*'*"
End If
DoCmd.OpenReport "rptmama", acPreview, , strWhere

End Sub

When I go to the form and enter a number on the text box, let say number 33, I get this result

Document_Id Referenced_Document_Id
000-525-015
335.18
334.044(14)
334.044(17)
Rule 14-96
Rule 14-97
334.044(2)
000-525-025
23CFR450.216
23CFR450.202
339.135(2b4)
163.3204
339.135(4b4)
339.135(4c1)
339.175
23CFR450.208
23CFR450.224
23CFR450.210

That result is wrong. Again I'd like for Access to give me all the numbers that start with 33 in each of the two tables.
Do you think because those tables are joined by Document_ID as a one to many, Im not getting the results?

Again, I appreciate your help.

Thanks

Rita

 

[bacalaoyalcapurria]

I'm sorry this is the code I used on the command click function

Private Sub Command15_Click()
Dim strWhere As String
strWhere = "1=1 "
If Not IsNull(Me.txtSearchID) Then
strWhere = strWhere & _
" AND Document_Id & '~' & Referenced_Document_Id Like '*" & _
Me.txtSearchID & "*'"

End If
DoCmd.OpenReport "rptmama", acPreview, , strWhere

End Sub

 

[bacalaoyalcapurria]

One think I forgot to mention is that when I type any number into the text box, let say 55 or just 5, I want Access to look at the beginning of every number that is on those two fields and give back the numbers that start with 55 like

Document_ID Referenced_Document_ID
556289
5569.23

551025
5587.00

552365
5800.00


Right now is looking at every place for 55 and is giving back the following

336559
005532
154855


Thanks and sorry for the confusion.

Rita

 

[PHV]

something like this ?
" AND (Document_ID Like '" & Me!txtSearchID & "*'" _
& " OR Reference_ID Like '" & Me!txtSearchID & "*')"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886