VBA code in query for new field?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
..
 
Perfect!!! Thank you!



John Spencer said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
..

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
..

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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]," "))

:

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
..

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
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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
..

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]," "))

:

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
..

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
Click to expand...
Click to expand...
Click to expand...
 
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
..
 
Back
Top