UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> User Entry Wildcard In A Query?, Access 2013    
 
   
ServiceMSA
post Aug 15 2019, 10:05 AM
Post#1



Posts: 7
Joined: 11-July 19



Hello,

Is it possible for a user to enter a wildcard into a query prompt?
I know it is possible to create a wildcard query however I may have scenarios where this could be a disadvantage.
As a hypothetical example say I am trying to hone in on a subset of records based on a model for a component "3820-S", however there are also components "3820-SA" and "3820-SB".
If I make a wildcard query and type in "3800-S" I would receive a list of records containing all 3 component models, this may be something I want to do at times but not in this particular scenario.
So is there a way I can, as a user, enter a wildcard so I can do both a search for "3820-S" or "3820-S*" if I need to?
Every time I try an error occurs "This expression is typed incorrectly, or is too complex to be evaluated."
Currently I will be trying to use an if(instr) in the criteria of my query to detect if a wildcard is entered and react accordingly, but I don't know if that is the right way to go.

Thank you in advance.
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 10:13 AM
Post#2


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


Welcome to UtterAccess.

Typically, seasoned Access developers tend NOT to use criteria in a query. Rather they use either tempvars as criteria, or they use a control on a form and set the criteria to reference that control.

I think either option would be more likely to work well for you here. Would you consider that?



--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 10:16 AM
Post#3


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


To address your question about sometimes wanting to include a wildcard and sometimes not, that'll need to be done via VBA, most likely. The problem is that people can resolve ambiguity better (this time I DO want to use a wildcard) and Access needs pretty specific instructions. In other words, if you want to change back and forth, you'll need to make that an explicit user-controlled choice.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
ServiceMSA
post Aug 15 2019, 10:30 AM
Post#4



Posts: 7
Joined: 11-July 19



Thank you for your reply.

Yes I would be willing to try that.
I am new to Access and this is my first database so I am learning as I go.

Basically part of my database is a parts list containing data such as price, manufacturer, model, etc.
I was planning to use a form in the end anyway and have a series of text boxes in the form, one for each field in the query that a user can use to search for parts info.
It seems like it would be pretty standard, I just have an issue with the wildcards.
I have had queries reference controls on forms before but wouldn't I have the same issue if I entered 3820-S* into the form as I am having now with the query?
Go to the top of the page
 
ServiceMSA
post Aug 15 2019, 10:34 AM
Post#5



Posts: 7
Joined: 11-July 19



I thought it might come to VBA to search for the wildcards and then actually write a string for the criteria into a tempvar but I was hoping to avoid the trouble.

Every database I have ever worked with outside Access has the feature on the user end to use a Wildcard.
I don't know how complicated it was to do this on the back end but it just seemed like something I might have been overthinking.
Go to the top of the page
 
GroverParkGeorge
post Aug 15 2019, 12:06 PM
Post#6


UA Admin
Posts: 35,514
Joined: 20-June 02
From: Newcastle, WA


It's not that hard, but your request was to make the query criteria work with or without a wild card, if I read it correctly. So, to me, that does mean a bit more complexity. Your example seems to suggest that sometimes you'd want to see ONLY an exact match and sometimes you'd want to see the exact match plus partial matches. The latter is not hard, but the "optional" part of the requirement is where things get sticky.

I have a demo on my website that illustrates some of what you're looking for, but it may be more complex than you need. Take a look.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
theDBguy
post Aug 15 2019, 12:59 PM
Post#7


Access Wiki and Forums Moderator
Posts: 76,028
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UA! welcome2UA.gif

QUOTE
...but wouldn't I have the same issue if I entered 3820-S* into the form as I am having now with the query?

Actually, I don't think I understand the issue. Can you post the SQL statement for your query (and maybe some screenshots)? I just tried it, as how I understood the problem, and didn't have any issues. I get exact matches when I wanted exact matches and got back multiple records when I wanted to use wildcards.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ADezii
post Aug 15 2019, 01:08 PM
Post#8



Posts: 2,539
Joined: 4-February 07
From: USA, Florida, Delray Beach


