Type Mismatch error

  • Thread starter Thread starter Karen
  • Start date Start date
K

Karen

I have a textbox that I am trying to assign a value to:

txtFullAddress.Value = FullAddress(txtAddress.Value, txtAddress2.Value, _
txtCity.Value, txtState.Value, txtZip.Value)

where FullAddress is a module

Public Function FullAddress(Address As String, _
Address2 As String, City As String, _
State As String, Zip As String) As String

FullAddress = IIf(IsNull(Address), "", Address) & _
IIf(IsNull(Address2), "", vbCrLf & Address2) & _
IIf(IsNull(City), "", vbCrLf & City) & _
IIf(IsNull(State), "", ", " & State) & _
IIf(IsNull(Zip), "", " " & Zip)

End Function

I have also tried to change the function to take the passing arguments as variants in case null was messing it up. No matter what I get a 'type mismatch' error on the line of code at the very top that assigns the value.

Any ideas?
 
By the way, if I forgo the function and use the following in the VBA on the form, everything is happy.

txtFullAddress.Value = IIf(IsNull(txtAddress.Value), "", txtAddress.Value) & _
IIf(IsNull(txtAddress2.Value), "", vbCrLf & txtAddress2.Value) & _
IIf(IsNull(txtCity.Value), "", vbCrLf & txtCity.Value) & _
IIf(IsNull(txtState.Value), "", ", " & txtState.Value) & _
IIf(IsNull(txtZip.Value), "", " " & txtZip.Value)

I would prefer to use the function because I'm going to be building a lot of these addresses on several forms

Karen
I have a textbox that I am trying to assign a value to:

txtFullAddress.Value = FullAddress(txtAddress.Value, txtAddress2.Value, _
txtCity.Value, txtState.Value, txtZip.Value)

where FullAddress is a module

Public Function FullAddress(Address As String, _
Address2 As String, City As String, _
State As String, Zip As String) As String

FullAddress = IIf(IsNull(Address), "", Address) & _
IIf(IsNull(Address2), "", vbCrLf & Address2) & _
IIf(IsNull(City), "", vbCrLf & City) & _
IIf(IsNull(State), "", ", " & State) & _
IIf(IsNull(Zip), "", " " & Zip)

End Function

I have also tried to change the function to take the passing arguments as variants in case null was messing it up. No matter what I get a 'type mismatch' error on the line of code at the very top that assigns the value.

Any ideas?
 
Karen said:
I have a textbox that I am trying to assign a value to:

txtFullAddress.Value = FullAddress(txtAddress.Value,
txtAddress2.Value, _
txtCity.Value, txtState.Value, txtZip.Value)

where FullAddress is a module

Public Function FullAddress(Address As String, _
Address2 As String, City As String, _
State As String, Zip As String) As String

FullAddress = IIf(IsNull(Address), "", Address) & _
IIf(IsNull(Address2), "", vbCrLf & Address2) & _
IIf(IsNull(City), "", vbCrLf & City) & _
IIf(IsNull(State), "", ", " & State) & _
IIf(IsNull(Zip), "", " " & Zip)

End Function

I have also tried to change the function to take the passing
arguments as variants in case null was messing it up. No matter what
I get a 'type mismatch' error on the line of code at the very top
that assigns the value.

Any ideas?
Click to expand...

Is txtZip by any chance bound to a number field?
 
Dirk,

Maybe another clue is that when I start typing

