- Local time
- Today, 13:57
- Joined
- Feb 28, 2001
- Messages
- 27,183
I've got a question to ask that is strictly for completeness but I didn't see it asked in the thread. What is the data type of [House_Number]?
Run-time error '5' on subform filter (1 Viewer)
- Thread starter moishier
- Start date
GinaWhipp
AWF VIP
- Local time
- Today, 14:57
- Joined
- Jun 21, 2011
- Messages
- 5,899
Good point, however, running out of why this wouldn't work now that the other issue is resolved.
- Local time
- Today, 13:57
- Joined
- Feb 28, 2001
- Messages
- 27,183
My problem is this... moishier reports that an expression fails of the form
x > 6
But the right-hand side of that expression is a constant; the ">" operator is legal for both numbers and for strings; and thus the only thing that CAN go wrong is the left-hand side of the expression. Access does automatic type conversion on variables in VBA, but there are limits on what it can do in SQL, and that expression is going to be part of an SQL filter tacked onto the .Recordsource expression. So we need to know everything we can about the left-hand side in order to diagnose this problem.
It probably also wouldn't hurt to know what the actual .Recordsource looks like. Is it a table name, a query name, or an SQL statement? If the latter, what does it look like?
x > 6
But the right-hand side of that expression is a constant; the ">" operator is legal for both numbers and for strings; and thus the only thing that CAN go wrong is the left-hand side of the expression. Access does automatic type conversion on variables in VBA, but there are limits on what it can do in SQL, and that expression is going to be part of an SQL filter tacked onto the .Recordsource expression. So we need to know everything we can about the left-hand side in order to diagnose this problem.
It probably also wouldn't hurt to know what the actual .Recordsource looks like. Is it a table name, a query name, or an SQL statement? If the latter, what does it look like?
- Local time
- Today, 13:57
- Joined
- Feb 28, 2001
- Messages
- 27,183
Oh, that's a good question, Gasman, and having such a function would certainly muddy the waters. Though I might have expected a different error message. But in the context of SQL, one never knows quite what ACE will kvetch about first so it is worth asking.
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 range.
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 range.
GinaWhipp
AWF VIP
- Local time
- Today, 14:57
- Joined
- Jun 21, 2011
- Messages
- 5,899
Well, I know why house_number didn't work, it's not a part of anything I could find. Now on to the main issue...
Technically, not all the House Number's is not an actual numbers for all addresses, i.e. 0-174, 1C, 2G, etc. And that explains the *Data type mismatch* error message. However, if you switch the Filter to text you will not get the desired results. You are going to need an algorithm to assign those addresses that are not numeric an actual number though not sure how that will affect your results.
Hmm, and in thinking about it you incorporate the IsNumeric() Function to filter out those addresses but then your search (filter) would be omitting some results. So you would first need to decide what you want to do about the odd balls before doing anything else.
Technically, not all the House Number's is not an actual numbers for all addresses, i.e. 0-174, 1C, 2G, etc. And that explains the *Data type mismatch* error message. However, if you switch the Filter to text you will not get the desired results. You are going to need an algorithm to assign those addresses that are not numeric an actual number though not sure how that will affect your results.
Hmm, and in thinking about it you incorporate the IsNumeric() Function to filter out those addresses but then your search (filter) would be omitting some results. So you would first need to decide what you want to do about the odd balls before doing anything else.
- Local time
- Today, 14:57
- Joined
- Jan 23, 2006
- Messages
- 15,379
I agree with Gina. There are records with no data in address_1 and some have alphabetic char in leftmost position of address_1, so these will have to be dealt with. Also, "house numbers" (like telephone numbers) are not numbers in the sense that we do arithmetic operations on numbers. Perhaps some process to separate your data into specific groups/patterns and then code/algorithm to deal with each would be useful. Getting the data "cleansed" as early as possible in your approach would be more efficient than handling the mixture of good, bad/missing throughout.
Good luck with your project.
Good luck with your project.
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 column without any text. So why can't I sort it numerically or use it to compare another to another number, especially if I am wrapping it in the val() function?
From what I am reading online is seems to have something to do with the null records in the address_1 field in the master_list table. But I am filtering those out.
The result of the house_number column is a number-only column without any text. So why can't I sort it numerically or use it to compare another to another number, especially if I am wrapping it in the val() function?
From what I am reading online is seems to have something to do with the null records in the address_1 field in the master_list table. But I am filtering those out.
- Local time
- Today, 14:57
- Joined
- Jan 23, 2006
- Messages
- 15,379
A few findings from test2
masterlist original 17952
masterlist addr1 is null 2491
Left masterlist addr1 is alphabetic 31
masterlist addr1 starts numeric 15429
masterlist filtered 15428
Unique_Account_Number Address_1 City house_number
77755 5020
78190 21
You can sort on the house_number.
masterlist original 17952
masterlist addr1 is null 2491
Left masterlist addr1 is alphabetic 31
masterlist addr1 starts numeric 15429
masterlist filtered 15428
Unique_Account_Number Address_1 City house_number
77755 5020
78190 21
You can sort on the house_number.
I am filtering out those in the query:
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.
Code:
<>"" 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.
Last edited:
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 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?
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 enter data?
Thank you all for your help!!
Follow up question: How do I prevent null errors on this field, if the user does not enter data?
Code:
(house_number > " & Val([Forms]![bulk_add_complaints2]![start_number]) & ")- Local time
- Today, 14:57
- Joined
- Jan 23, 2006
- Messages
- 15,379
If a field (or a control on a form) is not mandatory, you can use the NZ() to create a value that you can control.
If the user must put in some value and dosn't, you could detect this and prompt the user to enter a value.
If the user must put in some value and dosn't, you could detect this and prompt the user to enter a value.
Not out of the woods yet. After the filter, there is an update statement. Here is the SQL:
I get an error: Invalid procedure call.
If I change it to a SELECT, it's fine.
If I remove the "house_number >= 3" filter (or add another text filter) it also works. What gives?
(I even removed all the null records from master_list.)
Code:
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 8:36:01 PM','test' FROM master_list_filtered WHERE (house_number >= 3)I get an error: Invalid procedure call.
If I change it to a SELECT, it's fine.
If I remove the "house_number >= 3" filter (or add another text filter) it also works. What gives?
(I even removed all the null records from master_list.)
Similar threads
