VBA code in query for new field?

Hi -

I need to run a query for a mail merge and I need a field that will either
return “her� or “him� depending on my “sex� field. My sex field is already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

Perfect!!! Thank you!



"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

Hi John,

I have a question along the same lines as the question below, that you
answered perfectly for me!! thanks again for helping...

I need to address my letter, and I have two options, "first" name or
"second" name. if there is a "second" name listed, I want to use that name,
but if there is not a "second" name listed, I want to use the "first" name
listed. So this is what I wrote, based on the "himher" code. But it doesn't
return anything, and also asked for a parameter for the "first" name, but I
think I did something different then.

can you advise what I did incorrectly? Also, what do the last quotes " "
mean?

Thanks again, bluesky

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
Null,[second_name]," "))

"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

You test for Null values using Is Null and Is Not Null

addressee: IIf([second_name] IS Null,[first_name],IIf([second_name] IS Not
Null,[second_name]," "))

An easier solution is to use the NZ function

Addressee: NZ([Second_Name],[First_Name])
This will return Second_Name unless it is null. If it is null, then the
value of First_Name is returned. If both are null, then it returns null
(the value of First_Name).

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

"bluesky" wrote in message
...
Hi John,

I have a question along the same lines as the question below, that you
answered perfectly for me!! thanks again for helping...

I need to address my letter, and I have two options, "first" name or
"second" name. if there is a "second" name listed, I want to use that
name,
but if there is not a "second" name listed, I want to use the "first" name
listed. So this is what I wrote, based on the "himher" code. But it
doesn't
return anything, and also asked for a parameter for the "first" name, but
I
think I did something different then.

can you advise what I did incorrectly? Also, what do the last quotes " "
mean?

Thanks again, bluesky

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
Null,[second_name]," "))

"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will
either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

Hi John,

Thanks again, I'll try it as soon as I get to work. Well, I almost got it
correct, thanks to your earlier email!! I'm learning!

-bluesky

"John Spencer" wrote:

You test for Null values using Is Null and Is Not Null

addressee: IIf([second_name] IS Null,[first_name],IIf([second_name] IS Not
Null,[second_name]," "))

An easier solution is to use the NZ function

Addressee: NZ([Second_Name],[First_Name])
This will return Second_Name unless it is null. If it is null, then the
value of First_Name is returned. If both are null, then it returns null
(the value of First_Name).

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

"bluesky" wrote in message
...
Hi John,

I have a question along the same lines as the question below, that you
answered perfectly for me!! thanks again for helping...

I need to address my letter, and I have two options, "first" name or
"second" name. if there is a "second" name listed, I want to use that
name,
but if there is not a "second" name listed, I want to use the "first" name
listed. So this is what I wrote, based on the "himher" code. But it
doesn't
return anything, and also asked for a parameter for the "first" name, but
I
think I did something different then.

can you advise what I did incorrectly? Also, what do the last quotes " "
mean?

Thanks again, bluesky

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
Null,[second_name]," "))

"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will
either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

Hi John,

Thanks again, both worked, IIF and NZ!!!.

But, something really stranges happens when I use NZ instead of IIF for
"addressee". When I go to merge the query in Word for a letter, and I "Open
Data Source" the query with the NZ funtion does not show up!! All my other
queries do and if use the IIF function for "addressee," that shows up. BTW,
I also have the Him/Her IIF query in there as well. Is there a problem with
using both IIF and NZ in a query. I can run the query (NZ and IIF) in Access
and it is fine, but just doesn't show up in Word.....weird...any thoughts.

Thanks,

bluesky

"John Spencer" wrote:

You test for Null values using Is Null and Is Not Null

addressee: IIf([second_name] IS Null,[first_name],IIf([second_name] IS Not
Null,[second_name]," "))

An easier solution is to use the NZ function

Addressee: NZ([Second_Name],[First_Name])
This will return Second_Name unless it is null. If it is null, then the
value of First_Name is returned. If both are null, then it returns null
(the value of First_Name).

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

"bluesky" wrote in message
...
Hi John,

I have a question along the same lines as the question below, that you
answered perfectly for me!! thanks again for helping...

I need to address my letter, and I have two options, "first" name or
"second" name. if there is a "second" name listed, I want to use that
name,
but if there is not a "second" name listed, I want to use the "first" name
listed. So this is what I wrote, based on the "himher" code. But it
doesn't
return anything, and also asked for a parameter for the "first" name, but
I
think I did something different then.

can you advise what I did incorrectly? Also, what do the last quotes " "
mean?

Thanks again, bluesky

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
Null,[second_name]," "))

"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will
either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

