Concatenate error - Duane Hookom's concat function

[Guest]

I followed the Duane Hookom's directions for setting up a concatenate
function in my database. As far as I can tell it's all set up correctly...

I copied the basConcatenate function over to my database and in my query I'm
using the VBA code:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
Region =" & [Region])


But when I run the query, it comes up with an error for each row:

Run-time error '-2147217900 (8004e14)':
Syntax error (missing operator) in query expression 'Region =North West'.

(ie where the row is for the North West region)

What have I done wrong??
How do I fix it??

Please help!

 

[Al Campagna]

Kat,
Since Region is a text field it should be in it's own quotes...
"Region = '" & [Region] &"'"
I'll put spaces in the quotes for clarity, but don't use them in the code...
"Region = ' " & [Region] & " ' "

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[Guest]

Have I got this right? The new code would be:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
"Region = '" & [Region] &"'")

This doesn't seem to work. I get an error:

"The expression you entered contains invalid syntax. You may have entered an
operand without an operator."


Did I miss something?

Sorry to be a pain.

Kat,
Since Region is a text field it should be in it's own quotes...
"Region = '" & [Region] &"'"
I'll put spaces in the quotes for clarity, but don't use them in the code...
"Region = ' " & [Region] & " ' "

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I followed the Duane Hookom's directions for setting up a concatenate
function in my database. As far as I can tell it's all set up correctly...

I copied the basConcatenate function over to my database and in my query I'm
using the VBA code:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
Region =" & [Region])


But when I run the query, it comes up with an error for each row:

Run-time error '-2147217900 (8004e14)':
Syntax error (missing operator) in query expression 'Region =North West'.

(ie where the row is for the North West region)

What have I done wrong??
How do I fix it??

Please help!

 

[Guest]

To work out if it would make it any easier, I changed by Region table to have
a primary key as a number and the region name as just another a text field
(instead of just one text field that also serves as a primary key)

After adjusting the congregation table to deal with that I tested the query
and it still came up with the same error.

So that leads me to think it's not a text field problem....??
Or have I done something wrong again?

Kat,
Since Region is a text field it should be in it's own quotes...
"Region = '" & [Region] &"'"
I'll put spaces in the quotes for clarity, but don't use them in the code...
"Region = ' " & [Region] & " ' "

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I followed the Duane Hookom's directions for setting up a concatenate
function in my database. As far as I can tell it's all set up correctly...

I copied the basConcatenate function over to my database and in my query I'm
using the VBA code:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
Region =" & [Region])


But when I run the query, it comes up with an error for each row:

Run-time error '-2147217900 (8004e14)':
Syntax error (missing operator) in query expression 'Region =North West'.

(ie where the row is for the North West region)

What have I done wrong??
How do I fix it??

Please help!

 

[Al Campagna]

Kat,
I'm not familiar with Duane's utility, but it appears that the Concatenate argument
requires a legitimate SQL statement.
Are you trying to write the SQL statement manually?
If so, try using the query design grid to create the query you want, then cut and paste
the View/SQL text into your Concatenate function.
I did a quick test...
SELECT tblCustomers.State FROM tblCustomers WHERE (((tblCustomers.State)="ME"));
So, I would suspect...
Concatenate("SELECT tblCustomers.State FROM tblCustomers WHERE
(((tblCustomers.State)="ME"));")

"should" work...
--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Have I got this right? The new code would be:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
"Region = '" & [Region] &"'")

This doesn't seem to work. I get an error:

"The expression you entered contains invalid syntax. You may have entered an
operand without an operator."


Did I miss something?

Sorry to be a pain.

Kat,
Since Region is a text field it should be in it's own quotes...
"Region = '" & [Region] &"'"
I'll put spaces in the quotes for clarity, but don't use them in the code...
"Region = ' " & [Region] & " ' "

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I followed the Duane Hookom's directions for setting up a concatenate
function in my database. As far as I can tell it's all set up correctly...

I copied the basConcatenate function over to my database and in my query I'm
using the VBA code:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
Region =" & [Region])


But when I run the query, it comes up with an error for each row:

Run-time error '-2147217900 (8004e14)':
Syntax error (missing operator) in query expression 'Region =North West'.

(ie where the row is for the North West region)

What have I done wrong??
How do I fix it??

Please help!

 

[John Spencer]

No, you don't have it right. (all on one line, it should look like the
following). Remember that you need to have an even number of quote marks
once you finish the entry. Try

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
Region = '" & [Region] &"'")

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

Have I got this right? The new code would be:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation
WHERE
"Region = '" & [Region] &"'")

This doesn't seem to work. I get an error:

"The expression you entered contains invalid syntax. You may have entered
an
operand without an operator."


Did I miss something?

Sorry to be a pain.

Kat,
Since Region is a text field it should be in it's own quotes...
"Region = '" & [Region] &"'"
I'll put spaces in the quotes for clarity, but don't use them in the
code...
"Region = ' " & [Region] & " ' "

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

I followed the Duane Hookom's directions for setting up a concatenate
function in my database. As far as I can tell it's all set up
correctly...

I copied the basConcatenate function over to my database and in my
query I'm
using the VBA code:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation
WHERE
Region =" & [Region])


But when I run the query, it comes up with an error for each row:

Run-time error '-2147217900 (8004e14)':
Syntax error (missing operator) in query expression 'Region =North
West'.

(ie where the row is for the North West region)

What have I done wrong??
How do I fix it??

Please help!

 

[Guest]

Thankyou!!!
It all works now.

No, you don't have it right. (all on one line, it should look like the
following). Remember that you need to have an even number of quote marks
once you finish the entry. Try

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation WHERE
Region = '" & [Region] &"'")

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

Have I got this right? The new code would be:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation
WHERE
"Region = '" & [Region] &"'")

This doesn't seem to work. I get an error:

"The expression you entered contains invalid syntax. You may have entered
an
operand without an operator."


Did I miss something?

Sorry to be a pain.

Kat,
Since Region is a text field it should be in it's own quotes...
"Region = '" & [Region] &"'"
I'll put spaces in the quotes for clarity, but don't use them in the
code...
"Region = ' " & [Region] & " ' "

--
hth
Al Campagna
Candia Computer Consulting
Microsoft Access MVP - Candia, NH USA
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."


I followed the Duane Hookom's directions for setting up a concatenate
function in my database. As far as I can tell it's all set up
correctly...

I copied the basConcatenate function over to my database and in my
query I'm
using the VBA code:

RegionTest: Concatenate("SELECT CongregationName FROM tblCongregation
WHERE
Region =" & [Region])


But when I run the query, it comes up with an error for each row:

Run-time error '-2147217900 (8004e14)':
Syntax error (missing operator) in query expression 'Region =North
West'.

(ie where the row is for the North West region)

What have I done wrong??
How do I fix it??

Please help!