combining multiple records in one field

[evansc]

Okay--this is kind of an odd request. . .

I have a table that lists courts and the year they completed a project. So frequently a single court may have completed projects in multiple years.

Obviously, the query lists them per record as follows:

Court#1 2000
Court#1 2001
Court#1 2002
etc.

I would like it to print on the report as:

Court #1 2000/2001/2002

Is that even possible?

Just FYI--the Court is the detail of the report--there are two grouping levels above it.

 

[Trendsetter]

Yuo could use a do while Loop that looked a the info based on the project and then loop through the records based on Court Number then did something like
Dim rst as Recordset
Dim txtYears as string
Set rst = CurrentDb.Openrecordset( Select * From CourtList Where Project=" & ...............)
Do While rstCourt = some variable that selects the one you want.....
txtYears = txtYears & "/" & rst!Year
rst.movenext
Loop
rst.close
set rst = nothing
I realise this is not complete but is a guide fro you to work with.

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[dhookom]

There is a generic Concatenate function and sample usage at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[evansc]

Okay, I went into that sample, and copied the sql to try to redo it for my query.

This is what it looks like now:

SELECT Courts.Court, Concatenate("SELECT Year FROM ProjPerCourt WHERE Court =" & [Court]) AS Year
FROM Courts;

But when I try to run it I get "Compile error in query expression 'Concatenate. . .'"

What might I be doing wrong?

 

[Trendsetter]

Because [Court] produces a string not an integer you need to amend your SQL as follows:

WHERE Court =" & "'" & [Court] & "'")

Frank J Hill
FHS Services Ltd.
frank@fhsservices.co.uk

 

[evansc]

That makes sense--thanks. I still don't know very much sql or code and I don't always realize the little things like that.

 

[evansc]

Okay, I still get the same error for the following.

SELECT Courts.Court, Concatenate("SELECT Year FROM ProjPerCourt WHERE Court =" & "'" & [Court] & "'") AS Years
FROM Courts;

I did check to make sure and Court is text, so I would think this would be right. I've even checked to see if I have the same number of spaces as Duane did!

And when I try the following in a form or report I am told that the Visual Basic Module contains a syntax error.

=Concatenate("SELECT Year FROM ProjPerCourt WHERE Court =" & "'" & [Court] & "'")

Would it make a difference that I'm on Access 2002?

 

[dhookom]

After you copied the code into a module, did you attempt to compile your database? Make sure also that you didn't save the module with the same name as the function.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[maduko]

I am attempting a similar operation and hope someone can help me. After downloading Duane's MDB I copied the script into a new module in my MDB and compiled it. But my query gives me an error every time:

Run time error- 2147217904 (80040e10)
No value given for one or more required paramters.

The expression I'm using in my query is:

Products: Concatenate("SELECT LineID FROM PROOF_lines WHERE PEIdir.member_class =" & [MemberID])

The debug window has highlighted this portion:

rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic

The first time this happened I tried commenting out the DAO portion and using the ADO- but that bombed even sooner.

 

[dhookom]

You would not have PEIDir.Member_Class in table PROOF_lines. Does member_class contain the same values as MemberID? Which field [member_class] or [MemberID] is in the table PROOF_Lines?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[maduko]

MemberID is in the Proof_lines table, and it is the same value as member_class in PEIdir.

 

[dhookom]

Products: Concatenate("SELECT LineID FROM PROOF_lines WHERE [MemberID] = " & [member_class])

This assumes both fields are numeric. If they are text, use:
Products: Concatenate("SELECT LineID FROM PROOF_lines WHERE [MemberID] = """ & [member_class] & """")


Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[maduko]

Bravo!

Thank you very much.

 

[maduko]

Once again I am stumped.

I cannot undertsand why this will not work:

Topics: Concatenate("SELECT Description FROM tblTableAssignments WHERE [Reg_No] =" & [RegID])

Both Reg_No and RegID are numeric fields and I have tried every combination of brackets, no brackets, etc. It gives me the same familiar error:

Run time error- 2147217904 (80040e10)
No value given for one or more required paramters.

 

[dhookom]

Is it possible for the RegID field to be null?

Topics: Concatenate("SELECT Description FROM tblTableAssignments WHERE [Reg_No] =" & Nz([RegID],-1))


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[maduko]

No, but it's a pretty good chance that Description exists in another table!

Duh.

Sorry for the trouble!