Hi friends! I have this query th 1

[cuok]

Hi friends!

I have this query that works fine.
======== Query ===================================
SQL = "SELECT ComplicationsRespiratory.Id, ComplicationsRespiratory.HistoryActiveRespProb, ComplicationsRespiratory.DateCheck
FROM ComplicationsRespiratory
WHERE (((ComplicationsRespiratory.Id)=[forms]![frmrashi]![id]))
ORDER BY ComplicationsRespiratory.DateCheck DESC;
"
========== End Query ==================================

"HistoryActiveRespProb" is a MemoField
------------------------------------------------
the query gives result like this:

ID HistoryActiveRespProb (Memo)

1 A
1 B
1 C
1 D
----------------------------------------------------------
What i tried to do with no success is to get from (by?) this query the next result:

ID HistoryActiveRespProb (Memo)

1 A
B
C
D

A,B,C,D IN the SAME FIELD

How can i change/make a query to get (=as a 'chain') ALL "HistoryActiveRespProb" fields gathered into the last or first record or any other way?

Thanks for your help
CUOK

 

[PaulBricker]

Cuok, try this. Put this function in a module

Function getValues(myID as Integer) as String
Dim rst as DAO.Recordset
Dim strSQL as String, strHolder as String
strSQL = "Select ComplicationsRespiratory.HistoryActiveRespProb From ComplicationsRespiratory Where WHERE ComplicationsRespiratory.Id = " & myIDSet
rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strHolder = strHolder & rst!HistoryActiveRespProb & " "
rst.MoveNext
Loop
getValues = strHolder
End Function

Create a query and add the table ComplicationsRespiratory. Add the Id field to the Field grid, turn on the Totals and GroupBy the Id and then put something like this in a new Column
MyRespProb:getValues([Id])
Set the totals line for this field to Expression.

Paul

 

[cuok]

Hi Paul and ALL!

First I have to apology to you and to members forum because I put this Q. ,by mistake, in Module forum then I corrected it by putting it here .
Sorry for doing this by mistake . In the other forum SBendBuckeye
Have sent an advice and I’m going to learn it too.

Now to business :
Dear Paul!

I’m week with queries so I’ll be grateful to you for any help and if you can please, step by step.

Thanks in advance Paul.

I put your function in a module and have changed the:

1
“Function getValues(myID as Integer) as String “
To
Function getValues(myID as String) as String
Because my ID is string
2
strSQL = "Select ComplicationsRespiratory.HistoryActiveRespProb From ComplicationsRespiratory Where WHERE ComplicationsRespiratory.Id = " & myIDSet
TO
& Forms!frmrashi!Id


“Create a query and add the table ComplicationsRespiratory. Add the Id field to the Field grid,”
Till here I did ok.

But I don’t know how to do this:

“turn on the Totals and GroupBy the Id and..”

Only after I choosed the “crossTab query”, then “select query” I got the “total” and the “GroupBy”

The rest I did ok:
“then put something like this in a new Column
MyRespProb:getValues([Id])
Set the totals line for this field to Expression.”

Here is the syntax of this query:

“SELECT ComplicationsRespiratory.Id, getValues([id]) AS MyRespProb
FROM ComplicationsRespiratory
GROUP BY ComplicationsRespiratory.Id;”


==============================

The run:

I got ErrMsg: Invalid use of property here:

rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
I tried to delete the “, dbOpenDynaset” with no success.


I understood that the “strHolder” will appear in the query, am I right?

Thank you very very much Paul!

 

[PaulBricker]

Cuok, if you are using the Form to pass the value to the SQL then try this,

Function getValues()
Dim rst as DAO.Recordset
Dim strSQL as String, strHolder as String
strSQL = "Select ComplicationsRespiratory.HistoryActiveRespProb From ComplicationsRespiratory Where WHERE ComplicationsRespiratory.Id = '" & Forms!frmrashi!Id & "'"
rst = CurrentDb.OpenRecordset(strSQL,dbOpenDynaset)
rst.MoveFirst
Do Until rst.EOF
strHolder = strHolder & rst!HistoryActiveRespProb & " "
rst.MoveNext
Loop
getValues = strHolder
End Function

Don't worry about the Totals query. You won't need it now.

Paul

 

[cuok]

hI Paul!

i hope u r with me!
i'm working on it

CUOK

 

[cuok]

OK Paul i'm in trouble!

A fatal error occurs 3 times and access turn off!

I put your function in vb code behind a form.
I assigned: TextBox = strHolder
CUOK

 

[PaulBricker]

I'm sorry Cuok but this won't work trying to get a value into a textbox on a form. You didn't mention trying to do that so I didn't write the function to give you that.
Where did you put the code behind the Form. What event? How did you assign the textbox value to be strHolder?

Paul

 

[cuok]

SO WHERE TO PUT THE FUNCTION AND WHERE THE RESULT SHOULD BE TO APPEAR?

any way i still get the same error: Invalid use of property

thanks
CUOK

 

[cuok]

Paul, somthing wrong with:
rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

i deleted the assign to the textbox and added debug.print strSQL before this row and got nothing then i got the error i have mentioned before.
thanks
cuok

 

[cuok]

the "set" is missing should be:set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

i'm going on!

 

[PaulBricker]

You are correct about the Set. Sorry, I must have missed it when I copied it to the post.
Paul

 

[cuok]

ok Paul all works perfect!!!
you are great!

i deleted one "where" in strSQL

ADDED TEXT
ADDED BUTTON to TRIGGER THE YOUR FUNCTION !!
NOW I'M VERY HAPPY TO GIVE YOU A BIG STAR MY DEAR FRIEND!!

OOOPS, DO NOT GO TO SLEEP !!
THERE ARE A LOT TO DO MORE PAUL:

1. How to sort the list ?
2. each row of the list looks like this:
Abscess - 513(05/01/2003)
Allergic bronchopulmonary aspergillosis - 518.6(05/01/2003)

i need to sort in 2 ways: by the name ("Allergic bronchopulmonary aspergillosis - 518.6&quot
and by date "(05/01/2003)"

Paul if I exaggerated kick me out!!
but for me its very important and i have not enough knowledge for this!

Thank you very very much DEAR PAUL!
CUOK

 

[PaulBricker]

Cuok, I'm not sleeping, I'm watching the Super Bowl. You need to add a Order By clause to the strSQL statement. It will look like this.

"Select ComplicationsRespiratory.HistoryActiveRespProb From ComplicationsRespiratory Where WHERE ComplicationsRespiratory.Id = '" & Forms!frmrashi!Id & "'
ORDER BY ComplicationsRespiratory.HistoryActiveRespProb, ComplicationsRespiratory.DateCheck DESC"

Paul

 

[PaulBricker]

Cuok, the Order By clause should be on the same line as the rest of the strSQL, not a separate line like it appears in the post.

Paul

 

[cuok]

Good night Paul!
now its 03:35
i must sleep

to be continue!!!

CUOK