QUOTE
To the best of my knowledge, you cannot enter an Optional Wildcard Criteria directly into the Query Grid, but all is not lost. Let's assume that your Table is named tblTest and it consists of a [Component] Field.

  1. Create a simple Query that selects all Records from tblTest including the [Component Field].
  2. Copy-N-Paste the following Code into the Click() Event of a Command Button:
    CODE
    Dim intResponse As Integer
    Dim strCriteria As String
    Dim qdf As DAO.QueryDef

    strCriteria = InputBox("Enter a Component to search for", "Component Search")

    If strCriteria = "" Then Exit Sub

    Set qdf = CurrentDb.QueryDefs("qryComponents")

    intResponse = MsgBox("Use a Wildcard for Component Search?", vbQuestion + vbDefaultButton2 _
                          + vbYesNo, "Wildcard Search")

    If intResponse = vbYes Then
      qdf.SQL = "SELECT * FROM tblTest WHERE [Component] Like '" & strCriteria & "*';"
    Else
      qdf.SQL = "SELECT * FROM tblTest WHERE [Component] = '" & strCriteria & "';"
    End If

    DoCmd.OpenQuery "qryComponents", acViewNormal, acReadOnly
  3. Once you click on the Command Button, the following will occur:
    1. You will get a Prompt, in your scenario you would enter 3820-S.
    2. You will receive another Prompt asking if you wish to perform a Wildcard Search (3820-S*). Take note that No is selected by Default.
    3. The SQL of the Query (qryComponents) will dynamically be modified depending on whether or not you chose to use a Wildcard (*).
    4. The Query (qryComponents) will then be opened in Read Only Mode and will display the appropriate Results.
  4. I have uploaded a Graphic of the two Prompts and they can be viewed below.
  5. The Code has been tested and is fully operational.

P.S. - You can also check and see if qryComponents returns any Records after the twp Prompts have been dealt with. If no Records are returned, then notify the User and do not Open the Query.
This post has been edited by ADezii: Aug 15 2019, 01:15 PM
Attached File(s)
Attached File  Prompt1.JPG ( 7.55K )Number of downloads: 0
Attached File  Prompt2.JPG ( 6.96K )Number of downloads: 0
 
Go to the top of the page
 
ServiceMSA
post Aug 15 2019, 02:01 PM
Post#9



Posts: 7
Joined: 11-July 19



Hello and thank you for the welcome.

I agree that you can get exact records but only for the longer models.
In the case I stated you couldn't get exact records when looking for 3820-S.
I cannot post my database exactly since it has work related materials in it however I threw together a sample of a database demonstrating what I mean.
There is a table called PartsList containing three fields Model, Description and Cost.
If I have a wildcard query base on that table and type in 3820-S I get all three components listed in the example.

The query in question is coded as such:

CODE
SELECT PartsList.Model, PartsList.Description, PartsList.Cost
FROM PartsList
WHERE (((PartsList.Model) Like "*" & [Enter Model] & "*"));

Go to the top of the page
 
theDBguy
post Aug 15 2019, 02:04 PM
Post#10


Access Wiki and Forums Moderator
Posts: 76,028
Joined: 19-June 07
From: SunnySandyEggo


Hi. Thanks for trying to clarify the issue. I don't see any database file attached to your post above. Also, assuming the query you just posted works for one of your requirements, I still don't understand what the other requirement was. For example, with this query, if a user enters "3820-S" in the prompt, three records are returned. What was it you wanted to happen at other times when the user enters the same value in the prompt?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
ServiceMSA
post Aug 15 2019, 02:36 PM
Post#11



Posts: 7
Joined: 11-July 19



That is more like what I was looking for although it would be nice to not have to do it through a button.
However now that I see the VBA for Access isn't too bad I think I can tweak that to just check a string for a wildcard and then react to the user input and skip the second prompt.
Thank you very much for your response.
Go to the top of the page
 
ServiceMSA
post Aug 15 2019, 02:51 PM
Post#12



Posts: 7
Joined: 11-July 19



