Concatenate Query Problem

[welcomesui@gmail.com]

Hi All,

I am useing below code to concatenate the different fileds, omit if columns contians % as well as dates.

A small bug found in the code, where the total filed [Expr10] of data unable to concatenate in filed as "Name" (query: 1_1)
attached screenshot and sample dbf.

can you help in this regard.

Attached samples files

Public Function fConcatValues(ParamArray aFields() As Variant)
 
Dim i As Long
Dim strReturn As String
 
For i = LBound(aFields) To UBound(aFields)
   If IsDate(aFields(i)) Then  'Screen out text that looks like a date and date fields
      'Do nothing
   ElseIf InStr(aFields(i) & "", "%") > 0 Then  'Screen out text fields that have a percent sign, does not screen actual number fields that formatted to show percentages
      'Do nothing
   ElseIf Len(aFields(i) & "") > 0 Then  'Skips Nulls and zero-length string fields
      strReturn = strReturn & ", " & aFields(i)
   End If
Next i
 
   fConcatValues = Mid(strReturn, 3)  'Trim leading space and comma and return the concatenated value
End Function

 

[vbaInet]

You haven't explained what it isn't doing? I didn't understand your second sentence.

 

[welcomesui@gmail.com]

Hi,

It concatenate all the fields, except % and Dates, it gives final output in filed as "Name", it does not concatenate few cloumns in filed "Expr10" whre it conitans some text. Please refer filed "Expr10" and "Name" you can understand.

Regards,
Hari

 

[vbaInet]

Show me how you are calling the function in your query.

 

[welcomesui@gmail.com]

Show me how you are calling the function in your query.

You can Find in atached Sample.zip file contians sample Access file.

please check.

Regards,
Hari

 

[vbaInet]

I don't have time to start downloading your db which was why I asked you to copy and paste it on here.

 

[welcomesui@gmail.com]

I don't have time to start downloading your db which was why I asked you to copy and paste it on here.

It's Ok.

I have two queries..

query :1

SELECT FBD.L1, FBD.L2, GetCSWord([L1],1) AS Expr1, GetCSWord([L1],2) AS Expr2, GetCSWord([L1],3) AS Expr3, GetCSWord([L1],4) AS Expr4, GetCSWord([L1],5) AS Expr5, GetCSWord([L1],6) AS Expr6, GetCSWord([L1],7) AS Expr7, GetCSWord([L1],8) AS Expr8, "%" AS Expr9, GetCSWord([L2],1) AS Expr10, GetCSWord([L2],2) AS Expr11, GetCSWord([L2],3) AS Expr12, GetCSWord([L2],4) AS Expr13, GetCSWord([L2],5) AS Expr14, GetCSWord([L2],6) AS Expr15, GetCSWord([L2],7) AS Expr16, GetCSWord([L2],8) AS Expr17, GetCSWord([L2],9) AS Expr18
FROM FBD
GROUP BY FBD.L1, FBD.L2, GetCSWord([L1],1), GetCSWord([L1],2), GetCSWord([L1],3), GetCSWord([L1],4), GetCSWord([L1],5), GetCSWord([L1],6), GetCSWord([L1],7), GetCSWord([L1],8), "%", GetCSWord([L2],1), GetCSWord([L2],2), GetCSWord([L2],3), GetCSWord([L2],4), GetCSWord([L2],5), GetCSWord([L2],6), GetCSWord([L2],7), GetCSWord([L2],8), GetCSWord([L2],9);

Query :1_1
SELECT [1].L1, [1].L2, [1].Expr1, [1].Expr2, [1].Expr3, [1].Expr4, [1].Expr5, [1].Expr6, [1].Expr7, [1].Expr8, [1].Expr9, [1].Expr10, [1].Expr11, [1].Expr12, [1].Expr13, [1].Expr14, [1].Expr15, [1].Expr16, [1].Expr17, [1].Expr18, fConcatValues([Expr1],[Expr2],[Expr3],[Expr4],[Expr5],[Expr6],[Expr7],[Expr8],[Expr9],[Expr10],[Expr11],[Expr12],[Expr13],[Expr14],[Expr15],[Expr16],[Expr17],[Expr18]) AS Name
FROM 1;

