Help with a query Access 97

[GRCC]

Hi, I am new to conditional queries. We use access 97 in our club. Our DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing address
field either has a 1 or a 2 in it depending where the member is at the time.
1 is local address, 2 is northern address. We want to be able to export a
text file with the correct address at the time of the mailing. I have tried
using the expression builder but keep getting an error message "invalid
brackets" ? Any help or point me to some reference info? Sorry for the long
post, but this is very important for us. Below is what I have typed in the
expression builder of access 97. I assume I should be using the expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member
]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Membe
r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta
te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

If you break it down in a more readable form you should see your error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the Switch
function will only return one value. In this case, it's only going to return
either Address1 or Address2, as one of these will be the first value found.
You will need either a Switch function or an IIF function for City, State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi, I am new to conditional queries. We use access 97 in our club. Our DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing address
field either has a 1 or a 2 in it depending where the member is at the time.
1 is local address, 2 is northern address. We want to be able to export a
text file with the correct address at the time of the mailing. I have tried
using the expression builder but keep getting an error message "invalid
brackets" ? Any help or point me to some reference info? Sorry for the long
post, but this is very important for us. Below is what I have typed in the
expression builder of access 97. I assume I should be using the expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

ber!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta
te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

Lynn,
Thanks so much for the response. I also believe I have the feilds wrong. I
have in the first field member name-thats OK , but in the second field I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the city,state and
zip. When I try to enter this in the field I get an error saying" The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent to me by our
accounting software company.(I am the IT person here, but this is a bit
different than a w2k network). I sure could use some help. Thanks again,
Frank

If you break it down in a more readable form you should see your error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the Switch
function will only return one value. In this case, it's only going to return
either Address1 or Address2, as one of these will be the first value found.
You will need either a Switch function or an IIF function for City, State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi, I am new to conditional queries. We use access 97 in our club. Our DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing address
field either has a 1 or a 2 in it depending where the member is at the time.
1 is local address, 2 is northern address. We want to be able to export a
text file with the correct address at the time of the mailing. I have tried
using the expression builder but keep getting an error message "invalid
brackets" ? Any help or point me to some reference info? Sorry for the long
post, but this is very important for us. Below is what I have typed in the
expression builder of access 97. I assume I should be using the expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem
be

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

The following should work for the address:

Switch([Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2])

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn,
Thanks so much for the response. I also believe I have the feilds wrong. I
have in the first field member name-thats OK , but in the second field I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the city,state and
zip. When I try to enter this in the field I get an error saying" The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent to me by our
accounting software company.(I am the IT person here, but this is a bit
different than a w2k network). I sure could use some help. Thanks again,
Frank

If you break it down in a more readable form you should see your error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the Switch
function will only return one value. In this case, it's only going to return
either Address1 or Address2, as one of these will be the first value found.
You will need either a Switch function or an IIF function for City, State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi, I am new to conditional queries. We use access 97 in our club. Our DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing address
field either has a 1 or a 2 in it depending where the member is at the time.
1 is local address, 2 is northern address. We want to be able to

export

a

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

be

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

Yes, that looks like it will work correctly.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi,
Would this be correct?
Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2])

Switch([Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Member!City

2])

Switch([Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!State2]
)

