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(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.

[pietlinden]

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?

[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(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)

[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********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

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?

[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 <[e-mail]> - 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", "tblCustomer", strWhere)
Debug.Print varResult, strWhere

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

strWhere = "DOB = #" & CDate("1/2/00") & "#"
varResult = DLookup("DOB", "tblCustomer", 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********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.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 <[e-mail]> - 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", "tblCustomer", strWhere)
Debug.Print varResult, strWhere

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

strWhere = "DOB = #" & CDate("1/2/00") & "#"
varResult = DLookup("DOB", "tblCustomer", 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.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.

--
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********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.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 <[e-mail]> - 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", "tblCustomer", strWhere)
Debug.Print varResult, strWhere

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

strWhere = "DOB = #" & CDate("1/2/00") & "#"
varResult = DLookup("DOB", "tblCustomer", 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]

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. :-(

[Dixie]

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" <di***@dogmail.com> wrote in message
news:43******@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. :-(

[Tim Marshall]

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

[Dixie]

Just found one more thing I want to filter for in this concatenate module.
It is a field called [Year] which is a format 'General number', field size
'byte'. Can someone tell me what to put in the argument (I think that is
what you call it :-) The following part.

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

the bit, I need to know is what to put for the As part. For example, if I
called it Year As xxx, what would the xxx have to be?

The rest, I'll have a go at myself.

dixie

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:df**********@coranto.ucs.mun.ca...

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

[Dixie]

Don't worry about this one. Didn't need to do this anyway. Next time, I
will think a bit longer before I go for the newsgroup. :-)

dixie

"Dixie" <di***@dogmail.com> wrote in message
news:43********@duster.adelaide.on.net...

Just found one more thing I want to filter for in this concatenate module.
It is a field called [Year] which is a format 'General number', field size
'byte'. Can someone tell me what to put in the argument (I think that is
what you call it :-) The following part.

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

the bit, I need to know is what to put for the As part. For example, if I
called it Year As xxx, what would the xxx have to be?

The rest, I'll have a go at myself.

dixie

"Tim Marshall" <TI****@PurplePandaChasers.Moertherium> wrote in message
news:df**********@coranto.ucs.mun.ca...

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

[Salad]

Bob Quintal wrote:

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

[Bob Quintal]

Salad <oi*@vinegar.com> wrote in
news:9W***************@newsread3.news.pas.earthlin k.net:

Bob Quintal wrote:

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