Run-time error '5' on subform filter (1 Viewer)

[moishier]

Hi,

I have a form that has a subform with a filter. VBA is this:

Me.Filter = "([house_number] > " & [Forms]![bulk_add_complaints2]![start_number] & ")"
Me.FilterOn = True
Me.Requery

The input name is start_number and the database field is house_number.

When I press the button for the filter I get:

Run-time error '5':
Invalid procedure call or argument

What am I doing wrong? I made sure the field returned in the query and subform is a number field so I can compare integers.

Thanks!

 

[MajP]

To debug, can you do the following and post back?

dim strFilter as string
strFilter = "([house_number] > " & [Forms]![bulk_add_complaints2]![start_number] & ")"
debug.print strFilter
'post back what you get
me.filter = strFilter
Me.FilterOn = True
Me.Requery

Also which line does it fail on.

 

[GinaWhipp]

Hmm, wondering if it's because House Number can contain no value? Greater than nothing might produce an error. Maybe wrap House Number in Nz(), i.e. Nz([house_number],0)

 

[Micron]

Try it without the parentheses (). To me, the syntax posted looks like function parameters or the other thing whose syntax I can't qualify at the moment and don't know how I'd begin to research it. I'm referring to syntax that refers to a collection, such as Me.Controls("txtMyTextbox") instead of Me.txtMyTextbox.

 

[The_Doc_Man]

I think Gina called it. Check your dataset for a null or empty [House_Number].

 

[moishier]

The solutions offered here did not work, but it seems the issue is in the query that this form writes to. I'm extracting the house number from the street addess like this:

house_number: Val(IIf([Address_1] Is Null Or [Address_1]='',0,Replace(Left([Address_1],(InStr(1,[Address_1]," "))-1),"-","")))

If I try to sort this, I get an error 'Invalid procedure call'. So something is wrong with the data in this query. Any ideas?

 

[Micron]

So something is wrong with the data in this query.

Maybe encountering Nulls as already suggested? If a Null is encountered, trying to subtract 1 will raise this error.
EDIT:
once that is figured out you will still have errors because

a) there is no default/alternate value for the IIF expression (there has to be one where the asterisk is below) and

b) I don't think it will accept 2 single quotes that you are using to denote an empty string.

house_number = Val(IIf(Address_1 Is Null Or Address_1 = [COLOR=red]""[/COLOR], Replace(Left(Address_1, InStr(1, Address_1, " ") - 1), "-", ""), [COLOR=purple][B]*[/B][/COLOR]))

Note that this isn't something that will work in vb code as an expression. I'm basing the guesstimation on it being for a query, as you posted.

 

[GinaWhipp]

It will always generate an error if the result is empty or null. You need to resolve that first which you can do with 0 (zero) and then use conditional formatting to hide the 0.

@Micron... it will take single marks instead of quotation marks. I use them all the time because in VBA, depending on what you are doing you have to use single marks as the regular quotation marks will cause an error. They are interchangeable.

 

[Micron]

Don't forget this is supposed to be a query, not vba.

 

[moishier]

It does not seem to be nulls. I created a new query that checks for nulls and empty strings, and based the original query on that. Then I simplified the query:

house_number: Val(Left([Address_1],(InStr(1,Replace([Address_1],"-","")," "))-1))

It still gives me an error when I sort the query on this field ascending.

 

[GinaWhipp]

Don't forget this is supposed to be a query, not vba.

True, but still interchangeable. I know I use single quotes there as well.

 

[GinaWhipp]

Check for nulls or empty? Does it filter them out?

 

[moishier]

Okay, I fixed the query sort - there were two records that had a partial address, no space character, so I excluded them and the sorting works now.

I am still getting Run-time error '5' on the filter in VBA.

 

[GinaWhipp]

What does *debug.print strFilter* show in the Immediate Window?

 

[moishier]

[house_number] > 6

 

[GinaWhipp]

Okay and if you put the *>6* in the record source does it produce the desired results?

 

[Micron]

True, but still interchangeable. I know I use single quotes there as well.

So do I, but never have used single quotes to create an empty string (that I can recall). You sure that works?

 

[moishier]

Okay and if you put the *>6* in the record source does it produce the desired results?

I get an error when I do this:

Datatype mismatch if criteria expression.

I'm getting this even when adding the Nz function:

house_number: Val(Nz(Left([Address_1],(InStr(1,Replace([Address_1],"-","")," "))-1),0))

I have the same issue even when first filtering out null on that field in a query.

 

[GinaWhipp]

Hmm, I'm thinking that something is translating the 6 to text. Try...

strFilter = "([house_number] > " & Val([Forms]![bulk_add_complaints2]![start_number]) & ")"

 

[The_Doc_Man]

Gina, presuming that moishier correctly reported the output from the Debug.Print, it did not convert the 6 to text because if it had, Debug.Print would have reported > "6"