Concatenating text fields with a WHERE condition

[Dixie]

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(st rChildTable 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_fConcatChil d

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_fConcatChil d
End Select

Set rs = db.OpenRecordse t(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_fConcatChi ld:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChil d:
Resume Exit_fConcatChi ld
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.

[pietlinden]

You have to pass the StartDate and EndDate into the function, so it
should look like this:

Function fConcatChild(st rChildTable 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_fConcatChil d
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?

[Salad]

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(st rChildTable 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_fConcatChil d

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_fConcatChil d
End Select

Set rs = db.OpenRecordse t(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_fConcatChi ld:
Set rs = Nothing: Set db = Nothing
Exit Function
Err_fConcatChil d:
Resume Exit_fConcatChi ld
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 strColumnToConc at As String
Dim strFieldNameOfK ey As String
Dim strKeyValue As Variant
Dim strKeyType As String
Dim strDateRestrict As String

strTable = "Order Details"
strColumn = "Quantity"
strFieldNameOfK ey = "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.FromD ate) Then
strDateRestrict = "DateFld >= #" & Me.FromDate & "#"
Endif
If Not IsNull(Me.FromD ate) And Not IsNull(Me.ToDat e) Then
strDateRestrict = strDateRestrict & " And "
Endif
If Not IsNull(Me.FromD ate) 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(datFromDat e) <> 1899 Then
strDateRestrict = strDateRestrict & " And "
Endif
If Year(datTo) <> 1899 Then
strDateRestrict = "DateFld <= #" & datTo & "#"
Endif
********

strTable = "Order Details"
strColumnToConc at = "Quantity"
strFieldNameOfK ey = "OrderID"
strKeyValue = 10255
strKeyType = "Long"

fConcatChild(st rTable, strFieldNameOfK eystrColumnToCo ncat, _
strKeyType, strKeyValue, strEDEatRestric t)
Now, the function needs to have the new argument and I'll call it
strDateFilter. Add it to the function.
Function fConcatChild(st rChildTable 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.OpenRecordse t(strSQL, dbOpenSnapshot)

[Dixie]

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

[Dixie]

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

[Dixie]

Pieter, just ran into a surprising problem that took me half an hour to work
out. I am in Australia. Our date format is DD/MM/YYYY. When I run this
function, it is using American Date format. So it takes the date from my
text boxes and reverses the day and the month (where this is possible). Is
there any way around this?

dixie

<pi********@hot mail.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

You have to pass the StartDate and EndDate into the function, so it
should look like this:

Function fConcatChild(st rChildTable 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_fConcatChil d
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?

[pietlinden]

Here... found one from Allen Browne... so it'll work.

4. Allen Browne Oct 12 2000, 11:36 pm show options
Newsgroups: comp.databases. ms-access
From: Allen Browne <abro...@odysse y.apana.org.au> - Find messages by
this author
Date: Fri, 13 Oct 2000 12:38:17 +0800
Local: Thurs, Oct 12 2000 11:38 pm
Subject: Re: Date Format

Michael, my experience suggests that Format() with "#" handles
the case where Access misunderstands the data type, but CDate()
does not:

Function TestDate()
Dim strWhere As String
Dim varResult As Variant

strWhere = "DOB = #" & Format("1/2/00", "mm\/dd\/yyyy") & "#"
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere

strWhere = "DOB = " & CDate("1/2/00")
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere

strWhere = "DOB = #" & CDate("1/2/00") & "#"
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere
End Function

Output (with Short Date defined as dd/mm/yyyy in Control Panel):
1/02/2000 DOB = #02/01/2000#
Null DOB = 1/02/2000
Null DOB = #1/02/2000#

[Dixie]

Well Pieter, I tried all 3 of those and still get the same results. On
31/8/2005, I get the expected result. When I roll the date up to 1/9/2005,
I get no result. I found that if I modify the Dev Ashish module to use any
of these methods, I get no result at all. If I use the original code you
gave me, I get a result for days later than the 12th of each month where you
cant roll the date around. I also tried to use the CDate and Format in the
query as well. I got the same result no matter what I used. In contol
panel, my short date is set to dd/mm/yyyy.

These are the 3 possibilities I have tried in the module

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

The first, which is not commented out here, is the one you gave me
initially. The other 2 are my interpretation of Allen Browne's code.

dixie

<pi********@hot mail.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...

Here... found one from Allen Browne... so it'll work.

4. Allen Browne Oct 12 2000, 11:36 pm show options
Newsgroups: comp.databases. ms-access
From: Allen Browne <abro...@odysse y.apana.org.au> - Find messages by
this author
Date: Fri, 13 Oct 2000 12:38:17 +0800
Local: Thurs, Oct 12 2000 11:38 pm
Subject: Re: Date Format

Michael, my experience suggests that Format() with "#" handles
the case where Access misunderstands the data type, but CDate()
does not:

Function TestDate()
Dim strWhere As String
Dim varResult As Variant

strWhere = "DOB = #" & Format("1/2/00", "mm\/dd\/yyyy") & "#"
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere

strWhere = "DOB = " & CDate("1/2/00")
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere

strWhere = "DOB = #" & CDate("1/2/00") & "#"
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere
End Function

Output (with Short Date defined as dd/mm/yyyy in Control Panel):
1/02/2000 DOB = #02/01/2000#
Null DOB = 1/02/2000
Null DOB = #1/02/2000#

[Bob Quintal]

"Dixie" <di***@dogmail. com> wrote in
news:43******** @duster.adelaid e.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.

--
Bob Quintal

PA is y I've altered my email address.

[Dixie]

My brain must be fading. After staring at this code for ages, I spotted the
mistake I had made in it. The final version has:

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

I also visited Allen Browne's website and read his help article on date
formatting. Very informative.

It works just as it should have now. Thankyou very much Pieter for taking
the time to dig that piece of code out for me.

dixie
<pi********@hot mail.com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...

Here... found one from Allen Browne... so it'll work.

4. Allen Browne Oct 12 2000, 11:36 pm show options
Newsgroups: comp.databases. ms-access
From: Allen Browne <abro...@odysse y.apana.org.au> - Find messages by
this author
Date: Fri, 13 Oct 2000 12:38:17 +0800
Local: Thurs, Oct 12 2000 11:38 pm
Subject: Re: Date Format

Michael, my experience suggests that Format() with "#" handles
the case where Access misunderstands the data type, but CDate()
does not:

Function TestDate()
Dim strWhere As String
Dim varResult As Variant

strWhere = "DOB = #" & Format("1/2/00", "mm\/dd\/yyyy") & "#"
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere

strWhere = "DOB = " & CDate("1/2/00")
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere

strWhere = "DOB = #" & CDate("1/2/00") & "#"
varResult = DLookup("DOB", "tblCustome r", strWhere)
Debug.Print varResult, strWhere
End Function

Output (with Short Date defined as dd/mm/yyyy in Control Panel):
1/02/2000 DOB = #02/01/2000#
Null DOB = 1/02/2000
Null DOB = #1/02/2000#