I want to add an additonal "where" parament to Allen Browne's concatRelated

[wchelly]

ConcatRelated("serial_no","packages","[Release_Status] = """ & "Released" & """" AND ","[Package_Type] = """ & "[Package_Type" & """")

I've tried this many many ways and I wind up getting either a list of all the "released" packages....of all package types, OR All of the packages of one type whether they are released or not.

The above doesn't seem to work.

It's based on another query which already filters out the "Not-released" Packages, however it returns "Released" and "Not-Released"

Any suggestions would be appreciated. Thanks!

 

[Isskint]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

Does this work?

ConcatRelated("serial_no","packages","[Release_Status] = 'Released' AND [Package_Type] = '" & [Package_Type] & "'")

 

[wchelly]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

That's Awesome! It works. Thank YOU!

 

[wchelly]

Re: Follow-up questions: Can I add a field to concatRelated? How?

I should have included this in the original post, but I did't know I needed it.

Is there a way to add a field? I need the out put to be serial_number and condition_comments. I've tried the following and both result in the output being -1.

ConcatRelated("[serial_no] And [condition_comments]", "packages","[Release_Status] = 'Released' AND [Package_Type] = '" & [Package_Type] & "'")

ConcatRelated("[serial_no]" And "[condition_comments]", "packages","[Release_Status] = 'Released' AND [Package_Type] = '" & [Package_Type] & "'")

Any suggestions for this?

 

[Isskint]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

I am not aware of this function form Allen Browne, however the format seems to mirror the aggregate functions in Access, so try;

ConcatRelated("[serial_no]" & "[condition_comments]", "packages","[Release_Status] = 'Released' AND [Package_Type] = '" & [Package_Type] & "'")

Can you post a link to this function so i can have a look?

 

[wchelly]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

http://allenbrowne.com/func-concat.html

 

[Isskint]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

Thanks.

Having looked at the function my previous post would seem to be the way to go. You would basically get a list like;

Field1Field2, Field1Field2, ....

If you want a space (comma, hyphen etc) between your 2 fields add it like:
ConcatRelated("[serial_no]" & ", " & "[condition_comments]", "packages","[Release_Status] = 'Released' AND [Package_Type] = '" & [Package_Type] & "'")

 

[wchelly]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

I'll try it and let you know how it goes, thanks for the help!

I tried this and it gives me a syntax error

Error 3075: Syntax error (Missing operator) in query expression'[serial_no][condition_comments]'.

 

[wchelly]

Re: I want to add an additonal "where" parament to Allen Browne's concatRelated

Lesson Learned (Just wanted to post back for anyone reading this thread looking for answers):
There are conditions where concatRelated is needed, and I have them, and use this function because I need it.
Other times, there may be a better method. Eg. it's possible to create a subreport with columns which will produce faster results and not involve complicated SQL.