Thanks Thanks:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Macro comparison, why is one not working??

  1. #1
    Board Regular
    Join Date
    Mar 2017
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Macro comparison, why is one not working??

    I have 2 search forms, one is working just fine, I used it as a template to build the second but something is different and Im not seeing it. Both forms are using an Apply filter macro on a "search" command that references 3 test boxes

    Working Marco - Apply Filter - Where

    ([SEARCH_MTR]![MTR] Like "*" & [Forms]![SEARCH_MTR]![Search-MTR] & "*") And
    ([SEARCH_MTR]![HEAT/SLAB] Like "*" & [Forms]![SEARCH_MTR]![Search-HS] & "*") And
    ([SEARCH_MTR]![THICK] Like "*" & [Forms]![SEARCH_MTR]![Search-THICK] & "*")

    Failed Macro - Apply Filter - Where

    ([SEARCH_INV]![MTR] Like "*" & [Forms]![SEARCH_INV]![Search_MTR] & "*") And
    ([SEARCH_INV]![HEAT/SLAB] Like "*" & [Forms]![SEARCH_INV]![Search_HS] & "*") And
    ([SEARCH_INV]![THICK] Like "*" & [Forms]![SEARCH_INV]![Search_Thick] & "*")

    When I open the working Form I receive a enter parameter prompt, I can bypass it and all data will show. I can use any of the search boxes and the form returns the correct results, it will show a single prompt to enter additional data but clicking ok and bypassing it returns the correct results enven when only one text box has a value.

    When I open the failing Form I receive no prompts, it opens but if I use any of the text filters I receive 3 prompts to enter data, one for each text field. If I enter data in the text boxes and bypass the prompts no data is shown, if I enter data into one or two of the prompts no data is shown, if I enter data into all three of the prompts all data shows with matched entries sorted first.

    I don't understand why they are preforming so differently, any thoughts on what might be going wrong here?

  2. #2
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,622
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro comparison, why is one not working??

    are all the forms spelled correctly?
    are all the textboxes spelled correctly?

    (the control could be misspelled than what you think it is)
    Last edited by ranman256; Oct 4th, 2017 at 01:22 PM.

  3. #3
    Board Regular
    Join Date
    Mar 2017
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro comparison, why is one not working??

    Initially I copied and pasted, then edited the fields that needed to be changed. When it didnt work the first thing I did was rebuild it, as soon as you put the ! after a table or form the field options pop up in the drop down, every item was selected from the options available so I dont think spelling could be the issue.

  4. #4
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,893
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro comparison, why is one not working??

    One of your queries uses hyphens where the other uses underscores. maybe that's it?

    Search-MTR
    vs.
    Search_MTR

    and likewise for HS and Thick.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  5. #5
    Board Regular
    Join Date
    Mar 2017
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro comparison, why is one not working??

    No, unfortunately Im not that lucky. Its 2 macros, and 2 forms, one in each. The Text boxes have similar but different names in each Form, thats what that difference is.

  6. #6
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,893
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro comparison, why is one not working??

    Search forms are a pain. The error would normally be related to names not being correct, but that's about all I can say. Personally I just use Access in built filtering buttons and right-click options, which is very powerful and requires no extra work from you.
    Last edited by xenou; Oct 4th, 2017 at 05:28 PM.

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  7. #7
    Board Regular
    Join Date
    Mar 2017
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro comparison, why is one not working??

    Thanks guys, and yep, it was pretty simple. I had one control with the same name as my data source, right name wrong source on one entry. There is a followup question though, in my first form, the associated query did not contain any null fields. The form that was just fixed references a query that dose contain null fields. The macro I am using is filtering out all records that contain a null field. Any suggestions on a tweek to correct this?

  8. #8
    Board Regular ranman256's Avatar
    Join Date
    Jun 2014
    Location
    Kentucky
    Posts
    1,622
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro comparison, why is one not working??

    the base query should ask for Non Null fields.

  9. #9
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    15,893
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Macro comparison, why is one not working??

    This construct might work to include Nulls:
    (NZ([SEARCH_MTR]![MTR],"") Like "*" & [Forms]![SEARCH_MTR]![Search-MTR] & "*")

    (if by including null you mean to include nulls only when the search criteria textbox is empty/blank/null.)

    Using: Office 2010/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  10. #10
    Board Regular
    Join Date
    Mar 2017
    Posts
    83
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Macro comparison, why is one not working??

    I am getting the apply filter macro action has an invalid value for the where condition argument.

    And yes, I am trying to include nulls when the the text box is blank. The MTR and HS fields contain several null values but there are no nulls in the thick field. So the goal is to be able to search by thickness and have no text entries in the other fields and return records that contain null values in either the MTR or HS fields on a search by thick


    (Nz([BALANCE_QUERY]![MTR],"") Like "*" & [Forms]![INV_SEARCH]![Search_MTR] & "*") And
    (Nz([BALANCE_QUERY]![HEAT/SLAB],"") Like "*" & [Forms]![INV_SEARCH]![Search_HS] & "*") And
    ([BALANCE_QUERY]![THICK] Like "*" & [Forms]![INV_SEARCH]![Search_Thick] & "*")

    I am thinking the Nz function did not need to be added to the last part of the argument as there are no null values in the thickness fields and trying to do so puts me over the 250 character limit. Dose anything stand out as incorrect on this one?

    Also, cant say thanks enough for the help on this, I have a pretty decent head for this kind of stuff so it always falls on me but accounting is my field. I have learned a lot of new stuff with this project so again, many thanks for the assistance.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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