txtfulladdress.value = fulladdress(

it doesn't show the argument lookaheads that I have in the function.

I mean I have another function

Public Function FullName(FirstName As Variant, _
MiddleName As Variant, LastName As Variant) As String

FullName = IIf(IsNull(FirstName), "", FirstName) & _
IIf(IsNull(MiddleName), "", " " & MiddleName) & _
IIf(IsNull(LastName), "", " " & LastName)

End Function

and when I start typing to use it

txtfullname.value = fullname(

I get the listing of the function's lookahead arguments with
'FirstName,MiddleName,LastName' displayed.

Maybe if I could figure out why the FullAddress lookahead isn't working then
I could figure out what is wrong.

Karen
 
Karen said:
Dirk

No, I just looked, it is a text field. I would have felt so foolish!!

Karen
Click to expand...

Odd, I'd expect you to get the error "Invalid use of Null", not "Type
mismatch". Try changing your function's declaration to:

Public Function FullAddress(Address As Variant, _
Address2 As Variant, City As Variant, _
State As Variant, Zip As Variant) As String

and tell me what happens.
 
Karen said:
Dirk,

Maybe another clue is that when I start typing

txtfulladdress.value = fulladdress(

it doesn't show the argument lookaheads that I have in the function.

I mean I have another function

Public Function FullName(FirstName As Variant, _
MiddleName As Variant, LastName As Variant) As String

FullName = IIf(IsNull(FirstName), "", FirstName) & _
IIf(IsNull(MiddleName), "", " " & MiddleName) & _
IIf(IsNull(LastName), "", " " & LastName)

End Function

and when I start typing to use it

txtfullname.value = fullname(

I get the listing of the function's lookahead arguments with
'FirstName,MiddleName,LastName' displayed.

Maybe if I could figure out why the FullAddress lookahead isn't
working then I could figure out what is wrong.
Click to expand...

Hmm ... I wonder if you have multiple declarations of this function.
Try searching your whole project for "FullAddress" and see what you
find.
 
Dirk,

Same error. Also I still don't get that lookahead when I start referencing
the function..........that's gotta be part of the problem.

Karen
 
Dirk,

Nope, I just did a search and only see one function with that name.

Also I just started this today so I'm not surprised it's the only one. I've
saved, closed and re-opened the db a few times but it doesn't change that
symptom.

Karen
 
Dirk,

I got it!!

I renamed the function BuildAddress and voila!, it works. I get the
lookahead and no type mismatch.

There is a field in the table that is 'FullAddress'. It is a text field.
Is that why there was a problem?

Karen
 
It sounds like you used "FullAddress" as the Function name
as well as the *Module* name that contains the function.

Functions & Modules share the same name space so Access
will get very confused if you use the same name for both.
To avoid this, I always use the prefix "vba" (or "mdl")
for Modules and "fn" for functions.

HTH
Van T. Dinh
MVP (Access)
 
Karen said:
Dirk,

I got it!!

I renamed the function BuildAddress and voila!, it works. I get the
lookahead and no type mismatch.

There is a field in the table that is 'FullAddress'. It is a text
field. Is that why there was a problem?
Click to expand...

I'll bet it is! You could find out by renaming or deleting the
FullAddress field and renaming the function back to FullAddress.

Are you saying you have a field FullAddress containing the same data
that is also present in the separate fields Address, Address2, City,
State, and Zip? That's what this function is for, to create the value
to be assigned to that field? Then may I ask, is there ever any
circumstance in which the FullAddress field should *not* equal the
result of the function? If not, then why have the field in your table
at all? You can always calculate the full address by calling the
function, passing it the base fields. If you want to display the full
address in a text box, you can always use a controlsource expression:

=FullAddress([Address],[Address2],[City],[State],[Zip])

And if you need the full address in a query, you can define it as a
calculated field:

SELECT ...
FullAddress(Address,Address2,City,State,Zip)
As ContactAddress
FROM Contacts;

For most purposes this would be an improvement over having the same data
stored in more than one field in your table, a situation that wastes
space and is prone to data-consistency errors.
 
Thanks Van,

The module however was named basAddress and that's a habit I picked up
somewhere else. I don't even know why the original source use the 'bas'
prefix. 'mdl' would actually make a lot more sense, perhaps I shall now
develop a new habit. Thanks again.

Karen
 
Karen said:
Thanks Van,

The module however was named basAddress and that's a habit I picked up
somewhere else. I don't even know why the original source use the
'bas' prefix. 'mdl' would actually make a lot more sense, perhaps I
shall now develop a new habit. Thanks again.
Click to expand...

"bas" for Visual Basic. I tend to use that myself.
 
Dirk,

Well, I renamed the field in the table and changed the name of the function
back to FullAddress and it was broken again. I even checked if FullAddress
could somehow be a keyword but no go.

I like your suggestions. I was building the field because I'll be linking
it to Word docs in a mail merge and thought it would be faster to just grab
the pre-calculated field for that.

Thanks again for the help. I just get stuck sometimes and having someone to
bounce off of really helps.

Karen

Dirk Goldgar said:
Karen said:
Dirk,

I got it!!

I renamed the function BuildAddress and voila!, it works. I get the
lookahead and no type mismatch.

There is a field in the table that is 'FullAddress'. It is a text
field. Is that why there was a problem?
Click to expand...

I'll bet it is! You could find out by renaming or deleting the
FullAddress field and renaming the function back to FullAddress.

Are you saying you have a field FullAddress containing the same data
that is also present in the separate fields Address, Address2, City,
State, and Zip? That's what this function is for, to create the value
to be assigned to that field? Then may I ask, is there ever any
circumstance in which the FullAddress field should *not* equal the
result of the function? If not, then why have the field in your table
at all? You can always calculate the full address by calling the
function, passing it the base fields. If you want to display the full
address in a text box, you can always use a controlsource expression:

=FullAddress([Address],[Address2],[City],[State],[Zip])

And if you need the full address in a query, you can define it as a
calculated field:

SELECT ...
FullAddress(Address,Address2,City,State,Zip)
As ContactAddress
FROM Contacts;

For most purposes this would be an improvement over having the same data
stored in more than one field in your table, a situation that wastes
space and is prone to data-consistency errors.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Karen said:
Dirk,

Well, I renamed the field in the table and changed the name of the
function back to FullAddress and it was broken again. I even checked
if FullAddress could somehow be a keyword but no go.
Click to expand...

Hmm. It could just be due to Access "remembering" the old definition of
FullAddress, or you may have to reset the form's recordsource to make it
rebuild the form's property list. On the other hand, if you've got a
solution that works, you may decide not to bother.
I like your suggestions. I was building the field because I'll be
linking it to Word docs in a mail merge and thought it would be
faster to just grab the pre-calculated field for that.
Click to expand...

I'd suggest basing your mail merge on a query that defines the
calculated field.
Thanks again for the help. I just get stuck sometimes and having
someone to bounce off of really helps.
Click to expand...

You're very welcome. I know exactly what you mean.
 
Back
Top