invalid procedure call
- Thread starter tytlwkltc
- Start date
but if the query has a wrong datatype, why the first query can still be run.
Also, i am no good in vba (i find savenumber2 on the net), can you help me on it please
Thank you very much!!!!!!!!!!!!Not the query, the parameter in the function pStr is String so it will fail if the value you assign it is Null. You can't assign Null to a String data type.but if the query has a wrong datatype, why the first query can still be run.
Also, i am no good in vba (i find savenumber2 on the net), can you help me on it please
Anyway, don't worry about that. When you show us some sample data of the address we can take it from there.
Here it is.
Firstly, i will match the fire street.
In fire street, there are different road no (from_no)
i would like to find out there last words equal to from_no. If it is, verify = 1
And I want to extract all the 1 in the query,
so that i can finally assign the unique code of the street address. (221-84-01)
Firstly, i will match the fire street.
In fire street, there are different road no (from_no)
i would like to find out there last words equal to from_no. If it is, verify = 1
And I want to extract all the 1 in the query,
so that i can finally assign the unique code of the street address. (221-84-01)
Last edited:
updated in xls file
1 more is that even if i just want to find out the front words
For example:
SELECT [New Site Address Search 1].[front words]
FROM [New Site Address Search 1]
WHERE [front words] = "NO.8";
It also gives the same problem.
So I am not sure the whether the function gives out the problem.
1 more is that even if i just want to find out the front words
For example:
SELECT [New Site Address Search 1].[front words]
FROM [New Site Address Search 1]
WHERE [front words] = "NO.8";
It also gives the same problem.
So I am not sure the whether the function gives out the problem.
Last edited:
Still a problem with your function. Your first query includes ALL records so it will bomb.
Your sample data isn't very good. Can you show us different variations of addresses.
Also, go back to your first query, remove the function and enter Is Null in the criteria for front_words. Tell me how many records.
Your sample data isn't very good. Can you show us different variations of addresses.
Also, go back to your first query, remove the function and enter Is Null in the criteria for front_words. Tell me how many records.
SELECT [New Site Address].[code no], [New Site Address].[Site RI no], [New Site Address].[site address], Left([site address],InStr([site address],[name_eng])-2) AS [front words], InStr([front words],",") AS comma, savenumer2(Replace((Right([front words],Len([front words])-comma)),"."," ")) AS [last words], iif([last words] = from_no, 1, 0) as verify, TPU.name_eng, TPU.from_no, TPU.to_no, TPU.tpu, TPU.sb, TPU.plot
FROM [New Site Address], TPU
WHERE Len(Left([site address],InStr([site address],[name_eng])-2) & "") = 0
ORDER BY [New Site Address].[code no];
FROM [New Site Address], TPU
WHERE Len(Left([site address],InStr([site address],[name_eng])-2) & "") = 0
ORDER BY [New Site Address].[code no];
Maybe I should post my original tables here. (sample)
I have some street address and i want to enter the code (TPU,SB,PLOT) if i can.
First, they should match the street and find out whether there are any street no match the street address. If the TPU does not contain the street number even it is the same street, just leave it and in this sample, i want only 2 records with street name and street number to be extracted.
I have some street address and i want to enter the code (TPU,SB,PLOT) if i can.
First, they should match the street and find out whether there are any street no match the street address. If the TPU does not contain the street number even it is the same street, just leave it and in this sample, i want only 2 records with street name and street number to be extracted.
Last edited:
We keep going back and forth on this issue. Can you upload a db that:
1. includes sample data
and
2. includes the function
and
3. includes the query that doesn't work
Make sure the query throws the same error in the db you're going to attach.
1. includes sample data
and
2. includes the function
and
3. includes the query that doesn't work
Make sure the query throws the same error in the db you're going to attach.