From: Dixie on
Help, this is never ending. I just thought of another one that I can't do
outside the module and that is to have the facility to not include certain
awards on a certificate. I have a Y/N field called 'Certificate'. I would
like to restrict the number of awards concatenated to only those whose
'Certificate' field is 0 (zero). I presume this would need a 2nd part to
the WHERE clause which is currently:

strSQL = strSQL & " AND [Date] BETWEEN #" & Format(dtmStart, "mm\/dd\/yyyy")
& "# AND #" & Format(dtmEnd, "mm\/dd\/yyyy") & "#"

I imagine another AND clause? and another argument as well. What I have
working now is as follows:

'**************************************
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant, _
dtmStart As Date, _
dtmEnd As Date) _
As String

'Returns a field from the Many table of a 1:M relationship
'in a semi-colon separated format.
'
'Usage Examples:
' ?fConcatChild("Order Details", "OrderID", "Quantity", _
"Long", 10255)
'Where Order Details = Many side table
' OrderID = Primary Key of One side table
' Quantity = Field name to concatenate
' Long = DataType of Primary Key of One Side Table
' 10255 = Value on which return concatenated Quantity

Dim db As Database
Dim rs As Recordset
Dim varConcat As Variant
Dim strCriteria As String, strSQL As String
On Error GoTo Err_fConcatChild

varConcat = Null
Set db = CurrentDb
strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
strSQL = strSQL & " Where "

Select Case strIDType
Case "String":
strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
Case "Long", "Integer", "Double": 'AutoNumber is Type Long
strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
Case Else
GoTo Err_fConcatChild
End Select

strSQL = strSQL & " AND [Date] BETWEEN #" & Format(dtmStart, "mm\/dd\/yyyy")
& "# AND #" & Format(dtmEnd, "mm\/dd\/yyyy") & "#"

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & vbCrLf
.MoveNext
Loop
End If
End With
'******************************

If I can get this part, that should round it off nicely, as I can't think of
any other delimiter I would need.

Hope you can help. I know this is probably just straight SQL, but I failed
that part. :-(


From: Dixie on
You know the best way to solve your problems? Put a message for help on the
newsgroup, then 5 minutes later, you're bound to solve it yourself anyway.
:-).
Seems I was on the right track - I added another argument booCert AS Boolean
and added the following line after my current WHERE clause.
strSQL = strSQL & " AND [Certificate] = 0"

That appears to work. I will need to test it more, but at this stage, it
seems to be fine. It might not be elegant, but what the heck.

Thanks guys, learning heaps.

dixie

"Dixie" <dixie(a)dogmail.com> wrote in message
news:4317b81d(a)duster.adelaide.on.net...
> Help, this is never ending. I just thought of another one that I can't do
> outside the module and that is to have the facility to not include certain
> awards on a certificate. I have a Y/N field called 'Certificate'. I
> would like to restrict the number of awards concatenated to only those
> whose 'Certificate' field is 0 (zero). I presume this would need a 2nd
> part to the WHERE clause which is currently:
>
> strSQL = strSQL & " AND [Date] BETWEEN #" & Format(dtmStart,
> "mm\/dd\/yyyy") & "# AND #" & Format(dtmEnd, "mm\/dd\/yyyy") & "#"
>
> I imagine another AND clause? and another argument as well. What I have
> working now is as follows:
>
> '**************************************
> Function fConcatChild(strChildTable As String, _
> strIDName As String, _
> strFldConcat As String, _
> strIDType As String, _
> varIDvalue As Variant, _
> dtmStart As Date, _
> dtmEnd As Date) _
> As String
>
> 'Returns a field from the Many table of a 1:M relationship
> 'in a semi-colon separated format.
> '
> 'Usage Examples:
> ' ?fConcatChild("Order Details", "OrderID", "Quantity", _
> "Long", 10255)
> 'Where Order Details = Many side table
> ' OrderID = Primary Key of One side table
> ' Quantity = Field name to concatenate
> ' Long = DataType of Primary Key of One Side Table
> ' 10255 = Value on which return concatenated Quantity
>
> Dim db As Database
> Dim rs As Recordset
> Dim varConcat As Variant
> Dim strCriteria As String, strSQL As String
> On Error GoTo Err_fConcatChild
>
> varConcat = Null
> Set db = CurrentDb
> strSQL = "Select [" & strFldConcat & "] From [" & strChildTable & "]"
> strSQL = strSQL & " Where "
>
> Select Case strIDType
> Case "String":
> strSQL = strSQL & "[" & strIDName & "] = '" & varIDvalue & "'"
> Case "Long", "Integer", "Double": 'AutoNumber is Type Long
> strSQL = strSQL & "[" & strIDName & "] = " & varIDvalue
> Case Else
> GoTo Err_fConcatChild
> End Select
>
> strSQL = strSQL & " AND [Date] BETWEEN #" & Format(dtmStart,
> "mm\/dd\/yyyy") & "# AND #" & Format(dtmEnd, "mm\/dd\/yyyy") & "#"
>
> Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
>
> 'Are we sure that 'sub' records exist
> With rs
> If .RecordCount <> 0 Then
> 'start concatenating records
> Do While Not rs.EOF
> varConcat = varConcat & rs(strFldConcat) & vbCrLf
> .MoveNext
> Loop
> End If
> End With
> '******************************
>
> If I can get this part, that should round it off nicely, as I can't think
> of any other delimiter I would need.
>
> Hope you can help. I know this is probably just straight SQL, but I
> failed that part. :-(
>


From: Tim Marshall on
Dixie wrote:
> You know the best way to solve your problems? Put a message for help on the
> newsgroup, then 5 minutes later, you're bound to solve it yourself anyway.
> :-).

