Results 1 to 6 of 6
  1. #1
    jeffhanner is offline Advanced Beginner
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Location
    Lincolln, CA
    Posts
    40

    Query Design using Like and TempVars

    I have a query where I have two conditions (criteria). One field is boolean and it is set to True, and it works. The other is Short Text. In the query design I am trying to use a Tempvars variable in the criteria "field" which originates from a user inputbox. I have tried, in the criteria field, the following:

    [TempVars]![ShelterName] (I spelled out the whole name correctly)

    Like [TempVars]![ShelterName] (I just spelled part of the name)

    Like "*" & [TempVars]![ShelterName] & "*". (This works part of the time. I use this information to fill an Excel Spreadsheet, it doesn't always write, so the problem might be in my Excel Code. I will review that. )

    If I put in an actual string, without any user input, such as Like "Foothill*". (it works).

    I tried researching this in the forum and on google, but the solutions never covered using a variable as part of the criteria.

    Any suggestions?



    Jeff
    Last edited by jeffhanner; 10-05-2022 at 05:47 PM. Reason: Update

  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Untested, but try

    Like [TempVars]![ShelterName] & "*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    jeffhanner is offline Advanced Beginner
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Location
    Lincolln, CA
    Posts
    40
    That didn't work any different . I built a subroutine, below to see if the query was taking the tempvars variable, it isn't. Again I tried Criteria:

    Like "*" & [TempVars]![ShelterName] & "*" (Which seems to work in other parts of my code.)
    Like [TempVars]![ShelterName] & "*"
    [TempVars]![ShelterName]

    Code:
    Sub TestVarQRY()
    TempVars!ShelterName = InputBox("Enter Name")
    Debug.Print TempVars!ShelterName ' This works fine
    DoCmd.OpenQuery "qryExportToSCUFforPriorityList"
    
    
    
    
    End Sub

  4. #4
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Can you attach the db here? I haven't used TempVars but this works in a brief test with a form textbox:

    Like [forms]![form1].[text16] & "*"
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    jeffhanner is offline Advanced Beginner
    Windows 10 Access 2019
    Join Date
    Sep 2022
    Location
    Lincolln, CA
    Posts
    40
    Sorry to respond late. However, the problem was in two different areas. First of all I was able to use
    Like "*" & [TempVars]![ShelterName] & "*", however I had to change the Trust Settings since I moved both files of the split database to a server. Second I changed the tempvar name and used the Trim function to trim the ShelterName as follows:

    Code:
    TempVars!SelectShelter = Trim(InputBox("Enter Shelter Name"))

    Everything works fine now.
    Thanks for trying to help.

  6. #6
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Glad you got it sorted. Looks like you're just over the hill from me.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

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

Similar Threads

  1. Replies: 3
    Last Post: 06-23-2020, 10:37 PM
  2. TempVars Slowing Down Query?
    By jnoonan22 in forum Queries
    Replies: 4
    Last Post: 12-31-2015, 12:13 PM
  3. How do you use Tempvars in an Update query?
    By JrMontgom in forum Queries
    Replies: 4
    Last Post: 05-02-2015, 08:43 AM
  4. How to use Tempvars in query
    By JrMontgom in forum Programming
    Replies: 3
    Last Post: 09-03-2014, 12:36 PM
  5. Using TempVars in query
    By jonesy29847 in forum Programming
    Replies: 3
    Last Post: 03-03-2011, 11:07 PM

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