Recent content by moishier

Not out of the woods yet. After the filter, there is an update statement. Here is the SQL: INSERT INTO complaints(unique_account_number, reason_code,missing_from,missing_to,complaint_date,complaint_text) SELECT master_list_filtered.unique_account_number, 'DC','1/25/2019','1/25/2019','1/27/2019...

Ok, so I got this working by making a query to first filter out nulls. Then I loaded a query on top of that for my form which will do the filtering/comparing. That seems to work! Thank you all for your help!! Follow up question: How do I prevent null errors on this field, if the user does not...

Yes, so I updated those to have real addresses and it still won't work. I think the solution is to deal with the nulls. I need to support empty address_1 fields; should I just update the field to something? What is the best way to handle this? Is ZLS the same as null?

I am filtering out those in the query: <>"" And Is Not Null And Like "[0-9]*" You can sort on the house_number column with no changes? BTW, as soon as I delete the address_1 null records the sorting works for me.

See the attached db. I was using house_number as a new field on the master_list_filtered query to extract the number part of the street address. Just like I am doing in the VBA code without the house_number field in the previous database. The result of the house_number column is a number-only...

Thanks, attached is the database (zipped). I tried adding the same filter to the VBA and it gives me the same error. The objective is to use the form to filter for the start number of the address so that I can only filter the subform and only display addresses who's numbers match a specific...

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.

[house_number] > 6

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.

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

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],"...

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

So far, so good! Thanks very much for you help!

Okay, I'm making lots of progress. I have the filter working in vb, and the insert statement working. I am having some trouble with 2 things: 1. Getting an integer sum of records returned by the filter and then using it in the msgbox (in place of the X) 2. Adding a validation rule that you...

The query itself is not filtered - it gets filtered by the top part of the form. I want to insert the results of the filter into a table. My VBA for the event is like this so far, but it does nothing: Private Sub add_complaints_Click() CurrentDb.Execute "INSERT INTO...