Yup, that happens to me a lot too! 8)

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
From: Salad on
Bob Quintal wrote:
> "Dixie" <dixie(a)dogmail.com> wrote in
> news:43166039$1(a)duster.adelaide.on.net:
>
>
>>I have a problem using Dev Ashish's excellent module to
>>concatenate the results of a field from several records into
>>one record.
>>
>>I am using the code to concatenate certain awards onto a
>>certificate at the end of the year. I have the code working
>>fine, except for the fact that when I want to restrict the
>>entries to awards between certain dates, even though I can use
>>the restriction in the query that shows the actual records,
>>when the fConcatChild function runs, it picks up all the
>>entries, regardless of the date restriction. I tried to run
>>the table part as a qry rather than a tbl, but no joy. I
>>think the code inside Dev's module will need to get have the
>>date restriction in it. I need the type of restriction that
>>is WHERE Date >start date <End date.
>>
>>Does anyone know how to do that within the module.
>>The code in that module is beyond my expertise.
>>
>
> Dev's code works perfectly well against a query name passed as
> the strChildTable parameter, so your problem is for some reason
> other than the code,
>
> I see from other replies that you encountered other issues with
> the date format issues.
>
> Did you check that the query has all the fields from your main
> table and just a filter for the date range. Test it to make sure
> that that's not where the problem lies.
>
> example:.
> SELECT * from tblSomeTable WHERE dMyDate BETWEEN dLoDate AND
> dHiDate.

Hi Bob. She had a second parameter, date range. Dev's code only took
into consideration 1 criteria for the filter.
From: Bob Quintal on
Salad <oil(a)vinegar.com> wrote in
news:9WYSe.767$9x2.766(a)newsread3.news.pas.earthlink.net:

> Bob Quintal wrote:
>> "Dixie" <dixie(a)dogmail.com> wrote in
>> news:43166039$1(a)duster.adelaide.on.net:
>>
>>
>>>I have a problem using Dev Ashish's excellent module to
>>>concatenate the results of a field from several records into
>>>one record.
>>>
>>>I am using the code to concatenate certain awards onto a
>>>certificate at the end of the year. I have the code working
>>>fine, except for the fact that when I want to restrict the
>>>entries to awards between certain dates, even though I can
>>>use the restriction in the query that shows the actual
>>>records, when the fConcatChild function runs, it picks up all
>>>the entries, regardless of the date restriction. I tried to
>>>run the table part as a qry rather than a tbl, but no joy. I
>>>think the code inside Dev's module will need to get have the
>>>date restriction in it. I need the type of restriction that
>>>is WHERE Date >start date <End date.
>>>
>>>Does anyone know how to do that within the module.
>>>The code in that module is beyond my expertise.
>>>
>>
>> Dev's code works perfectly well against a query name passed
>> as the strChildTable parameter, so your problem is for some
>> reason other than the code,
>>
>> I see from other replies that you encountered other issues
>> with the date format issues.
>>
>> Did you check that the query has all the fields from your
>> main table and just a filter for the date range. Test it to
>> make sure that that's not where the problem lies.
>>
>> example:.
>> SELECT * from tblSomeTable WHERE dMyDate BETWEEN dLoDate AND
>> dHiDate.
>
> Hi Bob. She had a second parameter, date range. Dev's code
> only took into consideration 1 criteria for the filter.

Sorry for the belated reply, I was using teranews, which started
dropping posts, and has been unreachable for a week now.

Anyways, I addressed the question of the date range, but perhaps
I could have done so more clearly.

Dixie claimed, and I quote
>>>the entries, regardless of the date restriction. I tried to
>>>run the table part as a qry rather than a tbl, but no joy. I

to which I answered

>> Dev's code works perfectly well against a query name passed
>> as the strChildTable parameter, so your problem is for some
>> reason other than the code,
>>
That query would have filtered against the date range, returning
the pk and values to be concatenated. ,

--
Bob Quintal

PA is y I've altered my email address.