Concat Related / Query Design Problem ?

[ipk*]

hello,

i am at the end of my wits with allen brown's concat related function (to be
found here: http://allenbrowne.com/func-concat.html). it seems the only thing
out there to concatenate multiple records of a database into one field, and
it does do that very nicely for me, if i use it without "where" statement,
i.e.

=ConcatRelated("Label";"query_label_top")

however, i need to restrict the records output, using it like this

=ConcatRelated("Label";"query_label_top";"GScreenId =" & [GScreenId])

with the underlying query looking like this:

SELECT tbl_gscreencomponents.GScreenId,
IIf([pHGScreenComponentTL]<>[pHGScreenComponentTR],[SubstanceName] & "
(pH)",[SubstanceName]) AS Label, tbl_gscreencomponents.cGScreenComponentTL,
tbl_gscreencomponents.pHGScreenComponentTL
FROM tbl_substances INNER JOIN tbl_gscreencomponents ON
tbl_substances.SubstanceId = tbl_gscreencomponents.SubstanceId WHERE
(((tbl_gscreencomponents.cGScreenComponentTL)<>[cGScreenComponentTR])) OR
(((tbl_gscreencomponents.pHGScreenComponentTL)<>[pHGScreenComponentTR]));


the query itself correctly returns a number of hits, some of which have
GScreenId =
1, and if i write GScreenId from the calling form into a text box, i also
receive 1, so the form has the right GScreenId, the query returns hits, but
concat related returns me an error 3061 (one parameter expected, to few
handed over (translated from german)). as i said, without the where clause
it does work just fine, but returns to many fields.

i have been able to track the error back to the following line in allen
brown's vba
code:

Set rs = DBEngine(0)(0).OpenRecordset(strSql, dbOpenDynaset)

where a msgbox echoing strsql seems to echo the right sql string, but
dbopendynaset returns nothing. so evidentally here is the missing parameter.

can anybody help ?

1000 thanks in advance

greetings

ingo

 

[Allen Browne]

Suggestions:

1. The parameter means there's a name it can't undertand. Double-check
what's in GScreenId at the time it plays up.

2. Could it be null? If so, the final argument resolves to just:
GScreenID =
which is clearly not going to work. Try:
";"GScreenId =" & Nz([GScreenId],0)

3. What's the data type of the GScreenId field?
If Text, you need additional quote marks:
http://allenbrowne.com/casu-17.html

 

[ipk*]

Hi Allen,

thanks for taking the time to answer ...

After I gave up yesterday and re-started my computer today, the problem
misteriously went away ... evidentally I have some instability in my database
design, since something did not work yesterday, no matter how i banged my
head against it ... unfortunately today, without changing anything in the
database, just re-opening it, i am unable to reproduce the error and track
down the underlying problem.

Guess I will have to leave it like that. As long as it works ... .

Thanks, again ...

Ingo

 

[Allen Browne]

Right: that happens sometimes. A restart may have been all that's needed.

When a database starts playing up in an inconsistent way, you may want to
check that Name AutoCorrect is off, do a compact, decompile, compact,
compile sequence, etc -- the basic recovery steps outlined here:
http://allenbrowne.com/recover.html

All the best