Macro comparison, why is one not working??

[TDC21]

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?

 

[ranman256]

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

(the control could be misspelled than what you think it is)

 

[TDC21]

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.

 

[xenou]

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.

 

[TDC21]

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.

 

[xenou]

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.

 

[TDC21]

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?

 

[ranman256]

the base query should ask for Non Null fields.

 

[xenou]

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.)

 

[TDC21]

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.