Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query by Form (A2K)

    Edited by HansV to provide links to MSKB articles - makes it easier for Loungers wanting to help

    A few years back, Ken Getz posted what has to be one of the most usefule pieces of code that enables you to select a form and having a QBF form generated so that it can be used for search purposes.

    I've used it with great success over time and have been able to adapt it to not only search for specific alpha items by using "wildcard" *'s but also by setting up two fields for the same numeric data item and being able to search for > and < than for a specific field.

    What I'm attempting to do now is to search for > and < in alpha fields, but I'm running into problems.

    I don't want to tie up Forum space by printing out the code, but if someone out there has used this specific item, I'd appreciate hearing from you and hopefully we can trade QBF notes. If you do want me to post the exact code and problems, also let me know.

    If you haven't seen this code, it's well worth looking at and can be downloaded from the Microsoft site at <!mskb=136062>Microsoft Knowledge Base Article 136062<!/mskb> (Access 95/97) or <!mskb=209645>Microsoft Knowledge Base Article 209645<!/mskb> (Access 2000).
    Cheers,
    Andy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query by Form (A2K)

    If you want to compare text values, you must put quotes around the literal values. For example:
    <code>
    strWhere = strWhere & " AND EmployeeName > '" & Me.txtName & "'"
    </code>
    or
    <code>
    strWhere = strWhere & " AND EmployeeName > """ & Me.txtName & """"
    </code>
    or
    <code>
    strWhere = strWhere & " AND EmployeeName > " & Chr(34) & Me.txtName & Chr(34)
    </code>
    The three lines are basically equivalent, they demonstrate various ways of working around the problem that you cannot directly include a double quote <code>"</code> in a quoted string.

  3. #3
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form (A2K)

    Hans,

    Thanks kindly for the reply. I'll get on this this evening and let you know the results.
    Cheers,
    Andy

  4. #4
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form (A2K)

    Hans,

    I can see the appropriatness of your code and will be added to my treasure trove of Hans-suggested treasures which I'll be able to use under "normal" circumstances.

    The problem in this case is to be able to apply to Ken's code (which follows) and have it usable for both numeric and text fields. I don't think this is going to happen unless there is someway to test for the nature of the field being used.

    I do appreciate your suggestions, which, like I said, will be set aside for future use.

    '================================================= ====
    Private Function BuildWHEREClause(frm As Form) As String
    Dim intI As Integer
    Dim strLocalSQL As String
    Dim strTemp As String
    Dim varDataType As Integer
    Dim varControlSource As Variant
    Dim ctl As Control
    For intI = 0 To frm.Count - 1
    Set ctl = frm(intI)
    varControlSource = adhCtlTagGetItem(ctl, "qbfField")
    If Not IsNull(varControlSource) Then
    If Not IsNull(ctl) Then
    varDataType = adhCtlTagGetItem(ctl, "qbfType")
    If Not IsNull(varDataType) Then
    strTemp = "(" & BuildSQLString(CStr(varControlSource), ctl, CInt(varDataType)) & ")"
    strLocalSQL = strLocalSQL & IIf(Len(strLocalSQL) = 0, strTemp, " AND " & strTemp)
    End If
    End If
    On Error GoTo 0
    End If
    Next intI
    If Len(strLocalSQL) > 0 Then strLocalSQL = "(" & strLocalSQL & ")"
    BuildWHEREClause = strLocalSQL
    End Function
    '================================================= ====
    Cheers,
    Andy

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query by Form (A2K)

    The procedure you posted calls BuildSQLString. Neither of the MSKB articles you referred to contains this code, so it would be helpful if you posted it. If it is long, save it as a text file and attach that.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query by Form (A2K)

    You wrote you have already adapted this code to work with > and <. How?

  7. #7
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form (A2K)

    Hans,

    I'll put together the version that I have that works and will forward it to you by tomorrow. Thanks for keeping an eye out for me,
    Cheers,
    Andy

  8. #8
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form (A2K)

    Hans,

    I've attached a cut-back version of what I'm attempting to do. The steps to follow in order to test out my problems are as follows:

    1. Open QBF Systems.mdb and it will go directly to the Inventory Form.
    2. There are 13 records in the table
    3. Click on GoTo Parameters
    4. In the Parameters Form, Enter >10 in MaxGreaterThan and <30 in MaxLessThan, then click on any other field. This signifies the end of parameter entries. These are both numeric fields.
    5. Click on OK at the bottom of the form and it will take you back to the original form, and you will have 3 filtered records meeting the requested parameters qualifications.
    6. Click on Remove Criteria which will reset the records back to 13
    7. Click on GoTo Parameters
    8. In the Parameters Form, Enter >"B*" in PartNoGreaterThan and <"G*" in PartNoLessThan, then click on any other field. This signifies end of parameter entries. These are both text fields.
    9. Click on OK at the bottom of the form and and it will take you back to the original form, and you will have 5 filtered records meeting the requested parameters qualifications.
    10. Click on Remove Criteria which will reset the records back to 13
    11. Click on GoTo Parameters
    12. In the Parameters Form, Enter >"*B*" in BinNoGreaterThan and <"*G*" in BinNoLessThan, then click on any other field. This signifies end of parameter entries. These are both text fields.
    13. Click on OK at the bottom of the form and and it will take you back to the original form, and you will have 0 filtered records which does not meet the parameters qualifications
    14. So it appears that it can handle numeric fields and text fields when the wild card character is after the first part of the criteria, but will not handle wild card characters on either side of an alpha character. That would be the ideal situation, but I consider myself lucky that it works to this degree. It was your hint to put quotes around the alpha characters that got past my first problem and that's greatly appreciated. Icing on the cake would be to be able to do the multiple wildcard scenario.

    Thanks again for looking at this for me,
    Cheers,
    Andy

  9. #9
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form (A2K)

    Attachment edited by HansV to insert copyright line. The code in the attachment is Ken Getz 1997. All rights reserved.

    Hans,

    Please see attached:
    Cheers,
    Andy

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query by Form (A2K)

    Greater than and less than for text values ALWAYS compares starting at the first character, regardless of any wildcards you enter.

    By the way, you can use <code>>"B"</code> and <code><"G"</code> instead of <code>>"B*"</code> and <code><"G*"</code> - wildcards are only used when you use Like or Not Like, not when you use = or > or < etc.

    If you want to filter all values that contain one of the letters B through G anywhere in the value, you can use <code>Like "*[B-G]*"</code> as criteria.
    If you want to filter all values that contain one of the letters B through G in the second position, you can use <code>Like "?[B-G]*"</code> as criteria. ? stands for one character, * for any number of characters (even 0).

  11. #11
    5 Star Lounger
    Join Date
    Nov 2001
    Location
    Toronto Canada
    Posts
    920
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query by Form (A2K)

    Hans,

    Has anyone ever presented you with a problem you couldn't solve? Totally outstanding!

    I thank you most kindly,
    Cheers,
    Andy

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Query by Form (A2K)

    > Has anyone ever presented you with a problem you couldn't solve?

    Sure! There are some threads I don't even attempt to participate in. (Plus, there's a whole world outside the Lounge...)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •