Create one field from 3 using a function (1 Viewer)

[harrisw]

How can I create a function that receives 3 fields from a query but allows me to use the function so that I can return them as 1 field

There will be some code that willl play around with the data in the 3 fields in the function but I dont now how to pass 3 fields into a function.

Thanks

 

[Bat17]

function MyFunction(arg1 as Integer, arg2 as string, agr3 as string) as string

myFuntion = arg1 & arg2 & arg3

End function

HTH

Peter

 

[harrisw48]

I tried that using the code below

Public Function OneContact(strSalutation As String, strFirstname As String, strInitials As String, strSurname As String) As String

OneContact = strSalutation & strFirstname & strInitials & strSurname

End Function

When I try to use the OneContact function from a query I get the error

"Undefined Function in Expression"

Any ideas?

 

[workmad3]

not relevant to the problem but, are there spaces at the end of each of the strings being passed in? if not, then add them or you could get the strange result of

MrJoeTDBlogs

 

[workmad3]

oh... had an idea though (relevant this time)

you are definitely using the full name of the function arent you?

as in if its in a module called stuff you call it like

stuff.OneContact(str1, str2, str3, str4)

sorry its very basic, but sometimes this stuff is forgotten and is easily overlooked

 

[Bat17]

and make sure you have not named the module with the same name as the function!

Peter

 

[workmad3]

or the same name as the query

 

[Mile-O]

DOn't have the function in a module attached to a form or report - these are Class modules and the code is enclosed. Use a standalone module.

 

[ScottGem]

I tried that using the code below

Public Function OneContact(strSalutation As String, strFirstname As String, strInitials As String, strSurname As String) As String

OneContact = strSalutation & strFirstname & strInitials & strSurname

End Function

When I try to use the OneContact function from a query I get the error

"Undefined Function in Expression"

Any ideas?

My question is why bother with a function here? What is the difference between typing:

=Onecontact([Salutation],[Firstname],[Intials],[Surname]) or
=[Salutation] & " " & [Firstname] & " " & [Initials] & " " & [Surname]

Both produce the same result (assuming you add the spaces in your function).

 

[Mile-O]

What is the difference between typing:

=Onecontact([Salutation],[Firstname],[Intials],[Surname]) or
=[Salutation] & " " & [Firstname] & " " & [Initials] & " " & [Surname]

Not everyonbe has initials and there would then be a three space gap between fore- and surnames. In the function you can check each field for null values and create a "best fit" name.

 

[ScottGem]

Not everyonbe has initials and there would then be a three space gap between fore- and surnames. In the function you can check each field for null values and create a "best fit" name.

Yes, I agree but that's not the way the asker was coding it. In fact he was using no spaces at all. But its still not necessary to use a function.

=[Salutation] & " " & [Firstname] & " " & IIF(IsNull([Initials]),"",[Initials] & " ") & [Surname]

will compensate is no initials are entered. Granted its a little bit longer, but once entered the expression can be copied and pasted.

 

[workmad3]

Yes, I agree but that's not the way the asker was coding it. In fact he was using no spaces at all. But its still not necessary to use a function.

=[Salutation] & " " & [Firstname] & " " & IIF(IsNull([Initials]),"",[Initials] & " ") & [Surname]

will compensate is no initials are entered. Granted its a little bit longer, but once entered the expression can be copied and pasted.

yes... and copying and pasting removes the point of havin functions in the first place. You might as well write everything in one line and just 'copy and paste' every line of code that gets re used.

Putting it in a function allows him to have it in one place. makes the code more readable, and means if he wants to add any extra functionality (i.e. a switch to indicate whether to put in the first name or not so he can address ppl as [Salutation] & " " & [Surname] for more formal occaisions he justy needs to add a couple of lines in one place rather than having to remember every place he has used it in his code.

i.e. he should use a function rather than putting it in line for every reason that functions were invented in the first place

 

[ScottGem]

I don't disagree with you about functions being better. But look at the function that was used. It was one line! If you are going to create a function to use instead of an expression then it should preform some additional functionality.

 

[workmad3]

if you read what was originally said, he said there would be some code to play around with it in the function, he just wanted to know how to pass 3 values in and then he would play around with them in the function

 

[ScottGem]

You're right I did forget about that after going thru the rest of the thread.

Sorry.

 

[workmad3]

heh its ok

im just a pedantic b*****d at times, i dont mean anything by it

 

[ScottGem]

No offense taken. Had I not missed that part I wouldn't have answered like I did.