invalid procedure call

[tytlwkltc]

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 ((([New Site Address].[site address]) Like "*" & [name_eng] & "*"))
ORDER BY [New Site Address].[code no];

site address is the address of the site (eg. 2-20 PALM STREET, FLAT A, 4/F & 5/F K.I.L. 10988)
and i want to extract the first street number (2)
if it is equal to the from_no, verify gives out 1.

In the next query, i want to extract all verify = 1 record -->

Select *
From [New Site Address Search]
Where Verify = 1;

But it says I have a invalid procedure call.
How to solve the problem?
(New Site Address Search can run successfully)

 

[GinaWhipp]

Is that an exact copy and paste of the SQL of your query? If not, please do a copy/paste of the SQL here (and please use code tags). Does the first query run?

 

[tytlwkltc]

Yes it is and the first query runs.

 

[GinaWhipp]

Try...

Select *
From [New Site Address Search]
Where Verify = "1";

Maybe it thinks the one is Text.

 

[tytlwkltc]

I give you a detailed example here... (Site RI no is private)

 

[tytlwkltc]

Try...

Select *
From [New Site Address Search]
Where Verify = "1";

Maybe it thinks the one is Text.

NO, it can't work.

 

[GinaWhipp]

Oops, just realized my error, try...

Select [New Site Address Search].*
From [New Site Address Search]
Where Verify = 1;

 

[tytlwkltc]

Oops, just realized my error, try...

Select [New Site Address Search].*
From [New Site Address Search]
Where Verify = 1;

Not works.
What a strange problem :banghead:

 

[GinaWhipp]

What is [New Site Address Search]?

 

[tytlwkltc]

The first query

 

[MarkK]

But the first query requires a parameter. How is that supplied when the second query is run? The function call SaveNumer2() may fail if the first query returns zero records.

 

[tytlwkltc]

But the first query requires a parameter. How is that supplied when the second query is run? The function call SaveNumer2() may fail if the first query returns zero records.

SaveNumer2(XXXX) is a function that remove everything except the first number . I have tried not using the function before but the problem remains.
I am not sure where after using instr(), the query can't use "WHERE" anymore.

 

[JHB]

Create a query (based on the first query) using the QBE window, and see what you get now.

 

[tytlwkltc]

Create a query (based on the first query) using the QBE window, and see what you get now.

The same...invalid procedure call

 

[JHB]

The same...invalid procedure call

Okay - only for nailing the problem down, I would save the result from the first query to a temp table and run the second query on that table.
I think the second query runs okay then!
I expect the problem is that you create a field name ([last words]) in the query and use the result from it to create another field name (verify).
I would change the "savenumer2" function to return 1 or 0 and remove the " iif([last words] = from_no, 1, 0) " part from the query.

savenumer2(Replace((Right([front words],Len([front words])-comma)),"."," ")) AS [last words], iif([last words] = from_no, 1, 0) as verify,

 

[vbaInet]

But the first query requires a parameter. How is that supplied when the second query is run? The function call SaveNumer2() may fail if the first query returns zero records.

savenumer2(Replace((Right([front words],Len([front words])-comma)),"."," ")) AS [last words]

It's an invalid procedure call error so like MarkK rightly said, you're passing a parameter that contains the wrong datatype (mostly likely Null) whereas the declared datatype in the function itself is most likely String.

So within the function change the datatype of the parameter to Variant, perform a Null check and return some value. Or perform the Null check in the query before passing it to the function. My preference would be the latter.

 

[GinaWhipp]

And, if you've tried everything posted and still getting the message, please post the savenumer2() Function.

 

[tytlwkltc]

Option Compare Database

Function SaveNumer2(ByVal pStr As String) As Long
Dim strHold As String
Dim intLen As Integer
Dim n As Integer

strHold = Trim(pStr)
intLen = Len(strHold)
n = 1
Do
If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) Then
strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
n = n - 1
End If
n = n + 1
Loop Until Val(strHold) > 0
SaveNumer2 = Val(strHold)

End Function

 

[vbaInet]

The diagnostics and solution was mentioned in post #16.

 

[tytlwkltc]

Option Compare Database

Function SaveNumer2(ByVal pStr As String) As Long
Dim strHold As String
Dim intLen As Integer
Dim n As Integer

strHold = Trim(pStr)
intLen = Len(strHold)
n = 1
Do
If Mid(strHold, n, 1) <> " " And Not IsNumeric(Mid(strHold, n, 1)) Then
strHold = Left(strHold, n - 1) + Mid(strHold, n + 1)
n = n - 1
End If
n = n + 1
Loop Until Val(strHold) > 0
SaveNumer2 = Val(strHold)

End Function

Or maybe I should tell more on what I'm doing here. Please help me with another method if i can't fix the problem afterwards.

I have different street address in my tables and i want to match their codes by street number. Firstly i will read the words before the address (including space) --> [front words]. Then i will search the place of the commas (it will return 0 if the street number is at the front), then, SaveNumer2 will just display the first number between comma and the road --> [last words]. Finally, if the number found in the query equal the street number, it will return 1 and 0 otherwise.