Invalid Procedure Call Len Function

[Hayles]

Hi,

I'm trying to write a formula that will return the first part of a postcode (since there could be 2-4 characters here, I have to drop the last three then use trim to get rid of gaps). There are some null values in the results, but when using what I currently have or IIF to get rid of the Null values, the OBDC link that I'm trying to generate in excel returns the error message: [Microsoft][OBDC Microsoft Access Driver] Invalid Procedure Call
I can't use NZ as this doesn't work in excel. I also get the 'Invalid Procedure Call' in a later query but not in the one below where the error is being caused:

SELECT [MIV Prep Query].[Unique ID], [MIV Prep Query].[Order Number], [MIV Prep Query].[Order Type], [MIV Prep Query].[Customer Account Number], [MIV Prep Query].[Customer name] AS [Customer Name], [MIV Prep Query].[Customer Reference], [MIV Prep Query].Postcode, Trim(Mid([Postcode],1,Len([Postcode])-3)) AS [Part Postcode], "" AS Branch, CarPlus_MIV.MIV AS [Add'n Detail], IIf(IsNull([MIV]),"Other","MIV") AS Type, [MIV Prep Query].Date
FROM [MIV Prep Query] LEFT JOIN CarPlus_MIV ON [MIV Prep Query].[MIV ID] = CarPlus_MIV.[Unique ID];


Any idea where I'm going wrong? It definitely seems to be to do with "Trim(Mid([Postcode],1,Len([Postcode])-3)) AS [Part Postcode]" as when I take this out, everything works ok.

Thanks in advance!

 

[plog]

Divide and conquer. Make a copy of your query and then yank out all fields that aren't using functions. Then run those fields seperately. I see 2 fields in your query that use functions:

IIf(IsNull([MIV]),"Other","MIV") AS Type
Trim(Mid([Postcode],1,Len([Postcode])-3)) AS [Part Postcode]

Run them seperately to see which of those 2 are causing errors. If its the second one, break it down into each function to see which function is causing it. My guess is that Len() is returning a null or negative number which causes Mid() to fail.

 

[pr2-eugin]

The problem would be Mid function. When the PostCode is Null, the Mid function will get the argument -3, which is invalid length. Try something like..

Trim(IIF(Len([Postcode] & '') = 0, '', Mid([Postcode], 1, Len([Postcode])-3))) AS [Part Postcode]

 

[jdraw]

Can you start with a simple query that just selects the Postal code and the first part of the postal code? Get it working to understand how the various pieces work, then move to a more complex query.

Please show us a few examples of the Postal codes you are working with.

 

[Hayles]

Thanks for the quick replies. It does appear to be the MID not working when the field data has less than 3 characters.

I don't seem to be able to use IIF etc. to get rid of the errors in the same query as above. I've went back to an earlier query and used criteria on the original full postcode (Like "[a-z]??*") to cleanse the data, then my later formula works. I'll then need to sort out anything that this criteria doesn't like (I'm thinking of using: Not Like "[a-z]??*").

I guess it's correct to sort the incorrect source data but it's a bit of a pain as I'm sure everyone knows how difficult it is to get inputs correct (I've got about 7,500 transactions a day coming in), fortunately it's a very low percentage that don't have a valid postcode. So unless anyone has any other ideas I think this should work for me!

Thanks again.

 

[plog]

Roll your calculated query field into a function, pass the function the [Postcode] field and handle it appropriately:

PartPostCode: getPartPostCode([Postcode])

Then you create a function to handle all the cases you will encounter:

Function getPartPostCode(in_PostCode) As String
   ' converts postcode (in_PostCode) to correct Part Postcode

Dim ret                         ' return value of function
ret="Invalid Post Code"    ' default value is error message

' Put your logic below, for now its a simple length test

If Len(in_PostCode)>3 Then ret="Valid Post Code" 

getPartPostCode = ret

End Function

This way you can have it work for every PostCode without having to filter it.