Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18

    One button with macro filter

    hi everyone, I was practicing with Access as usual, to understand the various Acess workings. As an exercise, I created a button in a form. In the form I used a query, it contains various data, including prices. And through this button that I created, I wanted to apply a Macro with a filter where in the "Where condition" I was going to specify a range between two numbers, so once I clicked the button it would have returned only that range of numbers. I know it's not the fastest and most sensible method, but I just wanted to force myself to make a filter button with macros.
    in fact in the macro I wrote:

    [Price] Between "3001" And "4000"

    the problem is that as results there are ALSO products with a price of 400 and I don't understand why. So I tried to make another button, with another macro by putting:

    [Price] Between "0" And "1000"

    and the product with price 400 was not appearing. And it wasn't a single record problem, but multiple records with the same 400, gave the same error. My question is: Why in the [Price] Between "3001" And "4000" he ALSO gets me prices from 400?
    PS. I noticed that it does the same thing with the 500, in fact if I change to:


    [Price] Between "4001" And "5000"
    it also returns me the products with price 500 besides the others that are correct. The "price" field is a text field and NOT a numeric one, because some product prices are marked with "?" or "???".

  2. #2
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Like many others here I prefer to use VBA code rather than macros. Have you tried without the quotation marks?
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  3. #3
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    This looks like a re-post of the same issue from here https://www.accessforums.net/showthr...556#post486556

    If it is, please don't post the same issue more than once, even if you think it could belong in different forums. I posted 2x with what seemed to be your problem, and given the post here, I'd say you already have your answer. 500 is between 1000 and 5000 because you are using text data type as numbers.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by Bob Fitz View Post
    Come molti altri qui preferisco usare il codice VBA piuttosto che le macro. Hai provato senza le virgolette?
    I know, but as I wrote above I want to commit to doing it with macros applied to a filter button. without quotes gives me a syntax error.

  5. #5
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by Micron View Post
    This looks like a re-post of the same issue from here https://www.accessforums.net/showthr...556#post486556

    If it is, please don't post the same issue more than once, even if you think it could belong in different forums. I posted 2x with what seemed to be your problem, and given the post here, I'd say you already have your answer. 500 is between 1000 and 5000 because you are using text data type as numbers.

    it is not a re-post also because in that post I was talking about Report and it is an exercise that I have abandoned for the moment, and I wanted to start from another simpler one. (and then I have never had this problem of the price at 400, which is taken even if I put between 3100 and 4000, in fact in that post if you see I never wrote that I have similar problems, while in this new case it is my main problem ). I also hypothesized that the problem could be that the data type is text and not numeric, in fact in the first message I specified that there are "?" or "???" in prices. However, I do not solve anything knowing this, because the field must remain of type text and therefore I need help to be able to apply the filters correctly in this way.

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Whether you want to use a macro, code, button, form, report - it is the same problem and the same cause. You need to think of numbers as text the same way you would if you were looking at names in a phone book (assuming you're old enough to know what that is). Here are some numbers in text (alpha) order:
    1
    10
    1000
    11
    111
    2
    22
    222
    23
    5
    8
    88
    9

    You should be able to see why 500 comes between 1000 and 600
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are confusing text "3001" and "4000" with numbers 3001 and 4000. Your mind is automatically converting the TEXT "3001" and "4000" to numbers.

    -> With text, access compares characters from left to right. <- (we're talking about comparing ASCII values from left to right)

    You get "400" because ACCESS compares the first character (ASCII value) in [Price] with the first character (ASCII value) in "3001" and with the first character (ASCII value) in "4000".

    Is the "4" (in the 400") is between "3" and "4" inclusive? Yes
    Then the 2nd character is compared. So next is the character "0" (from Price). Is the 2nd character "0" (in the 400") is between "0" (from "3001") and "0" (from "4000") inclusive? Yes
    Then the 3rd character is compared. So next is the character "0" (from Price). Is the 3rd character "0" (in the 400") between "0" (from "3001") and "0" (from "4000") inclusive? Yes
    So "400" meets the criteria, even though we know that 400 should not be returned.



    Lets say you have a table designed like this
    Click image for larger version. 

Name:	Table Design.png 
Views:	19 
Size:	34.4 KB 
ID:	46757
    and enter this data in each field
    Click image for larger version. 

Name:	Table Data.png 
Views:	19 
Size:	65.3 KB 
ID:	46758


    If you then create a query sorted by PriceNumber, you will see
    Click image for larger version. 

Name:	Number Sorted.png 
Views:	19 
Size:	31.7 KB 
ID:	46759


    but if you change sorted by PriceText, you will get
    Click image for larger version. 

Name:	Text Sorted.png 
Views:	19 
Size:	34.5 KB 
ID:	46760
    Why is the PriceText not sorted "correctly"?



    Guess what happens if the query is
    Code:
    SELECT PriceText
    FROM tblFilterTest
    WHERE (((tblFilterTest.PriceText) Between "1001" And "3000"));
    (you only get 20)



    "Between" is used for number types and datetime type fields (datetime is actually a number - double).


    I don't use macros (never even wrote a macro), so I don't know what type of formula would work for the criteria.

  8. #8
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by Micron View Post
    Whether you want to use a macro, code, button, form, report - it is the same problem and the same cause. You need to think of numbers as text the same way you would if you were looking at names in a phone book (assuming you're old enough to know what that is). Here are some numbers in text (alpha) order:
    1
    10
    1000
    11
    111
    2
    22
    222
    23
    5
    8
    88
    9

    You should be able to see why 500 comes between 1000 and 600


    Micron I understand what the problem is, I understand WHY this happens, what I want is a solution. I cannot change from text to numeric, and at the same time I need to make filters which still filter the prices in a normal way. so if I impose in a macro between 3000 and 4000 I don't want 400 to be in between, and I repeat I understand why, but I need a solution, otherwise I wouldn't be here to ask.

  9. #9
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by ssanfu View Post
    You are confusing text "3001" and "4000" with numbers 3001 and 4000. Your mind is automatically converting the TEXT "3001" and "4000" to numbers.

    -> With text, access compares characters from left to right. <- (we're talking about comparing ASCII values from left to right)

    You get "400" because ACCESS compares the first character (ASCII value) in [Price] with the first character (ASCII value) in "3001" and with the first character (ASCII value) in "4000".

    Is the "4" (in the 400") is between "3" and "4" inclusive? Yes
    Then the 2nd character is compared. So next is the character "0" (from Price). Is the 2nd character "0" (in the 400") is between "0" (from "3001") and "0" (from "4000") inclusive? Yes
    Then the 3rd character is compared. So next is the character "0" (from Price). Is the 3rd character "0" (in the 400") between "0" (from "3001") and "0" (from "4000") inclusive? Yes
    So "400" meets the criteria, even though we know that 400 should not be returned.



    Lets say you have a table designed like this
    Click image for larger version. 

Name:	Table Design.png 
Views:	19 
Size:	34.4 KB 
ID:	46757
    and enter this data in each field
    Click image for larger version. 

Name:	Table Data.png 
Views:	19 
Size:	65.3 KB 
ID:	46758


    If you then create a query sorted by PriceNumber, you will see
    Click image for larger version. 

Name:	Number Sorted.png 
Views:	19 
Size:	31.7 KB 
ID:	46759


    but if you change sorted by PriceText, you will get
    Click image for larger version. 

Name:	Text Sorted.png 
Views:	19 
Size:	34.5 KB 
ID:	46760
    Why is the PriceText not sorted "correctly"?



    Guess what happens if the query is
    Code:
    SELECT PriceText
    FROM tblFilterTest
    WHERE (((tblFilterTest.PriceText) Between "1001" And "3000"));
    (you only get 20)



    "Between" is used for number types and datetime type fields (datetime is actually a number - double).


    I don't use macros (never even wrote a macro), so I don't know what type of formula would work for the criteria.

    thank you for the most accurate explanation, and for being more polite than others. what I wrote above was that in broad terms I had already understood what the problem was, but unfortunately I can not find a solution, but if you are not an expert in macros then I believe that you cannot help me, but I thank you anyway for the time you have dedicated.

  10. #10
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    In my development, I don't use macros, but to try and keep up with Access' offerings, I do study them a bit.
    See if this will work for you:

    Click image for larger version. 

Name:	Demo.png 
Views:	18 
Size:	4.9 KB 
ID:	46761

  11. #11
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    These are quotes from individual posts that you made
    the problem is that as results there are ALSO products with a price of 400 and I don't understand why.
    Why in the [Price] Between "3001" And "4000" he ALSO gets me prices from 400?
    I want to do it this way because I want to understand some concepts and dynamics.
    So you think I'm rude? What should I call a person whom I tried to help but doesn't bother to answer a question and then posts the same problem elsewhere?
    The red quote is what I focused on - trying to get you to understand that 400 comes between 1000 and 500 (in your case) because that's what you asked. If not clear, please read the quotes again.

    I also hypothesized that the problem could be that the data type is text
    If you did, you didn't write that in a post.
    Yes, you said there were ? characters in the field, yet still asked why you get a particular result.

    Don't blame me for trying to answer your question and make you understand what your issue is, especially when you say you are just trying to learn concepts. If you asked for a specific solution, I missed that but would also have suggested using a conversion function BUT Clng will error because the value contains 1 or more characters that cannot be interpreted as a number. If you simply remove the ? from the value you wish to use, then the replace function will do that without altering the data. Then the Clng will convert the result to a number. Then it should sort/query correctly:

    Clng(Replace("123?","?",""))

    EDIT
    Of course, you would replace 123? with your field name. If your field can contain Nulls then I think you will raise another problem.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  12. #12
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by davegri View Post
    Nel mio sviluppo, non uso macro, ma per cercare di stare al passo con le offerte di Access, le studio un po'.
    Vedi se questo funzionerà per te:

    Click image for larger version. 

Name:	Demo.png 
Views:	18 
Size:	4.9 KB 
ID:	46761

    that's right Dave, I'm also doing this exercise to understand the dynamics of Macros, so I insist on solving the situation in this way. I tried what you told me, but he told me a null error. I checked and there are some products where there is no price, do you know a way to use what you wrote to me, also counting that there are null values? so then I can check if you have finally found the solution to my problem. However, I'm glad I found someone who understood that I'm just trying to practice Macros, purposely complicating my life.

  13. #13
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    Perhaps google NZ() function?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  14. #14
    Jackroland is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2021
    Posts
    18
    Quote Originally Posted by Micron View Post
    Queste sono citazioni da singoli post che hai scritto






    Quindi pensi che io sia scortese? Come dovrei chiamare una persona che ho cercato di aiutare ma non si preoccupa di rispondere a una domanda e poi pubblica lo stesso problema altrove?
    La citazione rossa è ciò su cui mi sono concentrato: cercando di farti capire che 400 arriva tra 1000 e 500 (nel tuo caso) perché è quello che hai chiesto. Se non è chiaro, leggi di nuovo le citazioni.

    Se lo hai fatto, non l'hai scritto in un post.
    Sì, hai detto che c'erano? caratteri nel campo, ma ancora chiesto perché ottieni un risultato particolare.

    Non incolpare me per aver cercato di rispondere alla tua domanda e farti capire qual è il tuo problema, specialmente quando dici che stai solo cercando di imparare concetti. Se hai chiesto una soluzione specifica, mi è sfuggita, ma avrei anche suggerito di utilizzare una funzione di conversione MA Clng genererà un errore perché il valore contiene 1 o più caratteri che non possono essere interpretati come un numero. Se rimuovi semplicemente il ? dal valore che si desidera utilizzare, la funzione di sostituzione lo farà senza alterare i dati. Quindi il Clng convertirà il risultato in un numero. Quindi dovrebbe ordinare/interrogare correttamente:

    Clng(Replace("123?","?",""))

    EDIT
    Ovviamente, sostituiresti 123? con il nome del tuo campo. Se il tuo campo può contenere Null, penso che solleverai un altro problema.

    I didn't mean to be rude, I think in the end we didn't understand each other, I know you're here to help and I thank you for that. If I looked bad to you, I'm sorry I didn't want to make you think this. You're right I reread the message and I hadn't written what I thought. that it was the text type instead of the numeric type the problem, but I just wrote that I had characters "???" and that was why I had the "text" date type. But in fact I wanted to make it clear that maybe that was the problem, but obviously then immediately after your first message, I realized that that was the real and only problem. Anyway to answer your last question .... yes I have null fields. In short, this exercise becomes more and more complicated to solve, but I don't want to change anything to make it easier, I also take it as a good challenge, for us who like these things, but I think you will understand me. A part of me would like to take and fill products where the price is not there, or remove the "???" and then change the data type to numeric, but I would really like to solve the problem if possible by counting that there can be null values ​​and values ​​like "???"
    anyway if you no longer want to help me I understand you, but anyway I thank you all the same for helping me up to this point.

  15. #15
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We're good.
    Try

    Clng(Replace(Nz([Price],0),"?","")) Between 1000 And 4000
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 06-17-2015, 12:20 PM
  2. Command Button to Run Macro, can't find macro
    By Ace2014 in forum Modules
    Replies: 22
    Last Post: 06-11-2014, 11:42 AM
  3. MACRO to filter
    By DAE60 in forum Programming
    Replies: 1
    Last Post: 11-13-2012, 02:10 PM
  4. Replies: 28
    Last Post: 03-08-2012, 06:47 PM
  5. Replies: 0
    Last Post: 01-12-2011, 12:43 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