Concatenating text fields with a WHERE condition
in [Help]
|
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 1 Sep 2005 22:26 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 1 Sep 2005 23:21 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 1 Sep 2005 23:46 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 5 Sep 2005 10:49 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 17 Sep 2005 10:06
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. |