Problem with a query that concatenates field values

[Amit]

MS Access 2K, Windows XP
====================
Hi,

I have a query (QueryA) that concatenates values for a field using the
function here:
http://www.mvps.org/access/modules/mdl0008.htm

I have another query (QueryB) that is combined with queryA. QueryB and
QueryA have a left join based on the Primary key. So, I'm including all
records from QueryB, and only those records from QueryA where the values
match based on the PK.

Here's my problem. If I run this new query (QueryC), I get "Error #. 3075:
Syntax Error: Missing OPerator in Query expression '[ContactID]=' ". I get
this error box many times, and after I've clicked, the query results are
displayed correctly.

But, if I run QueryA as a make-table query, and then left-join this new
table to QueryB, and then run this query (QueryC), I do not get the error
message, and it works fine.

The problem is that I have a form that exports the results of QueryC to
Excel spreadsheet by clicking on a button. Everytime before I export the
data, I have to go into the database and run QueryA and create the table.

Is there a way to fix this so that
a. Either there is a single query (as proposed in the first scenario, w/o
making a table) that I can run from the form without getting any error
messages.

b. Or some way to run the make-table query from the form. That means, not
displaying the warning message that pops up when a make-table query is run.

I haven't done b. so, will appreciate a detailed response if that is the
easier solution. This is in a multi-user split database. I'm assuming that
the table will be created in the FE that is on each user's desktop, and
deleted after exporting data.

Thanks for any pointers.

-Amit

 

['69 Camaro]

Hi, Amit.

I get this error box many times

Once for each NULL encountered. fConcatFld( ) doesn't accommodate NULL's
passed to it. One can prevent the error message by placing additional code
between these two lines:

On Error GoTo Err_fConcatFld

lovConcat = Null

Insert an IF block between these two lines of code that checks for a NULL
value and then takes appropriate action, so that the code snippet reads like
this:

On Error GoTo Err_fConcatFld

If (Nz(vForFldVal, "") = "") Then
fConcatFld = ""
Exit Function
End If

lovConcat = Null


HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

How much is your time worth? Many people think consultants are too
expensive. It took 6 minutes to solve this task and test the solution. Add
up the amount of time you spent on this task: struggling with the problem,
writing the question, posting it, waiting for replies, and reviewing each one
to determine whether it worked. Compared to a $75/hr consultant, if you're
the average U.S. IT worker (http://www.bls.gov/oes/current/oes_15Co.htm,
$30.11 + 30% (benefits) = $39.14/hr) and spent more than 11.5 minutes on this
task, your company paid too much for the free advice. If you're an average
worker (http://www.bls.gov/opub/ted/2005/mar/wk2/art04.htm, $23.90/hr (incl.
benefits)) and spent more than 18.8 minutes on this task, your company paid
too much, too. Perhaps your company should consider hiring one of the many
experts who answer questions in these newsgroups for a more cost-effective
way to develop, enhance, or fix your company's databases to free up your time
for more productive, revenue-generating tasks.

MS Access 2K, Windows XP
====================
Hi,

I have a query (QueryA) that concatenates values for a field using the
function here:
http://www.mvps.org/access/modules/mdl0008.htm

I have another query (QueryB) that is combined with queryA. QueryB and
QueryA have a left join based on the Primary key. So, I'm including all
records from QueryB, and only those records from QueryA where the values
match based on the PK.

Here's my problem. If I run this new query (QueryC), I get "Error #. 3075:
Syntax Error: Missing OPerator in Query expression '[ContactID]=' ". I get
this error box many times, and after I've clicked, the query results are
displayed correctly.

But, if I run QueryA as a make-table query, and then left-join this new
table to QueryB, and then run this query (QueryC), I do not get the error
message, and it works fine.

The problem is that I have a form that exports the results of QueryC to
Excel spreadsheet by clicking on a button. Everytime before I export the
data, I have to go into the database and run QueryA and create the table.

Is there a way to fix this so that
a. Either there is a single query (as proposed in the first scenario, w/o
making a table) that I can run from the form without getting any error
messages.

b. Or some way to run the make-table query from the form. That means, not
displaying the warning message that pops up when a make-table query is run.

I haven't done b. so, will appreciate a detailed response if that is the
easier solution. This is in a multi-user split database. I'm assuming that
the table will be created in the FE that is on each user's desktop, and
deleted after exporting data.

Thanks for any pointers.

-Amit

 

[Amit]

Thanks, Gunny.
Your solution worked like a charm!
Have a nice weekend.

-Amit