Here is the database.
If you use the testquery_userentry it will ask for you for a model, if you put in just 3820-S it will bring up all three 3820-S, 3820-SA, and 3820-SB.
It doesn't just return 3820-S like I want, what I was asking was how to do both a wildcard query and a non wildcard query.
So I want to be able to type in something like "3820-S" and get one entry returned as well as be able to enter "3820-S*" and get all three entries.
If you look at ADezii's response he has a solution that works, if you open the form in the database it has his solution built into it.
Attached File(s)
Attached File  UADatabase.zip ( 29.34K )Number of downloads: 2
 
Go to the top of the page
 
RJD
post Aug 15 2019, 02:59 PM
Post#13


UtterAccess VIP
Posts: 9,917
Joined: 25-October 10
From: Gulf South USA


Hi: PMFJI, but maybe I am missing something. You might try this instead ...

CODE
SELECT PartsList.Model, PartsList.Description, PartsList.Cost
FROM PartsList
WHERE PartsList.Model Like [Enter Model];

(removes the "*" parts from the WHERE clause)

... then enter 3820-S for just that code alone, or 3820-S* for everything starting with 3820-S, or *20-S* for everything containing 20-S regardless of the start or finish of the record. Like without the * equates to =.

I tested this approach (see attached) and it seems to work. Try entering the variations above.

Let me know if this is what you are trying to do. As I said, perhaps I am missing something...

HTH
Joe
Attached File(s)
Attached File  UserEntryWildcard.zip ( 18.86K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
theDBguy
post Aug 15 2019, 03:08 PM
Post#14


Access Wiki and Forums Moderator
Posts: 76,028
Joined: 19-June 07
From: SunnySandyEggo


Hi.
QUOTE
Here is the database.
...
So I want to be able to type in something like "3820-S" and get one entry returned as well as be able to enter "3820-S*" and get all three entries.
...
Thank you for the additional information. This was the part I was missing earlier. And as Joe just posted, that's exactly what I tried earlier when I said it worked for me. All I did was use the
CODE
Like [Enter Part]
and simply entered "3820-S" the first time and then entered "3820-S*" the second time. No code needed.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
RJD
post Aug 15 2019, 03:09 PM
Post#15


UtterAccess VIP
Posts: 9,917
Joined: 25-October 10
From: Gulf South USA


...and here is the revision to your db ... using the method I (and theDBguy) indicated. Just enter 3820-S and see the result, then 3820-S*, etc...

HTH
Joe
Attached File(s)
Attached File  UADatabase_Rev1.zip ( 21.7K )Number of downloads: 2
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
ServiceMSA
post Aug 16 2019, 08:56 AM
Post#16



Posts: 7
Joined: 11-July 19



Thank you very much that does seem to work.
Only I am having an issue with it only working when opening it the first time.
I copied your code into my query and it works when first opening the query, but if I try to refresh the query it doesn't work twice.
If you type in 3820-S into the query and then hit refresh all and enter 3820-S* do you still get all three entries?
Because I do not, this won't be much of an issue since I can close and rerun the query when using the form but I just found it interesting.
Go to the top of the page
 
RJD
post Aug 16 2019, 10:04 AM
Post#17


UtterAccess VIP
Posts: 9,917
Joined: 25-October 10
From: Gulf South USA


Hi: Opening a query directly for viewing/editing is not a usual procedure in a production database. That's the function of forms. So, your form should be revised to display the records you want, and a search textbox added to enter the search value. You can use the * there as well.

Access has a "quirk" that it does not like to easily release the previous search parameter/filter. So I have added a restatement of the record source in the form to "encourage" Access to release the internal filter and start again when you ask for a new search.

In the revision attached, you will see the form open automatically. Enter the search value (with a * at the end if that is desired) and hit your Enter key. That should do what you want. You can clear the search value with the Reset button, and then enter another search - or simply revise the search value and hit Enter again.

See the simple code behind the search textbox (After Update) and the Reset button (On Click).

See if this is more to your liking.

HTH
Joe
Attached File(s)
Attached File  UADatabase_Rev2.zip ( 26.86K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 02:20 AM