Address List

Hi John,

I checked Duane's formula again and the following simpler version seems to
be working for the latter half of the formula (ie after the= sign):

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE[Unit
No]&[Street Number ]&[Street Name] =" & [Unit No] & [Street Number] & [Street
Name])

However, when I run this I'm now getting the message:

Syntax error (Missing Operator) in query expression '[Unit No]&[Street
Number]&[Street Name]= 123 Winterbourne Dr.'

And when I click Debug it takes me to the same old line:
Set rs = db.OpenRecordset(pstrSQL)

So given that its properly picking up the address info after the = sign I
presume the problem is with the first half of the formula. I tried puttiing
the [Unit No]&[Street Number]&[Street Name] in single quotes, double quotes
and () but to no avail. I also tried it with additional "&" signs at the
beginning and end of the phrase but it makes no difference. When I look at
Duane's formula it seems to be constructed the same as mine is.

Have you any idea what this elusive "Missing Operator" might be?

Sorry for dragging this out but it seems we're so darned close!

Thanks,

FJ

"John Spencer" wrote:

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number] &
[Street Name] & ""')

It still appears to me as if you have the apostrophes and quotes switched.
Let's try a slight differenct approach.
LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE [Unit No]&[Street Number ] & [Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

If that fails, then obviously I am not giving you the correct advice.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"FJ Questioner" wrote in message
news
OK so here's what I've got (spaces are added just for clarity)

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number] &
[Street Name] & ""')

Now I get the same error message: Runtime error 3061. Too few parameters.
Expected 1.

Incidentally, I got the same error mesage even when I tried the above with
2
versions of the street concatenation. One with spaces and one without ie.

X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

and

X: ([Unit No] & [Street Number] & [Street Name])

Thanks again,
FJ

NO, the problem is that 123 WinterBourne Dr should be surrounded with Quote
marks to tell the query that you are looking at a string. Without
surrounding quotes the query thinks you want to do something with the number
123 and two fields named Winterbourne and Dr.

Did you try the suggestion below?

LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE[Unit No]&[Street Number ]&[Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

I'm not sure how you ended up with spaces in 123 Winterbourne Dr since there
is nothing in your posted SQL string that would add those in.

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

"FJ Questioner" wrote in message
...
Hi John,

I checked Duane's formula again and the following simpler version seems to
be working for the latter half of the formula (ie after the= sign):

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE[Unit
No]&[Street Number ]&[Street Name] =" & [Unit No] & [Street Number] &
[Street
Name])

However, when I run this I'm now getting the message:

Syntax error (Missing Operator) in query expression '[Unit No]&[Street
Number]&[Street Name]= 123 Winterbourne Dr.'

And when I click Debug it takes me to the same old line:
Set rs = db.OpenRecordset(pstrSQL)

So given that its properly picking up the address info after the = sign I
presume the problem is with the first half of the formula. I tried
puttiing
the [Unit No]&[Street Number]&[Street Name] in single quotes, double
quotes
and () but to no avail. I also tried it with additional "&" signs at the
beginning and end of the phrase but it makes no difference. When I look at
Duane's formula it seems to be constructed the same as mine is.

Have you any idea what this elusive "Missing Operator" might be?

Sorry for dragging this out but it seems we're so darned close!

Thanks,

FJ

"John Spencer" wrote:

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number] &
[Street Name] & ""')

It still appears to me as if you have the apostrophes and quotes
switched.
Let's try a slight differenct approach.
LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE [Unit No]&[Street Number ] & [Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

If that fails, then obviously I am not giving you the correct advice.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"FJ Questioner" wrote in message
news
OK so here's what I've got (spaces are added just for clarity)

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number]
&
[Street Name] & ""')

Now I get the same error message: Runtime error 3061. Too few
parameters.
Expected 1.

Incidentally, I got the same error mesage even when I tried the above
with
2
versions of the street concatenation. One with spaces and one without
ie.

X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

and

X: ([Unit No] & [Street Number] & [Street Name])

Thanks again,
FJ

EUREKA ! I tried it with the Chr(34) and this time it worked (although I
tried that the first time you mentioned it and it didn't).

However, it still needs to be tweaked because in the numerous instances
where there are 2 or more people at a residence with the same last name, the
query is returning a string that includes one last name for each resident.
Here's what the query produces:

Address Last Names

123Winterbourne Dr. Smith, Smith, Smith


I can't produce address labels to "The Smith & Smith & Smith residence". Is
there a way I filter it so that I end up only with one of each discrete name
at a given address?

Hope this isn't too tricky!

Incidentally, here's the version that I've now got:

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE[Unit
No]&[Street Number ]&[Street Name] =" & Chr(34) & [Unit No] & [Street Number]
& [Street Name] & Chr(34))

Thanks again.
FJ


"John Spencer" wrote:

NO, the problem is that 123 WinterBourne Dr should be surrounded with Quote
marks to tell the query that you are looking at a string. Without
surrounding quotes the query thinks you want to do something with the number
123 and two fields named Winterbourne and Dr.

Did you try the suggestion below?

LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE[Unit No]&[Street Number ]&[Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

I'm not sure how you ended up with spaces in 123 Winterbourne Dr since there
is nothing in your posted SQL string that would add those in.

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

"FJ Questioner" wrote in message
...
Hi John,

I checked Duane's formula again and the following simpler version seems to
be working for the latter half of the formula (ie after the= sign):

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE[Unit
No]&[Street Number ]&[Street Name] =" & [Unit No] & [Street Number] &
[Street
Name])

