Solved

Values from a text box as query criteria

Posted on 2001-06-18
33
258 Views
Last Modified: 2008-02-01
I have a form. It's a lovely form. On this for I have a text box. I want to use the value in this text box in the criteria section of a query. The value in the box looks something like this:

"OUT_MACHINE = 'Machine1'" Or "OUT_MACHINE = 'Machine2'"

When I paste this explicitly, right into the criteria box, it works just dandy. When I refer to the text box on the form, i.e.:

[Forms]![frmSelector_main]![FILTER_STRING2]

It goes kaflooie. It seems that when access reads it explicitly, it reads it as "Value 1" or "Value2", but when it reads it from the text box, it reads it all together as a string, like so:

" "OUT_MACHINE = 'Machine1'"Or"OUT_MACHINE = 'Machine2'" "

so the value will never match correctly to this string and the Or clause is not recognized as a clause at all but as part of a sentence for all intents and purposes. How do I get around this? It's driving me buggy!
0
Comment
Question by:niner
  • 10
  • 10
  • 4
  • +6
33 Comments
 
LVL 6

Expert Comment

by:PsychoDazey
Comment Utility
You should really write the code in SQL yourself, like this:
"SELECT * FROM yourtable WHERE yourtable.[yourfield] = " &
Me![FILTER_STRING2]

If you have to use the query builder, then what is your field set as, is it a text field?  If you have it set up as a non-text field that is most likely your problem.  If you need more help on the SQL statement, send me the field & table names and I will get a little more in detail.
0
 
LVL 5

Expert Comment

by:cekman
Comment Utility
I haven't tested this - but try changing the double quotes in your text string to single quotes before use the string in your form reference.
0
 
LVL 4

Expert Comment

by:srauda
Comment Utility
Try this:

OUT_MACHINE = "Machine1" Or OUT_MACHINE = "Machine2"
0
 
LVL 4

Expert Comment

by:srauda
Comment Utility
Ooops.  It has been a while since I have responded.  Disregard the last message.
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
basically, you want the user to be able to not only enter in the criteria, but also which field it applies to.  This is a great way to empower your users, as long as they are aware of how to use SQL.  Basically, you're either going to have to build the SQL criteria section dynamically with code and then either open a querydef object and parse out and set the criteria section of the SQL property of that querydef, or you're going to have to open up a recordset based on a complete SQL statement using the criteria you built with the code.  It all depends on what purpose of the query.  If it for the recordsource for a form or report, you might want to consider using the criteria string as a filter or for setting the RecordSource property of the form or report.  If it is an action query, you might want to use the SQL string in a CurrentDB.Execute call.  Can you describe the purpose of the query?

cjswimmer
0
 
LVL 1

Expert Comment

by:randycarpet
Comment Utility
I have just written a suggested solution to Prowlers problem which may also suit yours with some changes.

It takes the string value of a textbox, separates the string into words which it then converts into an SQL query.

