invalid procedure call

Can you show us some sample data and what you want to extract?
 
The diagnostics and solution was mentioned in post #16.

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

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)
 
Last edited:
Are these real addresses? It there are I would advise that you edit your post and remove the attachment.

Upload only test data and it would be best to put it in a spreadsheet because the screenshot isn't clear.
 
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.
 
Last edited:
You're doing something similar in the query. What's the SQL for the query "New Site Address Search 1"?
 
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.
 
so should i write iif(Left([site address],InStr([site address],[name_eng])-2) is null, 0,1) ??
It gives out all 1 - -!
 
Last edited:
Try this with the first query. Remove all the other criteria and put =0 as the criteria under this one.
Code:
Len(Left([site address],InStr([site address],[name_eng])-2) & "")
 
When i put "WHERE Len(Left([site address],InStr([site address],[name_eng])-2) & "") = 0"
in the first query, it gives invalid procedure call again......

It makes me crazy .....
 
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];
 
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.
 
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.
 
Ok the problem is the kind of join you had in the query, well there wasn't any. I've sorted that out so look at both qrySearch1 and qrySearch2 and take a note of the LIKE join in qrySearch1.
 

Attachments

Users who are viewing this thread

Back
Top Bottom