Function GetCSWord(ByVal S, Indx As Integer)
      ' Returns the nth word in a specific field.
      Dim WC As Integer, Count As Integer, SPos As Integer, EPos As Integer
         WC = CountCSWords(S)
         If Indx < 1 Or Indx > WC Then
           GetCSWord = Null
           Exit Function
         End If
         Count = 1
         SPos = 1
         For Count = 2 To Indx
           SPos = InStr(SPos, S, ",") + 1
         Next Count
         EPos = InStr(SPos, S, ",") - 1
         If EPos <= 0 Then EPos = Len(S)
         GetCSWord = Trim(Mid(S, SPos, EPos - SPos + 1))
      End Function

this Function splits the comma seperated values to different columns.

Please check

Regards,
Hari

 

[vbaInet]

I can see what's happening now. It seems to think that 2005-4 is a date.

What is the format of your date? Are they all a fixed format of dd/mm/yyyy?

In the meantime, see attached a workaround.

 

[welcomesui@gmail.com]

I can see what's happening now. It seems to think that 2005-4 is a date.

What is the format of your date? Are they all a fixed format of dd/mm/yyyy?

In the meantime, see attached a workaround.

Hi , it is fixed foramt, as mm/dd/yy

 

[vbaInet]

Hi , it is fixed foramt, as mm/dd/yy

Is 4/25/37 not a date? That is not mm/dd/yy.

Did you try the db I posted? No feedback on that.

 

[welcomesui@gmail.com]

Is 4/25/37 not a date? That is not mm/dd/yy.

Did you try the db I posted? No feedback on that.

Yes.. you are correct.. I would take date from another field, (other field in mm/dd/yy format) not from this..this function also skip's the date where ever it in splitted fields..

about your sample file..I am checking with...

Regards,
Hari

 

[vbaInet]

Yes.. you are correct.. I would take date from another field, (other field in mm/dd/yy format) not from this..this function also skip's the date where ever it in splitted fields..

Alright Hari, just use the code I provided.

 

[welcomesui@gmail.com]

Alright Hari, just use the code I provided.

Hi vbaInet,

There is another bug found while useing with large data..
as attached..

Can you try to solve...

Regards
Hari

 

[vbaInet]

What is the problem? I don't have time to start looking for a bug.

 

[welcomesui@gmail.com]

What is the problem? I don't have time to start looking for a bug.

if the data filed "L1" contains "-" between the text like "Broke-Hop" or "Dude-Like" it stops the macro.

Regards,
Hari

 

[vbaInet]

Does it error? If it is, what's the error message?

 

[welcomesui@gmail.com]

Does it error? If it is, what's the error message?

It is Run-time error '5'

Invalied procedure call or arument.

End Debug

Regads
Hari

 

[vbaInet]

The hyphen character is not the problem. None of your ExprX fields satisfy the criteria so nothing is returned and the Left() function breaks when you give it nothing.

Use:

    If Len(strReturn) <> 0 Then
        fConcatValues = Left(strReturn, Len(strReturn) - 2) ' Trim leading space and comma and return the concatenated value
    End If

 

[welcomesui@gmail.com]

The hyphen character is not the problem. None of your ExprX fields satisfy the criteria so nothing is returned and the Left() function breaks when you give it nothing.

Use:

    If Len(strReturn) <> 0 Then
        fConcatValues = Left(strReturn, Len(strReturn) - 2) ' Trim leading space and comma and return the concatenated value
    End If

Hi Good Morning,

It retruns Null values in filed if we use above code.
Can you check once.

Regards,
Hari

 

[vbaInet]

Good morning to you.

It retruns Null values in filed if we use above code.

This is because ...

None of your ExprX fields satisfy the criteria so nothing is returned and the Left() function breaks when you give it nothing.

Expr1 to ExprX have invalid values (date, "%") hence it displays nothing.