Results 1 to 12 of 12
  1. #1
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206

    Like "**"

    I have a Query that's I'm working on and it has Like "**" in the Criteria. This query supplies the data to the Row Source in a Search form unbound box. What does the double ** for? I see the * Allows you to match any string of any length (including zero length). I can't seem to find it in a search. One thing more is the Query works, but I'm looking to make changes to it.


    Thanks,
    Tom

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Are you sure ** isn't just a typo? I could understand "LIKE *" & somefieldConcatenationHere & "*"
    If the middle part was missing, I suspect you could arrive at LIKE** but I'd say that's because of an omission rather than by design.
    I understand && (the first is treated as an escape character, the latter as literal text) for form control shortcuts but not **
    Do you need help with modifications or are you just asking for an explanation of the **?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Looks like maybe they were intending to search for something but didn't? Or have it in there incase you want to manually search for something? As it is it should just return everything since no values inside the **. If you do not need it you should just remove it to avoid confusion down the road. But again, test the recordset with and without it to make sure it is the same. And maybe put a note in documentation that you removed it just incase.

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Show the entire SQL statement.

    Doubling a special character 'escapes' that character and should force it to be treated as literal text. I use this technique when building SQL strings in VBA and I expect data to contain apostrophes (such as: O'Reilly).
    Code:
    "SELECT * FROM tablename WHERE LastName='" & Replace(Me.cbxLName, "'", "''") & "'"
    However, wildcards seem to require special treatment. If you want to search for literal *, #, ? character in data, enclose in [ ]. LIKE "*[*]*"

    But without something between the two asterisks, just 2 wildcards in sequence and everything is returned.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Some how I got all of these Like "**" in my query Criteria, not sure how they got there. I went back to an old ver. and they weren't there ???. So I added the old SQL to my newest ver. and all seems to show as needed. What I'm working on is in my main form if I make a change I don't see it in my Search form. I tried me.requery, still doesn't update. I'm thinking it's something in the Query on the row source for the Search form. The info. is saved on main form before search and is listed in the tblParts Table.

    tblParts.P_Tag is the field I need updated. I changed
    tblBins.Bin_Tag, but still the same.
    Just need a little help where to go.
    Thanks,
    Tom

    Code:
    SELECT tblParts.P_ID, tblParts.P_Name, tblParts.P_Number, tblParts.P_Type, tblParts.P_TypeSub, tblParts.P_Value, tblParts.P_ValueType, tblParts.P_Rating, tblParts.P_RatingType, tblParts.P_Quantity, tblParts.P_Date, tblParts.P_Location, tblBins.Bin_Tag
    FROM tblBins INNER JOIN tblParts ON tblBins.[Bin_ID] = tblParts.[P_Location]
    ORDER BY tblParts.P_ID DESC , tblParts.ExtraFld, tblParts.P_Value, tblParts.P_Type, tblParts.P_TypeSub;

  6. #6
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Must be with the INNER JOIN in the SQL? just a quess. (update) Maybe not ?

    I changed this SQL, all data shows, but doesn't update the new data in main form.

    Code:
    SELECT tblParts.P_ID, tblParts.P_Name, tblParts.P_Number, tblParts.P_Type, tblParts.P_TypeSub, tblParts.P_Value, tblParts.P_ValueType, tblParts.P_Rating, tblParts.P_RatingType, tblParts.P_Quantity, tblParts.P_Date, tblParts.P_Location, tblParts.P_Tag
    FROM tblParts
    ORDER BY tblParts.P_ID DESC , tblParts.ExtraFld, tblParts.P_Value, tblParts.P_Type, tblParts.P_TypeSub;

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Form has RecordSource, not RowSource. Is this search form a single form or a form/subform? Do you need to requery subform?

    If you want to provide db for analysis, follow instructions at bottom of my post.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Search form is opened from main form, SQL is in the Row Source. I tried Me.Requery in Search form, still doesn't update.

  9. #9
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Update on my problem, If I enter a new part all fields show except P_Tag in Search form. all other data shows for other parts and P_Tag.

  10. #10
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Where is Me.Requery? Show code or provide db.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  11. #11
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    Data doesn't show for new P_Tag, but does show for data in tblParts, just blank. tblParts does show all the new data.


    This is code in Search form

    Code:
    Private Sub Form_Load()
        On Error Resume Next
        Call cmdReset_Click
        Me.txtSearch.SetFocus
        Me.Requery
    End Sub

  12. #12
    MadTom's Avatar
    MadTom is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jun 2018
    Location
    CT and VT
    Posts
    206
    OK! I took my SQL and created a new Query and it worked! So I had to look at what the results of the search SQL provided and I had to change some code on that in my Sub. I had the wrong Table in the statement. Most of the time it will dawn on me in the middle of the night. Its good to keep your mind sharp.
    Thanks for your help!
    Tom
    Don't know where I got Like"**", but didn't need it.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple table relationships ("faces" to "spaces" to "chairs")
    By skydivetom in forum Database Design
    Replies: 36
    Last Post: 07-20-2019, 01:49 PM
  2. Replies: 12
    Last Post: 10-01-2018, 02:40 PM
  3. Replies: 3
    Last Post: 06-06-2018, 08:26 PM
  4. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  5. Replies: 1
    Last Post: 09-03-2014, 03:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums