Concatenate Query Problem

W

welcomesui@gmail.com

Registered User.
Local time
Today, 07:33
Joined
May 3, 2011
Messages
32
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





PHP: 
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
 

Attachments

You haven't explained what it isn't doing? I didn't understand your second sentence.
 
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
 
Show me how you are calling the function in your query.
 
I don't have time to start downloading your db which was why I asked you to copy and paste it on here.
 
vbaInet said:
I don't have time to start downloading your db which was why I asked you to copy and paste it on here.
Click to expand...

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;

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

Attachments

vbaInet said:
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.
Click to expand...

Hi , it is fixed foramt, as mm/dd/yy
 
vbaInet said:
Is 4/25/37 not a date? That is not mm/dd/yy.

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

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
 
What is the problem? I don't have time to start looking for a bug.
 
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:
Code: 
    If Len(strReturn) <> 0 Then
        fConcatValues = Left(strReturn, Len(strReturn) - 2) ' Trim leading space and comma and return the concatenated value
    End If
 
vbaInet said:
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:
Code: 
    If Len(strReturn) <> 0 Then
        fConcatValues = Left(strReturn, Len(strReturn) - 2) ' Trim leading space and comma and return the concatenated value
    End If
Click to expand...

Hi Good Morning,

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

Regards,
Hari
 
You must log in or register to reply here.

Similar threads

Babycat
Long subform behaviour
Replies
11
Views
1,049
Edgar_
E
A
Automation Error
Replies
10
Views
747
The_Doc_Man
The_Doc_Man
K
Solved Query Help
2
Replies
21
Views
1,657
Gasman
Gasman
T
Solved Attache files from Subform in main form to Outlook
Replies
11
Views
988
theinviter
T
mloucel
Solved SQL Help..
Replies
7
Views
1,083
mloucel
mloucel

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom