IIf(IsError(

[PanzerAttack]

Hi all,

I'm sure I used to be quite good at this but I appear to have forgotten everything :banghead:

I'm having trouble with #Error in a query where I'm trying to bring in the Account Name with the Outcode at the end.

When it errors, I just want the Account Name.

This is what I have so far:

Name: IIf(IsError([Account Name] & " " & [Outcode]),[Account Name],[Account Name] & " " & [Outcode])


thank you

Anthony

 

[Cronk]

That won't work. What is the sql for the query you are trying to run?

 

[PanzerAttack]

Hi Cronk,

Sorry for the delay, totally lost my access to our system ahhhhhh

Is this what you need?

SELECT IIf(IsError([Account Name] & " " & [Outcode]),[Account Name],[Account Name] & " " & [Outcode]) AS AccountName, Left([Postcode],(InStr(1,[Postcode]," "))-1) AS OutCode
FROM CRM;

 

[Cronk]

The IsError is a vba function and not working within the query. What is the error you are trying to detect? Do you have nulls in any of your postcode or account code records that are giving the error in the first place? You also are defining a query output field OutPost which is the same name as the table field.

 

[PanzerAttack]

Hi Cronk,

I have a company name in 2 applications that don't match. I want to take the first 12 letters of the Account Name and add on the first part of the Post Code and then compare the 2.

The data in the postcode is terrible and sometimes unpopulated. Where it's unpopulated I can use the Isnull which works but I still get errors where the Postcode is either a number 1223443 or have no space EH19BW.

Where I have an error I would like to return the Account Name.

thank you again.

 

[Cronk]

What happens if the Account name is less than 12 characters? How do you define "first part" of post code?

Anyway, it's better to trap errors and make changes accordingly in a function used by your query.

Select CompositeAcctName(AccountName, PostCode) from tblYourTable

The function would be something like

function CompositeAcctName(AcctName, PCode)
dim str as string, n as integer
str = left(AcctName & space(12),12)
n= instr(PCode & ""," ")
if n>0 then
str = str&left(PCode, n-1)
else
...
endif
CompositeAcctName = str
end function

 

[PanzerAttack]

Thanks Cronk,

I have put this into a Module but get stuck on this line when I debug

str = str&left(PCode, n-1)

I've not done a module before, yikes.

Anthony

 

[Cronk]

Try
str = str & left(PCode, n-1)
ie with spaces around the ampersand

Using VBA judiciously in your application can enhance functionality immensely, so it's a very worthwhile investment of your time.