Results 1 to 10 of 10
  1. #1
    lorynhope is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4

    Add spaces to query criteria through form entry

    I have a table with a field that contains both 6 and 9 digit numbers. All 6 digit numbers have 3 spaces after them. I'm not able to change the structure of this table.

    If I query directly to the table and enter a 6-digit search criteria, I get results. However, if I program a field on a form to query the same value, I get zero results.



    Any ideas? Let me know what other kind of information I can provide. I'm stumped!

  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
    Shot in the dark, but try

    WHERE FieldName = Left(Forms!FormName.TextboxName & Space(9), 9)
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    How do you
    query directly to the table and enter a 6-digit search criteria
    ? Are you using the QDE to create a new query and entering a 6-digit number in the criteria row? Are you filtering and using "Contains". Is your form bound to the table directly or to a Select query based on the table? How did you
    program a field on a form to query the same value
    ? Is it a combo-box? Are you applying a Where clause or are you trying to filter the form?

    The easiest would be if you could upload a stripped down version of your db with just the table (just a couple of sample records with no private info in them) and the form.

    Cheers,
    Vlad

  4. #4
    lorynhope is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    Quote Originally Posted by Gicu View Post
    The easiest would be if you could upload a stripped down version of your db with just the table (just a couple of sample records with no private info in them) and the form.

    Cheers,
    Vlad
    Thanks, Vlad. The table is proprietary to our company and has hundreds of thousands of records. There's no where clause - it's simply the query criteria as [Forms]![frmASC]![txtASC] querying a 6-digit value. When I enter that same 6-digit value directly into a query (looking for the same exact information) I get the results (and of course the 3 erroneous spaces after the 6 digits in the table) but zero results from the form. I've tried entering wildcards into the form, spaces, etc. I've even tried using a combo box and having the user select the value from the actual table and I still get zero results. So frustrating!!

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Did you try the suggestion in post 2?
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    We don't need any of the actual records, just one or two samples with the number field in question (looks like it is defined as a text field). That is the Where clause is - in QDE it is created by entering a criteria on that row. So in the query you are trying to replace "123456 " with a reference to [Forms]![frmASC]![txtASC] ? Is the frmASC open when you do it? Can you make a copy of your original table then run an update query to remove the trailing spaces Trim([MyNumberFieldDefinedAsText]) and see what that does?

    Cheers,
    Vlad

  7. #7
    lorynhope is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    I did to no avail.

  8. #8
    lorynhope is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2019
    Posts
    4
    EUREKA!!!

    I used the Trim function on the same field I'm evaluating for the query criteria [Forms]![frmASC]![txtASC] and got the results I was looking for.

    Thanks so much for all of your help

  9. #9
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    Glad to hear!

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    Quote Originally Posted by lorynhope View Post
    I did to no avail.
    I'd be interested to see the SQL of that. Glad you got the Trim function to work, but I suspect the way you've used it will be inefficient if the table gets large.
    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: 07-31-2017, 07:51 PM
  2. Replies: 4
    Last Post: 02-06-2017, 09:23 PM
  3. Replies: 2
    Last Post: 10-20-2014, 03:09 PM
  4. Using Subform entry as criteria in a Query
    By bdicarlo1 in forum Access
    Replies: 2
    Last Post: 11-14-2012, 06:49 PM
  5. Replies: 3
    Last Post: 05-19-2010, 10:08 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