Prev: Can't open query in SQL View to correct query ("Join expression not supported")
Next: dirDataCopy error message when converting to Access 2000
From: Dixie on 31 Aug 2005 21:58
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.

The code I have is as follows:
***************************************
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant) _
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

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

'That's it... you should have a concatenated string now
'Just Trim the trailing ;
fConcatChild = Left(varConcat, Len(varConcat) - 2)

Exit_fConcatChild:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChild:
Resume Exit_fConcatChild
End Function
***************************

Apart from trying to get this module to do as I wish it, I had though of
using a maketable query to put the entries I wish to use into a temporary
table, then running this function on that data, but it would be nice to know
how to modify the module with a restriction.

TIA
Dixie
PS sorry about the length of this post.


From: pietlinden on 1 Sep 2005 01:02
You have to pass the StartDate and EndDate into the function, so it
should look like this:

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
'--Function body (mostly omitted for brevity!)
End Function

then after this:

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

You need to drop in the date filtering part of the WHERE clause
strSQL = strSQL & " AND [SomeDate] BETWEEN #" & dtmStart & "# AND #" &
dtmEnd &"#"

see? nothing to it, right?

From: Salad on 1 Sep 2005 01:08
Dixie wrote:
> 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.
>
> The code I have is as follows:
> ***************************************
> Function fConcatChild(strChildTable As String, _
> strIDName As String, _
> strFldConcat As String, _
> strIDType As String, _
> varIDvalue As Variant) _
> 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
>
> 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
>
> 'That's it... you should have a concatenated string now
> 'Just Trim the trailing ;
> fConcatChild = Left(varConcat, Len(varConcat) - 2)
>
> Exit_fConcatChild:
> Set rs = Nothing: Set db = Nothing
> Exit Function
> Err_fConcatChild:
> Resume Exit_fConcatChild
> End Function
> ***************************
>
> Apart from trying to get this module to do as I wish it, I had though of
> using a maketable query to put the entries I wish to use into a temporary
> table, then running this function on that data, but it would be nice to know
> how to modify the module with a restriction.
>
> TIA
> Dixie
> PS sorry about the length of this post.
>
>
I don't see where you are restricting by a date range. And the code
doesn't have any place for restricting it.

Dev's code puts in the name of the column to return from a table where
the ID = a key value passed.

If you know what the date range is, you could pass another argument to
the function. For example

'sample code prior to calling the function
Dim strTable As String
Dim strColumnToConcat As String
Dim strFieldNameOfKey As String
Dim strKeyValue As Variant
Dim strKeyType As String
Dim strDateRestrict As String

strTable = "Order Details"
strColumn = "Quantity"
strFieldNameOfKey = "OrderID"
strKeyValue = 10255
strKeyType = "Long"

***********
'Scenario 1: You have a from/To date on a form
'now we'll assume you have a FromDate and a ToDate on the form
'let's assume in the table the date field is called DateFld
'remember, date fields are surrounded by #
If Not IsNull(Me.FromDate) Then
strDateRestrict = "DateFld >= #" & Me.FromDate & "#"
Endif
If Not IsNull(Me.FromDate) And Not IsNull(Me.ToDate) Then
strDateRestrict = strDateRestrict & " And "
Endif
If Not IsNull(Me.FromDate) Then
strDateRestrict = strDateRestrict & _
"DateFld <= #" & Me.ToDate & "#"
Endif
********

***********
'Scenario 2: You have a From/To date variable
'if you didn't have a from/to date, this is how it would work
'with a variable. Again, the field in the table is assumed to
'be called DateFld
Dim datFrom As Date
Dim datTo As Date

'since the date fields have been dimmed, they are initialized
'to 12/30/1899 so check for that, not null
If Year(datFrom) <> 1899 Then
strDateRestrict = "DateFld >= #" & datFrom & "#"
Endif
If Year(datFrom) <> 1899 And Year(datFromDate) <> 1899 Then
strDateRestrict = strDateRestrict & " And "
Endif
If Year(datTo) <> 1899 Then
strDateRestrict = "DateFld <= #" & datTo & "#"
Endif
********

strTable = "Order Details"
strColumnToConcat = "Quantity"
strFieldNameOfKey = "OrderID"
strKeyValue = 10255
strKeyType = "Long"

fConcatChild(strTable, strFieldNameOfKeystrColumnToConcat, _
strKeyType, strKeyValue, strEDEatRestrict)


Now, the function needs to have the new argument and I'll call it
strDateFilter. Add it to the function.
Function fConcatChild(strChildTable As String, _
strIDName As String, _
strFldConcat As String, _
strIDType As String, _
varIDvalue As Variant,
strDateFilter As String) As String

Now you need to check for the date filter prior to opening the
recordset. If you passed something it it, add it to the SQL statement.
Change your above code to contain the following

If strDateFilter <> "" Tnen
strSQL = strSQL & " And " & strDateFilter
End If

Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
From: Dixie on 1 Sep 2005 02:47
Thank you Piet, with a little bit of work on the string in the query, this
is now working fine. Nothing to it? Maybe for you, but at least I can
follow how it works and was able to modify the query string to suit, so I
consider I learned something. Thanks for helping.

dixie


From: Dixie on 1 Sep 2005 02:57
Scenario 1: was my scenario - the start and end date both on the form. Both
have default dates that would encompass the whole range of entries.
I have read through your post and I think I follow it. I'll put some time
into it over the weekend and finish it off. I have already looked briefly
at a similar method from Piet Linden and at this stage, it seems to work OK.
I will particulary look at your code to check that the restrictor has valid
data. I think I need to check for the case where the end date is earlier
than the start date.

Thanks for your help Salad. It is always good to know that such
knowledgeable people are willing to help people.

dixie


 |  Next  |  Last
Pages: 1 2 3
Prev: Can't open query in SQL View to correct query ("Join expression not supported")
Next: dirDataCopy error message when converting to Access 2000