However, when I run this I'm now getting the message:

Syntax error (Missing Operator) in query expression '[Unit No]&[Street
Number]&[Street Name]= 123 Winterbourne Dr.'

And when I click Debug it takes me to the same old line:
Set rs = db.OpenRecordset(pstrSQL)

So given that its properly picking up the address info after the = sign I
presume the problem is with the first half of the formula. I tried
puttiing
the [Unit No]&[Street Number]&[Street Name] in single quotes, double
quotes
and () but to no avail. I also tried it with additional "&" signs at the
beginning and end of the phrase but it makes no difference. When I look at
Duane's formula it seems to be constructed the same as mine is.

Have you any idea what this elusive "Missing Operator" might be?

Sorry for dragging this out but it seems we're so darned close!

Thanks,

FJ

"John Spencer" wrote:

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number] &
[Street Name] & ""')

It still appears to me as if you have the apostrophes and quotes
switched.
Let's try a slight differenct approach.
LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE [Unit No]&[Street Number ] & [Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

If that fails, then obviously I am not giving you the correct advice.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"FJ Questioner" wrote in message
news OK so here's what I've got (spaces are added just for clarity)

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number]
&
[Street Name] & ""')

Now I get the same error message: Runtime error 3061. Too few
parameters.
Expected 1.

Incidentally, I got the same error mesage even when I tried the above
with
2
versions of the street concatenation. One with spaces and one without
ie.

X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

and

X: ([Unit No] & [Street Number] & [Street Name])

Thanks again,
FJ

Add DISTINCT to the Select clause.

LastNames: Concatenate("SELECT DISTINCT [Last Name]
FROM[list] WHERE[Unit No]&[Street Number ]&[Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))


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

"FJ Questioner" wrote in message
...
EUREKA ! I tried it with the Chr(34) and this time it worked (although I
tried that the first time you mentioned it and it didn't).

However, it still needs to be tweaked because in the numerous instances
where there are 2 or more people at a residence with the same last name,
the
query is returning a string that includes one last name for each resident.
Here's what the query produces:

Address Last Names

123Winterbourne Dr. Smith, Smith, Smith


I can't produce address labels to "The Smith & Smith & Smith residence".
Is
there a way I filter it so that I end up only with one of each discrete
name
at a given address?

Hope this isn't too tricky!

Incidentally, here's the version that I've now got:

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE[Unit
No]&[Street Number ]&[Street Name] =" & Chr(34) & [Unit No] & [Street
Number]
& [Street Name] & Chr(34))

Thanks again.
FJ


"John Spencer" wrote:

NO, the problem is that 123 WinterBourne Dr should be surrounded with
Quote
marks to tell the query that you are looking at a string. Without
surrounding quotes the query thinks you want to do something with the
number
123 and two fields named Winterbourne and Dr.

Did you try the suggestion below?

LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE[Unit No]&[Street Number ]&[Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

I'm not sure how you ended up with spaces in 123 Winterbourne Dr since
there
is nothing in your posted SQL string that would add those in.

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

"FJ Questioner" wrote in message
...
Hi John,

I checked Duane's formula again and the following simpler version seems
to
be working for the latter half of the formula (ie after the= sign):

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE[Unit
No]&[Street Number ]&[Street Name] =" & [Unit No] & [Street Number] &
[Street
Name])

However, when I run this I'm now getting the message:

Syntax error (Missing Operator) in query expression '[Unit No]&[Street
Number]&[Street Name]= 123 Winterbourne Dr.'

And when I click Debug it takes me to the same old line:
Set rs = db.OpenRecordset(pstrSQL)

So given that its properly picking up the address info after the = sign
I
presume the problem is with the first half of the formula. I tried
puttiing
the [Unit No]&[Street Number]&[Street Name] in single quotes, double
quotes
and () but to no avail. I also tried it with additional "&" signs at
the
beginning and end of the phrase but it makes no difference. When I look
at
Duane's formula it seems to be constructed the same as mine is.

Have you any idea what this elusive "Missing Operator" might be?

Sorry for dragging this out but it seems we're so darned close!

Thanks,

FJ

"John Spencer" wrote:

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street Number]
&
[Street Name] & ""')

It still appears to me as if you have the apostrophes and quotes
switched.
Let's try a slight differenct approach.
LastNames: Concatenate("SELECT [Last Name] FROM[list]
WHERE [Unit No]&[Street Number ] & [Street Name] ="
& Chr(34) & [Unit No] & [Street Number] & [Street Name] & Chr(34))

If that fails, then obviously I am not giving you the correct advice.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"FJ Questioner" wrote in
message
news OK so here's what I've got (spaces are added just for clarity)

LastNames: Concatenate("SELECT [Last Name] FROM[list] WHERE [Unit
No]&[Street Number ] & [Street Name] ="' & [Unit No] & [Street
Number]
&
[Street Name] & ""')

Now I get the same error message: Runtime error 3061. Too few
parameters.
Expected 1.

Incidentally, I got the same error mesage even when I tried the
above
with
2
versions of the street concatenation. One with spaces and one
without
ie.

X: ([Unit No] & "" & [Street Number] & "" & [Street Name])

and

X: ([Unit No] & [Street Number] & [Street Name])

Thanks again,
FJ