Switch([Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

Frank

Lynn,
Thanks so much for the response. I also believe I have the feilds wrong. I
have in the first field member name-thats OK , but in the second field I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the city,state and
zip. When I try to enter this in the field I get an error saying" The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent to me by our
accounting software company.(I am the IT person here, but this is a bit
different than a w2k network). I sure could use some help. Thanks again,
Frank

If you break it down in a more readable form you should see your error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the Switch
function will only return one value. In this case, it's only going to return
either Address1 or Address2, as one of these will be the first value found.
You will need either a Switch function or an IIF function for City, State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our club.

Our
DB

has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing address
field either has a 1 or a 2 in it depending where the member is at the
time.
1 is local address, 2 is northern address. We want to be able to

export

a
text file with the correct address at the time of the mailing. I have
tried
using the expression builder but keep getting an error message "invalid
brackets" ? Any help or point me to some reference info? Sorry for the
long
post, but this is very important for us. Below is what I have typed

in
the

expression builder of access 97. I assume I should be using the expression

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

Lynn
I am sorry to be a pest. But I am almost there. I now have trouble with what
to put in the fields? Please see my 11:30am post (also right below), if you
can. If not, i understand and will continue on. Thanks so very, very, much
Frank L

"> > > Lynn,

wrong.

I

have in the first field member name-thats OK , but in the second field I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the city,state and
zip. When I try to enter this in the field I get an error saying" The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent to me

by
our

accounting software company.(I am the IT person here, but this is a bit
different than a w2k network). I sure could use some help. Thanks again,
Frank

If you break it down in a more readable form you should see your error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the Switch
function will only return one value. In this case, it's only going to
return
either Address1 or Address2, as one of these will be the first value
found.
You will need either a Switch function or an IIF function for City, State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our club. Our
DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing address
field either has a 1 or a 2 in it depending where the member is at the
time.
1 is local address, 2 is northern address. We want to be able to export
a
text file with the correct address at the time of the mailing. I have
tried
using the expression builder but keep getting an error message "invalid
brackets" ? Any help or point me to some reference info? Sorry for the
long
post, but this is very important for us. Below is what I have

typed

in

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could post it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn
I am sorry to be a pest. But I am almost there. I now have trouble with what
to put in the fields? Please see my 11:30am post (also right below), if you
can. If not, i understand and will continue on. Thanks so very, very, much
Frank L

"> > > Lynn,

wrong.

field

I

put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the

city,state
and

zip. When I try to enter this in the field I get an error saying" The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent to me by
our
accounting software company.(I am the IT person here, but this is a bit
different than a w2k network). I sure could use some help. Thanks again,
Frank

If you break it down in a more readable form you should see your error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the Switch
function will only return one value. In this case, it's only going to
return
either Address1 or Address2, as one of these will be the first value
found.
You will need either a Switch function or an IIF function for City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our

club.
Our

DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing
address
field either has a 1 or a 2 in it depending where the member is

at
the

time.
1 is local address, 2 is northern address. We want to be able to
export
a
text file with the correct address at the time of the mailing. I have
tried
using the expression builder but keep getting an error message
"invalid
brackets" ? Any help or point me to some reference info? Sorry

for
the

long
post, but this is very important for us. Below is what I have

typed

in
the
expression builder of access 97. I assume I should be using the
expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a field in the
tabl, no criteria). In the fields for address,city,state,zip I am getting
confused. The example shows 2nd field as Address:Switch([Member]! .Same for
the other 3. I did this and i get an error saying I have an invalid .(dot)
or !. I really do not want to have to create any new fields in the database
because the table is one of about 50 in a very large accounting program. It
is the table with all the member address info . It is the only one I need
for this.Also, do you mean in each "critera field" in each of the 4 columns
I should insert the switch accordingly? I had the whole expression in the
first column criteria. Boy, Am I confused or what?
Frank

Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could post it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn
I am sorry to be a pest. But I am almost there. I now have trouble with what
to put in the fields? Please see my 11:30am post (also right below), if you
can. If not, i understand and will continue on. Thanks so very, very, much
Frank L

"> > > Lynn,

Thanks so much for the response. I also believe I have the fields wrong.
I
have in the first field member name-thats OK , but in the second

field

I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the city,state
and
zip. When I try to enter this in the field I get an error saying" The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent to

me
by

our
accounting software company.(I am the IT person here, but this is

a
bit

different than a w2k network). I sure could use some help. Thanks again,
Frank

If you break it down in a more readable form you should see your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the
Switch
function will only return one value. In this case, it's only

going
to

return
either Address1 or Address2, as one of these will be the first value
found.
You will need either a Switch function or an IIF function for City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our club.
Our
DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing
address
field either has a 1 or a 2 in it depending where the member

is

at

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a field in the
tabl, no criteria). In the fields for address,city,state,zip I am getting
confused. The example shows 2nd field as Address:Switch([Member]! .Same for
the other 3. I did this and i get an error saying I have an invalid .(dot)
or !. I really do not want to have to create any new fields in the database
because the table is one of about 50 in a very large accounting program. It
is the table with all the member address info . It is the only one I need
for this.Also, do you mean in each "critera field" in each of the 4 columns
I should insert the switch accordingly? I had the whole expression in the
first column criteria. Boy, Am I confused or what?
Frank

Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could post it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn
I am sorry to be a pest. But I am almost there. I now have trouble

with
what

to put in the fields? Please see my 11:30am post (also right below),

if
you

can. If not, i understand and will continue on. Thanks so very, very, much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the fields
wrong.
I
have in the first field member name-thats OK , but in the second field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the city,state
and
zip. When I try to enter this in the field I get an error

saying"
The

expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent

to

me

is

a

bit
different than a w2k network). I sure could use some help. Thanks
again,
Frank

message
If you break it down in a more readable form you should see your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because the
Switch
function will only return one value. In this case, it's only going
to
return
either Address1 or Address2, as one of these will be the first value
found.
You will need either a Switch function or an IIF function for City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our club.
Our
DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing
address
field either has a 1 or a 2 in it depending where the member

is

at
the
time.
1 is local address, 2 is northern address. We want to be

able

to

mailing.

I Sorry
for

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

I'm glad it worked.... good luck with learning Access inside and out --
there aren't many people who know it that well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn,
Thank you so much. It works great. I have plans to learn access inside and
out(that should take a while, but I know it's a great program and well worth
the effort. I have access 2000 at home.
Frank

No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a field

in
the

tabl, no criteria). In the fields for address,city,state,zip I am getting
confused. The example shows 2nd field as Address:Switch([Member]!

..Same
for

the other 3. I did this and i get an error saying I have an invalid .(dot)
or !. I really do not want to have to create any new fields in the database
because the table is one of about 50 in a very large accounting

program.
It

is the table with all the member address info . It is the only one I need
for this.Also, do you mean in each "critera field" in each of the 4 columns
I should insert the switch accordingly? I had the whole expression in the
first column criteria. Boy, Am I confused or what?
Frank
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could

post

it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have trouble with
what
to put in the fields? Please see my 11:30am post (also right

below),
if

you
can. If not, i understand and will continue on. Thanks so very, very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the fields
wrong.
I
have in the first field member name-thats OK , but in the second
field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the
city,state
and
zip. When I try to enter this in the field I get an error saying"
The
expressionyou entered has an invalid .(dot) or ! operator or
invalid
paretheses". I have been trying to follow a fax that was

sent
to

me
by
our
accounting software company.(I am the IT person here, but

this
is

a
bit
different than a w2k network). I sure could use some help. Thanks
again,
Frank

in

message
If you break it down in a more readable form you should

see
your

error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because
the
Switch
function will only return one value. In this case, it's only
going
to
return
either Address1 or Address2, as one of these will be the first
value
found.
You will need either a Switch function or an IIF function for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our
club.
Our
DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The
billing
address
field either has a 1 or a 2 in it depending where the member
is
at
the
time.
1 is local address, 2 is northern address. We want to be able
to
export
a
text file with the correct address at the time of the mailing.
I
have
tried
using the expression builder but keep getting an error message
"invalid
brackets" ? Any help or point me to some reference info? Sorry
for
the
long
post, but this is very important for us. Below is what I have
typed
in
the
expression builder of access 97. I assume I should be using
the
expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

Lynn,
Thank you so much. It works great. I have plans to learn access inside and
out(that should take a while, but I know it's a great program and well worth
the effort. I have access 2000 at home.
Frank

No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a field in the
tabl, no criteria). In the fields for address,city,state,zip I am getting
confused. The example shows 2nd field as Address:Switch([Member]! .Same for
the other 3. I did this and i get an error saying I have an invalid ..(dot)
or !. I really do not want to have to create any new fields in the database
because the table is one of about 50 in a very large accounting program. It
is the table with all the member address info . It is the only one I need
for this.Also, do you mean in each "critera field" in each of the 4 columns
I should insert the switch accordingly? I had the whole expression in the
first column criteria. Boy, Am I confused or what?
Frank

Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could

post

it

again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have trouble with
what
to put in the fields? Please see my 11:30am post (also right below), if
you
can. If not, i understand and will continue on. Thanks so very,

very,
much

Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the fields
wrong.
I
have in the first field member name-thats OK , but in the second
field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the
city,state
and
zip. When I try to enter this in the field I get an error saying"
The
expressionyou entered has an invalid .(dot) or ! operator or invalid
paretheses". I have been trying to follow a fax that was sent

to

me
by
our
accounting software company.(I am the IT person here, but this

is

a
bit
different than a w2k network). I sure could use some help. Thanks
again,
Frank

message
If you break it down in a more readable form you should see your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want,

because
the

Switch
function will only return one value. In this case, it's only going
to
return
either Address1 or Address2, as one of these will be the first
value
found.
You will need either a Switch function or an IIF function for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97 in our
club.
Our
DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The billing
address
field either has a 1 or a 2 in it depending where the

member
is

at
the
time.
1 is local address, 2 is northern address. We want to be

able

to
export
a
text file with the correct address at the time of the

mailing.

I
have
tried
using the expression builder but keep getting an error message
"invalid
brackets" ? Any help or point me to some reference info? Sorry
for
the
long
post, but this is very important for us. Below is what I have
typed
in
the
expression builder of access 97. I assume I should be

using
the

expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

Hello Lynn, if you are there. I neglected on thing. I needed to select only
certain member addresses. I thought it would be simple. I added the field
"Number" from the same table. I need records 1-799,900-999, and 4000-5599. I
have tried entering them in the criteria field in the number column, I get
nothing. How do you input this range in the same query?
Thanks agin,
Frank

I'm glad it worked.... good luck with learning Access inside and out --
there aren't many people who know it that well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn,
Thank you so much. It works great. I have plans to learn access inside and
out(that should take a while, but I know it's a great program and well worth
the effort. I have access 2000 at home.
Frank

field

in

the
tabl, no criteria). In the fields for address,city,state,zip I am getting
confused. The example shows 2nd field as Address:Switch([Member]! .Same
for
the other 3. I did this and i get an error saying I have an invalid .(dot)
or !. I really do not want to have to create any new fields in the
database
because the table is one of about 50 in a very large accounting program.
It
is the table with all the member address info . It is the only one I need
for this.Also, do you mean in each "critera field" in each of the 4
columns
I should insert the switch accordingly? I had the whole expression

in
the

first column criteria. Boy, Am I confused or what?
Frank
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have trouble
with
what
to put in the fields? Please see my 11:30am post (also right below),
if
you
can. If not, i understand and will continue on. Thanks so very, very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the
fields
wrong.
I
have in the first field member name-thats OK , but in the second
field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the
city,state
and
zip. When I try to enter this in the field I get an error
saying"
The
expressionyou entered has an invalid .(dot) or ! operator or
invalid
paretheses". I have been trying to follow a fax that was sent
to
me
by
our
accounting software company.(I am the IT person here, but this
is
a
bit
different than a w2k network). I sure could use some help.
Thanks
again,
Frank

in
message
If you break it down in a more readable form you should see
your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be [Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want, because
the
Switch
function will only return one value. In this case, it's only
going
to
return
either Address1 or Address2, as one of these will be the first
value
found.
You will need either a Switch function or an IIF

function
for

City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access 97

in
our

club.
Our
DB
has the following feilds in one table:member name- address1,
street1,city1,zip1.and address2,street2,city2,zip2. The
billing
address
field either has a 1 or a 2 in it depending where the member
is
at
the
time.
1 is local address, 2 is northern address. We want to be
able
to
export
a
text file with the correct address at the time of the
mailing.
I
have
tried
using the expression builder but keep getting an error
message
"invalid
brackets" ? Any help or point me to some reference info?
Sorry
for
the
long
post, but this is very important for us. Below is what I
have
typed
in
the
expression builder of access 97. I assume I should be using

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

I tried expression builder with feild name put in automatically when i
selected build while in the number field I added to the query Number >=1 And
<=799, etc. with a comma after each set and without a comma after each set.
It says i am missing an operand or operator.
Thanks
Frank

Hello Lynn, if you are there. I neglected on thing. I needed to select only
certain member addresses. I thought it would be simple. I added the field
"Number" from the same table. I need records 1-799,900-999, and 4000-5599. I
have tried entering them in the criteria field in the number column, I get
nothing. How do you input this range in the same query?
Thanks agin,
Frank

I'm glad it worked.... good luck with learning Access inside and out --
there aren't many people who know it that well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn,
Thank you so much. It works great. I have plans to learn access inside and
out(that should take a while, but I know it's a great program and well worth
the effort. I have access 2000 at home.
Frank
No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a

field

in
the
tabl, no criteria). In the fields for address,city,state,zip I am
getting
confused. The example shows 2nd field as Address:Switch([Member]! .Same
for
the other 3. I did this and i get an error saying I have an invalid
.(dot)
or !. I really do not want to have to create any new fields in the
database
because the table is one of about 50 in a very large accounting program.
It
is the table with all the member address info . It is the only one I
need
for this.Also, do you mean in each "critera field" in each of the 4
columns
I should insert the switch accordingly? I had the whole expression in
the
first column criteria. Boy, Am I confused or what?
Frank
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could
post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have trouble
with
what
to put in the fields? Please see my 11:30am post (also right below),
if
you
can. If not, i understand and will continue on. Thanks so very,
very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the
fields
wrong.
I
have in the first field member name-thats OK , but in the
second
field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the
city,state
and
zip. When I try to enter this in the field I get an error
saying"
The
expressionyou entered has an invalid .(dot) or !

operator

or

invalid
paretheses". I have been trying to follow a fax that

was
sent

to
me
by
our
accounting software company.(I am the IT person here,

but
this

is
a
bit
different than a w2k network). I sure could use some help.
Thanks
again,
Frank

in
message
If you break it down in a more readable form you

should
see

your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be
[Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want,
because
the
Switch
function will only return one value. In this case,

it's
only

going
to
return
either Address1 or Address2, as one of these will be the
first
value
found.
You will need either a Switch function or an IIF function
for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access

97

in

to

be

what

I

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

Try this:

Select <field List>
From YourTable
Where Number <= 799 OR (Number >= 900 AND Number <=999) OR (Number <= 4000
AND Number <= 5599)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello Lynn, if you are there. I neglected on thing. I needed to select only
certain member addresses. I thought it would be simple. I added the field
"Number" from the same table. I need records 1-799,900-999, and 4000-5599. I
have tried entering them in the criteria field in the number column, I get
nothing. How do you input this range in the same query?
Thanks agin,
Frank

I'm glad it worked.... good luck with learning Access inside and out --
there aren't many people who know it that well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Lynn,
Thank you so much. It works great. I have plans to learn access inside and
out(that should take a while, but I know it's a great program and well worth
the effort. I have access 2000 at home.
Frank
No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a

field

in
the
tabl, no criteria). In the fields for address,city,state,zip I am
getting
confused. The example shows 2nd field as Address:Switch([Member]! .Same
for
the other 3. I did this and i get an error saying I have an invalid
.(dot)
or !. I really do not want to have to create any new fields in the
database
because the table is one of about 50 in a very large accounting program.
It
is the table with all the member address info . It is the only one I
need
for this.Also, do you mean in each "critera field" in each of the 4
columns
I should insert the switch accordingly? I had the whole expression in
the
first column criteria. Boy, Am I confused or what?
Frank
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could
post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have trouble
with
what
to put in the fields? Please see my 11:30am post (also right below),
if
you
can. If not, i understand and will continue on. Thanks so very,
very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the
fields
wrong.
I
have in the first field member name-thats OK , but in the
second
field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same for the
city,state
and
zip. When I try to enter this in the field I get an error
saying"
The
expressionyou entered has an invalid .(dot) or !

operator

or

invalid
paretheses". I have been trying to follow a fax that

was
sent

to
me
by
our
accounting software company.(I am the IT person here,

but
this

is
a
bit
different than a w2k network). I sure could use some help.
Thanks
again,
Frank

in
message
If you break it down in a more readable form you

should
see

your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be
[Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want,
because
the
Switch
function will only return one value. In this case,

it's
only

going
to
return
either Address1 or Address2, as one of these will be the
first
value
found.
You will need either a Switch function or an IIF function
for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access

97

in

to

be

what

I

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Frank]

Lynn,
Thanks again. I will try to figure out where this goes ( trial and error)
and I am assuming I replace <field list> withi my field name <Number> and
YourTable my table name "Member". I will experiment Select <field List>From

AND Number <= 5599)

Hmmm. I have a lot to learn
Frank

Try this:

Select <field List>
From YourTable
Where Number <= 799 OR (Number >= 900 AND Number <=999) OR (Number <= 4000
AND Number <= 5599)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello Lynn, if you are there. I neglected on thing. I needed to select only
certain member addresses. I thought it would be simple. I added the field
"Number" from the same table. I need records 1-799,900-999, and

4000-5599.

I
have tried entering them in the criteria field in the number column, I get
nothing. How do you input this range in the same query?
Thanks agin,
Frank

I'm glad it worked.... good luck with learning Access inside and out --
there aren't many people who know it that well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn,
Thank you so much. It works great. I have plans to learn access

inside
and

out(that should take a while, but I know it's a great program and well
worth
the effort. I have access 2000 at home.
Frank
No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a field
in
the
tabl, no criteria). In the fields for address,city,state,zip I am
getting
confused. The example shows 2nd field as Address:Switch([Member]!
.Same
for
the other 3. I did this and i get an error saying I have an invalid
.(dot)
or !. I really do not want to have to create any new fields in the
database
because the table is one of about 50 in a very large accounting
program.
It
is the table with all the member address info . It is the only

one

I

the

4

columns
I should insert the switch accordingly? I had the whole

expression
in

the
first column criteria. Boy, Am I confused or what?
Frank
message
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could
post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have trouble
with
what
to put in the fields? Please see my 11:30am post (also right
below),
if
you
can. If not, i understand and will continue on. Thanks so very,
very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have the
fields
wrong.
I
have in the first field member name-thats OK , but in the
second
field
I
put address1, etc, the example shows the address field as
"Address:Switch([Member]!" without the qoutes. Same

for
the

city,state
and
zip. When I try to enter this in the field I get an error
saying"
The
expressionyou entered has an invalid .(dot) or !

operator

or
invalid
paretheses". I have been trying to follow a fax that was
sent
to
me
by
our
accounting software company.(I am the IT person here, but
this
is
a
bit
different than a w2k network). I sure could use some help.
Thanks
again,
Frank

"Lynn Trapp"

wrote
in
message
If you break it down in a more readable form you should
see
your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be
[Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want,
because
the
Switch
function will only return one value. In this case, it's
only
going
to
return
either Address1 or Address2, as one of these will be the
first
value
found.
You will need either a Switch function or an IIF function
for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access

97

in
our
club.
Our
DB
has the following feilds in one table:member name-
address1,
street1,city1,zip1.and

address2,street2,city2,zip2.
The

billing
address
field either has a 1 or a 2 in it depending where the
member
is
at
the
time.
1 is local address, 2 is northern address. We want

to

be
able
to
export
a
text file with the correct address at the time of the
mailing.
I
have
tried
using the expression builder but keep getting an error
message
"invalid
brackets" ? Any help or point me to some reference info?
Sorry
for
the
long
post, but this is very important for us. Below is

what

I
have
typed
in
the
expression builder of access 97. I assume I should be
using
the
expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Lynn Trapp]

If you will post the SQL for the query that you created, I am sure we can
figure out where the problem is.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Well, Lynn, I could not get it to work. So I had to edit the entire table
,over 1,000 entries to get the 386 I needed. I guess I just didn't get the
number range expression in right. I'll just do it that way every month I
guess, unless I can figure it out.
Thanks for your help,
Frank

Lynn,
Thanks again. I will try to figure out where this goes ( trial and error)
and I am assuming I replace <field list> withi my field name <Number> and
YourTable my table name "Member". I will experiment Select <field List>From
YourTable Where Number <= 799 OR (Number >= 900 AND Number <=999) OR (Number
<= 4000
Hmmm. I have a lot to learn
Frank

I
get and
well

is

a

field
in
the
tabl, no criteria). In the fields for address,city,state,zip

I
am

getting
confused. The example shows 2nd field as Address:Switch([Member]!
.Same
for
the other 3. I did this and i get an error saying I have an
invalid
.(dot)
or !. I really do not want to have to create any new fields

in
the

database
because the table is one of about 50 in a very large accounting
program.
It
is the table with all the member address info . It is the

only
one

I
need
for this.Also, do you mean in each "critera field" in each

of
the

4
columns
I should insert the switch accordingly? I had the whole expression
in
the
first column criteria. Boy, Am I confused or what?
Frank

in

message
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you
could
post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have
trouble
with
what
to put in the fields? Please see my 11:30am post (also right
below),
if
you
can. If not, i understand and will continue on. Thanks so
very,
very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I have
the
fields
wrong.
I
have in the first field member name-thats OK , but in
the
second
field
I
put address1, etc, the example shows the address field
as
"Address:Switch([Member]!" without the qoutes.

Same
for

the
city,state
and
zip. When I try to enter this in the field I get an
error
saying"
The
expressionyou entered has an invalid .(dot) or !
operator
or
invalid
paretheses". I have been trying to follow a fax that
was
sent
to
me
by
our
accounting software company.(I am the IT person here,
but
this
is
a
bit
different than a w2k network). I sure could use some
help.
Thanks
again,
Frank

"Lynn Trapp"

wrote
in
message
If you break it down in a more readable form you
should
see
your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be
[Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want,
because
the
Switch
function will only return one value. In this case,
it's
only
going
to
return
either Address1 or Address2, as one of these

will

be

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[GRCC]

Well, Lynn, I could not get it to work. So I had to edit the entire table
,over 1,000 entries to get the 386 I needed. I guess I just didn't get the
number range expression in right. I'll just do it that way every month I
guess, unless I can figure it out.
Thanks for your help,
Frank

Lynn,
Thanks again. I will try to figure out where this goes ( trial and error)
and I am assuming I replace <field list> withi my field name <Number> and
YourTable my table name "Member". I will experiment Select <field List>From
YourTable Where Number <= 799 OR (Number >= 900 AND Number <=999) OR (Number
<= 4000

AND Number <= 5599)

Hmmm. I have a lot to learn
Frank

Try this:

Select <field List>
From YourTable
Where Number <= 799 OR (Number >= 900 AND Number <=999) OR (Number <= 4000
AND Number <= 5599)

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Hello Lynn, if you are there. I neglected on thing. I needed to select only
certain member addresses. I thought it would be simple. I added the field
"Number" from the same table. I need records 1-799,900-999, and

4000-5599.

I
have tried entering them in the criteria field in the number column, I get
nothing. How do you input this range in the same query?
Thanks agin,
Frank
I'm glad it worked.... good luck with learning Access inside and out --
there aren't many people who know it that well.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn,
Thank you so much. It works great. I have plans to learn access inside
and
out(that should take a while, but I know it's a great program and well
worth
the effort. I have access 2000 at home.
Frank
No, don't put it in the criteria. Put it in the field section.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi Lynn,
In the field columns ., in the first I have Frmlname(which is a
field
in
the
tabl, no criteria). In the fields for address,city,state,zip I am
getting
confused. The example shows 2nd field as Address:Switch([Member]!
.Same
for
the other 3. I did this and i get an error saying I have an invalid
.(dot)
or !. I really do not want to have to create any new fields in the
database
because the table is one of about 50 in a very large accounting
program.
It
is the table with all the member address info . It is the only

one

I
need
for this.Also, do you mean in each "critera field" in each of

the

4
columns
I should insert the switch accordingly? I had the whole expression
in
the
first column criteria. Boy, Am I confused or what?
Frank
message
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you could
post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have
trouble
with
what
to put in the fields? Please see my 11:30am post (also right
below),
if
you
can. If not, i understand and will continue on. Thanks so very,
very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe I

have
the

fields
wrong.
I
have in the first field member name-thats OK , but

in
the

second
field
I
put address1, etc, the example shows the address

field
as

"Address:Switch([Member]!" without the qoutes. Same for
the
city,state
and
zip. When I try to enter this in the field I get an error
saying"
The
expressionyou entered has an invalid .(dot) or ! operator
or
invalid
paretheses". I have been trying to follow a fax

that
was

sent
to
me
by
our
accounting software company.(I am the IT person

here,
but

this
is
a
bit
different than a w2k network). I sure could use some help.
Thanks
again,
Frank

"Lynn Trapp"

wrote
in
message
If you break it down in a more readable form you should
see
your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be
[Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you want,
because
the
Switch
function will only return one value. In this case, it's
only
going
to
return
either Address1 or Address2, as one of these will

be
the

first
value
found.
You will need either a Switch function or an IIF
function
for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use

access
97

in
our
club.
Our
DB
has the following feilds in one table:member name-
address1,
street1,city1,zip1.and address2,street2,city2,zip2.
The
billing
address
field either has a 1 or a 2 in it depending

where
the

member
is
at
the
time.
1 is local address, 2 is northern address. We

want
to

be
able
to
export
a
text file with the correct address at the time

of
the

mailing.
I
have
tried
using the expression builder but keep getting an error
message
"invalid
brackets" ? Any help or point me to some reference
info?
Sorry
for
the
long
post, but this is very important for us. Below

is
what

I
have
typed
in
the
expression builder of access 97. I assume I

should
be

using
the
expression
builder?
Thanks,
Frank

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])

 

[Frank]

I will do that from work tomorrow(wednesday) As I said before Lynn, I am not
sure what to put in <file list> (does it just remain <file list>?,
yourtable, is that MY table(which is Member and where they all go? Does this
go in the field cell, just as the others did, because that part worked
great, it pulled the proper address from the DB table(which was address1,
etc (local) and address2, etc (up north) That was fine.
Thanks again
Frank

If you will post the SQL for the query that you created, I am sure we can
figure out where the problem is.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm

Well, Lynn, I could not get it to work. So I had to edit the entire table
,over 1,000 entries to get the 386 I needed. I guess I just didn't get the
number range expression in right. I'll just do it that way every month I
guess, unless I can figure it out.
Thanks for your help,
Frank
<=
4000

column,

I is

address,city,state,zip

I

am
getting
confused. The example shows 2nd field as
Address:Switch([Member]!
.Same
for
the other 3. I did this and i get an error saying I have an
invalid
.(dot)
or !. I really do not want to have to create any new

fields

in

the
database
because the table is one of about 50 in a very large accounting
program.
It
is the table with all the member address info . It is the only
one
I
need
for this.Also, do you mean in each "critera field" in each of
the
4
columns
I should insert the switch accordingly? I had the whole
expression
in
the
first column criteria. Boy, Am I confused or what?
Frank

in
message
Frank,
I'm sorry, but I dont' see a post from 11:30 am. Perhaps you
could
post
it
again.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Lynn
I am sorry to be a pest. But I am almost there. I now have
trouble
with
what
to put in the fields? Please see my 11:30am post (also right
below),
if
you
can. If not, i understand and will continue on. Thanks so
very,
very,
much
Frank L

"> > > Lynn,
Thanks so much for the response. I also believe

I
have

the
fields
wrong.
I
have in the first field member name-thats OK ,

but
in

the
second
field
I
put address1, etc, the example shows the address field
as
"Address:Switch([Member]!" without the qoutes. Same
for
the
city,state
and
zip. When I try to enter this in the field I get an
error
saying"
The
expressionyou entered has an invalid .(dot) or !
operator
or
invalid
paretheses". I have been trying to follow a fax that
was
sent
to
me
by
our
accounting software company.(I am the IT person here,
but
this
is
a
bit
different than a w2k network). I sure could use some
help.
Thanks
again,
Frank

"Lynn Trapp"
<[email protected]>
wrote
in
message
If you break it down in a more readable form you
should
see
your
error.

Switch(
[Member]![Billing]=1,
[Member]![Address1],
[Member]![Billing]=2,
[Member]![Address2],
[Member]![Billing]=1,
[Member]![City1],
[Member!Billing]=2,
[Member!City1]=2, <------ should be
[Member!City2]
[Member!Billing]=1,
[Member!State1],
[Member!Billing]=2,
[Member!State2],
[Member!Billing]=1,
[Member!Zip1],
[Member!Billing]=2,
[Member!Zip2])

However, your function still won't work as you
want,
because
the
Switch
function will only return one value. In this case,
it's
only
going
to
return
either Address1 or Address2, as one of these

will

be
the
first
value
found.
You will need either a Switch function or an IIF
function
for
City,
State,
and Zip also.


--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security:
www.ltcomputerdesigns.com/Security.htm



Hi, I am new to conditional queries. We use access
97
in
our
club.
Our
DB
has the following feilds in one table:member name-
address1,
street1,city1,zip1.and
address2,street2,city2,zip2.
The
billing
address
field either has a 1 or a 2 in it depending where
the
member
is
at
the
time.
1 is local address, 2 is northern address.

We
want

to
be
able
to
export
a
text file with the correct address at the

time
of

the
mailing.
I
have
tried
using the expression builder but keep

getting

an Below
is

Switch([Member]![Billing]=1,[Member]![Address1],[Member]![Billing]=2,[Member

]![Address2],[Member]![Billing]=1,[Member]![City1],[Member!Billing]=2,[Mem

r!City1]=2,[Member!Billing]=1,[Member!State1],[Member!Billing]=2,[Member!Sta

te2],[Member!Billing]=1,[Member!Zip1],[Member!Billing]=2,[Member!Zip2])