Here is the module (it's very well documented so should be straight forward to follow).  Note that it's looking for a textbox called "txtQ":

'=======================
'Finds words in a string
'=======================
Private Sub FindWords()
  Dim strQuestionText As String     'Text input as criteria by user
  Dim intQuestionLength As Integer  'Character length of string
  Dim intSpaces As Integer          'Character number where space is found
  Dim intSpacePos As Integer        'Character number from where to start search
  Dim strFindChar As String         'Character to find in string (" ")
  Dim intWordStart As Integer       'Starting point (from which word is extracted)
  Dim blnFinishedSearch As Boolean  'Search is active = True or inactive = False
  Dim strWord As String             'Extracted word
  Dim intNumOfChars As Integer      'Number of characters to extract (from starting point)
  Dim strWordPhrased As String      'Extracted word, re-phrased for SQL
  Dim strQueryPart1 As String       'The Question Field part of SQL query
  Dim strQueryPart2 As String       'The Answer Field part of SQL query
  Dim strFinalQuery As String       'Complete SQL code to send
 
  intSpaces = 0                     'Set default value. 0 = first run
  strFindChar = " "                 'Character to find in string

  Do While blnFinishedSearch = False
  strQuestionText = Trim(txtQ)      'Clear leading & trailing spaces
    '=========================
    'IS THIS THE FIRST SEARCH?
    '=========================
    If intSpaces = 0 Then  'This is the first check for a spacebar entry
      intSpacePos = 1       'Start of search = beginning of the sentence
    Else
      intSpacePos = intSpaces  'Start of search = next word
    End If
   
    '==================
    'FIND THE CHARACTER
    '==================
    intSpaces = InStr(intSpacePos, strQuestionText, strFindChar)
 
    '==================================
    'VALIDATE IF A SPACE HAS BEEN FOUND
    '==================================
    'IF NONE FOUND (IntSpace = 0), THERE ARE 2 POSSIBILITIES:
    '1. THERE IS ONLY ONE WORD.
    '2. THERE IS MORE THAN ONE WORD BUT PROGRAM HAS REACHED END OF SEARCH.
   
    '====================
    'THERE IS ONLY 1 WORD
    '====================
    If intSpaces = 0 And intSpacePos = 1 Then
      strWord = Trim(txtQ)
      strWordPhrased = "'*" & strWord & "*'"                              'Prepare for SQL use
      strQueryPart1 = "((([Q&A].Question) Like " & strWordPhrased & "))"  'Question field SQL code
      strQueryPart2 = "((([Q&A].Answer) Like " & strWordPhrased & "))"    'Answer field SQL code
      strFinalQuery = strQueryPart1 & " OR " & strQueryPart2              'Complete the SQL code
      SearchTable strFinalQuery                                           'Send word to match
      blnFinishedSearch = True                                            'Job done.  Exit program/End Loop
    '=====================================================
    'THERE IS MORE THAN 1 WORD AND SEARCH NOT YET FINISHED
    '=====================================================
    ElseIf intSpaces > 0 Then
      '==========
      'CHECK WORD
      '==========
      intWordStart = intSpacePos                                  'Starting character number
      intNumOfChars = intSpaces - intWordStart                    'Number of characters to get
      strWord = Mid(strQuestionText, intWordStart, intNumOfChars) 'Word to extract
      '==========================================
      'LOOK FOR EXTRA SPACES AND IGNORE AS A WORD
      '==========================================
      If strWord = "" Then
        'IGNORE. NO WORD TO RECORD
      Else
        '=========================
        'CREATE/ADD WORD TO STRING
        '=========================
        strWordPhrased = "'*" & strWord & "*'"    'Re-format the extracted word, ready for SQL acceptance

        'How this bit works:
        'Below, is a copy of an SQL query that has 3 criteria.
        'From the comments on the right, you'll see that to accommodate lots of words
        'all I need to do is repeat the middle line (in each table) until I detect the
        'last word, at which point, I use the 3rd line (in each table) to close off the query.
        '********************
        'QUESTION TABLE FIRST
        '********************
        '"((([Q&A].Question) Like " & strCriteria & "     FIRST WORD ADDED
        'And ([Q&A].Question) Like " & strCriteria & "    EVERY SUBSEQUENT WORD, EXCEPT...
        'And ([Q&A].Question) Like " & strCriteria & "))  LAST WORD THAT HAS 2 BRACKETS TO END
        'OR
        '((([Q&A].Answer) Like & strCriteria & "          FIRST WORD ADDED
        'And ([Q&A].Answer) Like & strCriteria & "        EVERY SUBSEQUENT WORD, EXCEPT...
        'And ([Q&A].Answer) Like & strCriteria & "));     LAST WORD THAT HAS 2 BRACKETS, PLUS COLON.
        '******************
        'ANSWERS TABLE LAST
        '******************
       
        Select Case strQueryPart1
          Case ""   'FIRST WORD BEING ADDED
            strQueryPart1 = "((([Q&A].Question) Like " & strWordPhrased & " "
            strQueryPart2 = "((([Q&A].Answer) Like " & strWordPhrased & " "
          Case Else 'EVERY OTHER WORD, EXCEPT THE LAST
            strQueryPart1 = strQueryPart1 & "And ([Q&A].Question) Like " & strWordPhrased & " "
            strQueryPart2 = strQueryPart2 & "And ([Q&A].Answer) Like " & strWordPhrased & " "
        End Select
      End If
     
    '================================================
    'THERE IS MORE THAN 1 WORD AND SEARCH IS FINISHED
    '================================================
    ElseIf intSpaces = 0 And intSpacePos > 1 Then
      '=============
      'GET LAST WORD
      '=============
      intQuestionLength = Len(txtQ)                               'Number of characters in string
      intWordStart = intSpacePos                                  'Starting character number
      intNumOfChars = intQuestionLength - (intWordStart - 1)      'Number of characters to get (less 1)
      strWord = Mid(strQuestionText, intWordStart, intNumOfChars) 'Word to extract
      strWordPhrased = "'*" & strWord & "*'"                      'Prepare for SQL use
      '=============================
      'ADD FINAL STRING TO SQL QUERY
      '=============================
      strQueryPart1 = strQueryPart1 & "And ([Q&A].Question) Like " & strWordPhrased & "))"
      strQueryPart2 = strQueryPart2 & "And ([Q&A].Answer) Like " & strWordPhrased & "))"
      '=================================
      'SEARCH FINISHED, POPULATE LISTBOX
      '=================================
      blnFinishedSearch = True  'Search complete, end loop
      strFinalQuery = strQueryPart1 & " OR " & strQueryPart2
      SearchTable strFinalQuery
    End If
    '===============================
    'INCREMENT COUNTER FOR NEXT WORD
    '===============================
    intSpaces = intSpaces + 1
  Loop
End Sub
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi niner,

In [Forms]![frmSelector_main]![FILTER_STRING2], drop all the double-quates (").

[Forms]![frmSelector_main]![FILTER_STRING2] should be:
OUT_MACHINE = 'Machine1' Or OUT_MACHINE = 'Machine2'
instead of
"OUT_MACHINE = 'Machine1'" Or "OUT_MACHINE = 'Machine2'"

Now when you refere to [Forms]![frmSelector_main]![FILTER_STRING2], you'll get this result:
"OUT_MACHINE = 'Machine1' Or OUT_MACHINE = 'Machine2'"
which is what you need...

Hope this helps,

Nosterdamus
0
 

Expert Comment

by:RichardVBman
Comment Utility
Hi niner,

Another way round this one is to create two public sting variables in a module, and two public functions to return their values.
 set the values of the variables from the afterUpdate events of each text box on your form.
 In the criteria cell in your query write GetMachine1() Or GetMachine2().

public strMachine1 as string
public strMachine2 as string

Public Function GetstrMachine1() as string

 GetstrMachine1 = strMachine1

End Function

Public Function GetstrMachine2() as string

 GetstrMachine2 = strMachine2

End Function

hope this helps,
Rich.
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
any update niner?
0
 
LVL 6

Accepted Solution

by:
cjswimmer earned 0 total points
Comment Utility
niner?
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
are you sure you meant to accept my comment as an answer?  Please give a little detail as to the solution you ended up with.
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
?????
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
ok, why don't you guys tell me who should get the points and I'll post a question for you.
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Lets wait for a couple more days and see what niner has to say about this...

Nosterdamus
0
 
LVL 6

Expert Comment

by:PsychoDazey
Comment Utility
I think I should get the points as I am a neutral party.
Allright, just kidding.
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
Well I'm leaving tomorrow for two weeks to get married so if someone wants the points, you have about 3 hours after the timestamp of this post to ask me to give them to you, or else you can wait until I get back.  :-)
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
If I'm not mistaken, I believe that PsychoDazey is tying the knot this weekend too.  Congratulations!
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
and no, we're not marrying each other....we just work in the same building.
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
CONGRATULATIONS to you both!

If it's ok with you 2, then I can handle the post to CS, or, we can all wait till u come back from your honey moon(s).

Nosterdamus
0
 
LVL 6

Expert Comment

by:PsychoDazey
Comment Utility
Thanks everyone, and congrats to you too cjs.
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
Go for it Nost...thanks PD!
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
I guess that now cj & PD are crossing the room to shake hands and mybe exchange kisses....

And they lived happyly ever after (with different partners)...

Enjoy life guys & gals...;-)

Nosterdamus
0
 
LVL 6

Expert Comment

by:cjswimmer
Comment Utility
just to note, we're both guys.  We've been offering each other congratulations/condolences for a couple of weeks now...
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
condolences indeed, but this is another story....
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
niner,

You seem to be experiencing some difficulty with this site.

Experts-Exchange is not a news group, in which you can post a question, get the answer, and walk away, or ignore any posts that do not serve to solve your problem.

You can read more about how the site works in our member agreement: http://experts-exchange.com/jsp/infoMemberAgreement.jsp

Although the agreement tells you something about how the site works, it doesn't tell you much about the netiquette that has evolved on our site. In Experts-Exchange it is expected that you always provide feeback to those who participate in the questions that you post. And I'll explain why this is the case.

When a question is resolved, it becomes a Previously Answered Question, and will be stored in the database. Users who have similar problems can buy your question for 1/10 of the original point value in order to find a solution to *their* problem.

A a result it is important that you provide feedback to our experts. If their suggestions do not work, tell them why. If they do, award them by grading the answer so that the question does indeed become such a "PAQ".

That way future visitors of this question get the most out of it.

I would also like you to consider this: Our experts are willing to spend some of their precious time to help you solve your problem. A lot of our experts are professionals, to whom time is money. And they're willingly giving up some of their time to help you.

And the only thing we're asking in return is a bit of feedback and an effort on your behalf to participate according to our member agreement and our peculiar brand of netiquette. I think that is a pretty good deal, don't you?

Now, you have graded this question. Probably to be able to unsubscribe and to stop the notifs coming in. Which is not the right thing to do either, especially since nobody seems to know why you awarded the points the way you did.

You will have to come back to this question and tell us what is going on. If you want to split points or something, just let me know, I can be of assistance there.

Please respond.

modder
Community Support


(PS - with the notification problems, and anyway, in case niner has switched off notifications, I will e-mail him or her with this note as well.

If no response is given within a week from now, post another comment here so I get another notif and a reminder)
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi modder,

This is a reminder...

Have a nice day.

Nosterdamus
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
Hi Nosterdamus....

The question is accepted..... did that just happen?

<confused>
modder
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
Hi modder,

I assume that you are a very bussy person, therefore, to refresh your memory, please read my post at CS [ http://www.experts-exchange.com/jsp/qManageQuestion.jsp?ta=commspt&qid=20160730 ] again ;-)

Thanx,

Nosterdamus
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
LOL..... thanks.
0
 
LVL 3

Expert Comment

by:modder
Comment Utility
Reduced points for this question to zero, and reduced niner's available question points to zero until such time as (s)he can come and explain what happened here.

And for the record. The available question points stood at 1756 (in case I need to reinstate)
0
 
LVL 7

Expert Comment

by:Nosterdamus
Comment Utility
modder,

Just a thought...

Since this is an extreme action taken towards a member of the community (reducing the accumulated points to zero), I suggest that in addition, you should send niner an e-mail to his/her personal account, notifing about the action taken, explaining why it was done and how (s)he can reclaim the points back. In such notification, I would also add a direct link to this thread.

Thanx for your intervention...

Nosterdamus
0
 

Author Comment

by:niner
Comment Utility
To Modder et al,

My, what a situation this has become.

First off, I would like to apologize to the members and support staff of the community. Shortly after accepting the answer to the question, my private life suffered a great upheaval. I won’t get into the gory details, but many things changed for me both privately and professionally. As a result of the changes in my life and the resulting change in my views about life, and having come into some money as things were settled, I decided to take a sabbatical from my life here and traveled to Africa to do some volunteer work for a while, returning only recently. I was without means of any type of electronic communication for great periods of time and never checked back to this site having to use my precious online communication time for more important, personal communications.

While I no longer recall the specifics of this question or the utilization of any or all of the proffered answers, I would definitely like to thank you all for your time and express my sincerest heartfelt gratitude for your help. Should a redistribution of any of my confiscated or since accumulated points provide a means for salving any wounds derived from this breach of netiquette, please have the moderator do as he or she sees fit.

Peace be with you.
Niner.
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now