I believe that if you try to run a query that contains any vb functions from
word, then the query will not be visible. That is the reason that the query
with NZ is not visible. It is auto-MAGIC-ally screened out of the available
queries.

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

"bluesky" wrote in message
...
Hi John,

Thanks again, both worked, IIF and NZ!!!.

But, something really stranges happens when I use NZ instead of IIF for
"addressee". When I go to merge the query in Word for a letter, and I
"Open
Data Source" the query with the NZ funtion does not show up!! All my
other
queries do and if use the IIF function for "addressee," that shows up.
BTW,
I also have the Him/Her IIF query in there as well. Is there a problem
with
using both IIF and NZ in a query. I can run the query (NZ and IIF) in
Access
and it is fine, but just doesn't show up in Word.....weird...any
thoughts.

Thanks,

bluesky

"John Spencer" wrote:

You test for Null values using Is Null and Is Not Null

addressee: IIf([second_name] IS Null,[first_name],IIf([second_name] IS
Not
Null,[second_name]," "))

An easier solution is to use the NZ function

Addressee: NZ([Second_Name],[First_Name])
This will return Second_Name unless it is null. If it is null, then the
value of First_Name is returned. If both are null, then it returns null
(the value of First_Name).

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

"bluesky" wrote in message
...
Hi John,

I have a question along the same lines as the question below, that you
answered perfectly for me!! thanks again for helping...

I need to address my letter, and I have two options, "first" name or
"second" name. if there is a "second" name listed, I want to use that
name,
but if there is not a "second" name listed, I want to use the "first"
name
listed. So this is what I wrote, based on the "himher" code. But it
doesn't
return anything, and also asked for a parameter for the "first" name,
but
I
think I did something different then.

can you advise what I did incorrectly? Also, what do the last quotes "
"
mean?

Thanks again, bluesky

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
Null,[second_name]," "))

"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will
either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M
then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

Thanks John,

So the IIF function is not Visual Basic, but the NZ function is?

Thanks again!! I thought I was going crazy!

-bluesky

"John Spencer" wrote:

I believe that if you try to run a query that contains any vb functions from
word, then the query will not be visible. That is the reason that the query
with NZ is not visible. It is auto-MAGIC-ally screened out of the available
queries.

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

"bluesky" wrote in message
...
Hi John,

Thanks again, both worked, IIF and NZ!!!.

But, something really stranges happens when I use NZ instead of IIF for
"addressee". When I go to merge the query in Word for a letter, and I
"Open
Data Source" the query with the NZ funtion does not show up!! All my
other
queries do and if use the IIF function for "addressee," that shows up.
BTW,
I also have the Him/Her IIF query in there as well. Is there a problem
with
using both IIF and NZ in a query. I can run the query (NZ and IIF) in
Access
and it is fine, but just doesn't show up in Word.....weird...any
thoughts.

Thanks,

bluesky

"John Spencer" wrote:

You test for Null values using Is Null and Is Not Null

addressee: IIf([second_name] IS Null,[first_name],IIf([second_name] IS
Not
Null,[second_name]," "))

An easier solution is to use the NZ function

Addressee: NZ([Second_Name],[First_Name])
This will return Second_Name unless it is null. If it is null, then the
value of First_Name is returned. If both are null, then it returns null
(the value of First_Name).

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

"bluesky" wrote in message
...
Hi John,

I have a question along the same lines as the question below, that you
answered perfectly for me!! thanks again for helping...

I need to address my letter, and I have two options, "first" name or
"second" name. if there is a "second" name listed, I want to use that
name,
but if there is not a "second" name listed, I want to use the "first"
name
listed. So this is what I wrote, based on the "himher" code. But it
doesn't
return anything, and also asked for a parameter for the "first" name,
but
I
think I did something different then.

can you advise what I did incorrectly? Also, what do the last quotes "
"
mean?

Thanks again, bluesky

addressee: IIf([second_name]=Null,[first_name],IIf([second_name]=Not
Null,[second_name]," "))

"John Spencer" wrote:

You could use a calculated field in the query.

Field: HimHer: IIF([Sex]="M","him",IIF([Sex]="F","Her"," "))



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

"bluesky" wrote in message
...
Hi -

I need to run a query for a mail merge and I need a field that will
either
return "her" or "him" depending on my "sex" field. My sex field is
already
established, either M or F, so I need a new field where if sex=M
then
newfield = him OR if sex=F then newfield = her.

I believe I need to do this in VBA? I'm a bit confused.

Thanks

bsk

Well, there is an IIF in VBA, but Access JET has its own IIF function.


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

"bluesky" wrote in message
...
Thanks John,

So the IIF function is not Visual Basic, but the NZ function is?

Thanks again!! I thought I was going crazy!

-bluesky