Using iif() wtih different data types - Text & Memo

[Mimo]

Hi,

I have 2 tables and I'm trying to run a query that runs iff() statements by
comparing columns from each table and returning True or False if they are the
same.

e.g. Using tables Person and User -

compare_Name:iif([Person].[F_Name]= [User].[Name],TRUE, FALSE)

The problem I am having is that [Person].[F_Name] is a text field and
[User].[Name] is a memo field. My question is can I use type casting to help
me compare the fields, and if so how?

Thankx

Mimo

 

[Douglas J. Steele]

Try the CStr function or the Left function:

compare_Name:iif([Person].[F_Name]= CStr([User].[Name]),TRUE, FALSE)

or

compare_Name:iif([Person].[F_Name]= Left([User].[Name], 255),TRUE, FALSE)

Why on earth would you use the Memo data type for a name field?

 

[Mimo]

Hi

I will try your suggestion.
I used an example below to illustrate my point, don't worry it's not a name
field.

Thanx

Try the CStr function or the Left function:

compare_Name:iif([Person].[F_Name]= CStr([User].[Name]),TRUE, FALSE)

or

compare_Name:iif([Person].[F_Name]= Left([User].[Name], 255),TRUE, FALSE)

Why on earth would you use the Memo data type for a name field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I have 2 tables and I'm trying to run a query that runs iff() statements
by
comparing columns from each table and returning True or False if they are
the
same.

e.g. Using tables Person and User -

compare_Name:iif([Person].[F_Name]= [User].[Name],TRUE, FALSE)

The problem I am having is that [Person].[F_Name] is a text field and
[User].[Name] is a memo field. My question is can I use type casting to
help
me compare the fields, and if so how?

Thankx

Mimo

 

[John Spencer]

Why use IIF at all?
Person.F_Name = User.Name will return true, false or null, depending
on the values in the fields.

What do you want to see if F_Name and User.Name are both null? You
aren't going to get true.

If you want the nulls to be considered equal then you need IIF or some
other construction

IIF(Person.FName is null And User.Name is Null,True,
Person.F_Name=User.Name)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi

I will try your suggestion.
I used an example below to illustrate my point, don't worry it's not a name
field.

Thanx

Try the CStr function or the Left function:

compare_Name:iif([Person].[F_Name]= CStr([User].[Name]),TRUE, FALSE)

or

compare_Name:iif([Person].[F_Name]= Left([User].[Name], 255),TRUE, FALSE)

Why on earth would you use the Memo data type for a name field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hi,

I have 2 tables and I'm trying to run a query that runs iff() statements
by
comparing columns from each table and returning True or False if they are
the
same.

e.g. Using tables Person and User -

compare_Name:iif([Person].[F_Name]= [User].[Name],TRUE, FALSE)

The problem I am having is that [Person].[F_Name] is a text field and
[User].[Name] is a memo field. My question is can I use type casting to
help
me compare the fields, and if so how?

Thankx

Mimo

 

[Mimo]

Hi John,

Both Person.F_Name and User.Name are not set as Required fields, I should've
spotted that nulll earlier.

Will now try and include that check as well, in the iff, as well as checking
the fields equality.

Thanx

Mimo

Why use IIF at all?
Person.F_Name = User.Name will return true, false or null, depending
on the values in the fields.

What do you want to see if F_Name and User.Name are both null? You
aren't going to get true.

If you want the nulls to be considered equal then you need IIF or some
other construction

IIF(Person.FName is null And User.Name is Null,True,
Person.F_Name=User.Name)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hi

I will try your suggestion.
I used an example below to illustrate my point, don't worry it's not a name
field.

Thanx

Try the CStr function or the Left function:

compare_Name:iif([Person].[F_Name]= CStr([User].[Name]),TRUE, FALSE)

or

compare_Name:iif([Person].[F_Name]= Left([User].[Name], 255),TRUE, FALSE)

Why on earth would you use the Memo data type for a name field?

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hi,

I have 2 tables and I'm trying to run a query that runs iff() statements
by
comparing columns from each table and returning True or False if they are
the
same.

e.g. Using tables Person and User -

compare_Name:iif([Person].[F_Name]= [User].[Name],TRUE, FALSE)

The problem I am having is that [Person].[F_Name] is a text field and
[User].[Name] is a memo field. My question is can I use type casting to
help
me compare the fields, and if so how?

Thankx

Mimo