Splitting control source value string in report

[Ken Warthen]

I have a report that is based on values from a crosstab query in an Access
2007 database. The values of several textbox controls in the report contain
a string value that looks like, "1435|1.00|10 for 10". I'd like to split the
value into separate textbox controls with each textbox containing one of the
three components values separated by the pipe character. Any ideas on how
that could be done?

TIA,

KEn

 

[Stuart McCall]

I have a report that is based on values from a crosstab query in an Access
2007 database. The values of several textbox controls in the report
contain
a string value that looks like, "1435|1.00|10 for 10". I'd like to split
the
value into separate textbox controls with each textbox containing one of
the
three components values separated by the pipe character. Any ideas on how
that could be done?

TIA,

KEn

Use the Split function, like this:

Dim a As Variant

a = Split(StringValue, "|")

You now have an array filled with the separate values (minus the |'s), so
you can go:

Me.Textbox1 = a(0)
Me.Textbox2 = a(1)
Me.Textbox3 = a(2)
etc.

 

[ryguy7272]

You will need a Query! Check this out:
http://www.techonthenet.com/access/questions/parse.php
http://stackoverflow.com/questions/157034/parsing-text-in-ms-access

Also, search on Google for 'MS Access parsing text'.


HTH,
Ryan--

 

[Ken Warthen]

Ryan,

The report is based on a crosstab query. I'm not quite sure what you are
suggesting.

Ken

 

[ryguy7272]

Ok, I'll admit that I haven't worked with Crosstab Queries in a while, and I
know there are some limitations of these types of Queries, but I don't think
that one of the limitations is that you can't sue a Crosstab Query as a
source for another query.

See a list of limitations of Crosstab Queries here:
http://www.databasedev.co.uk/crosstab_queries.html

Use the results of your Crosstab Query as the basis for the Query that I
recommended. The the results of that second Query as your final results.

HTH,
Ryan---

 

[ryguy7272]

Ok, just found an old Crosstab Query that I used a long time ago. I tested
it, and the function works perfect. Change it a bit, to suit your needs,
i.e., use the Pipe Character; like this:
LPosition = InStr(pValue, "|")


Function ParseFirstComp(pValue) As String

Dim LPosition As Integer

'Find postion of underscore
LPosition = InStr(pValue, "|")

'Return the portion of the string before the underscore
If LPosition > 0 Then
ParseFirstComp = Left(pValue, LPosition - 1)
Else
ParseFirstComp = ""
End If

End Function


HTH,
Ryan---

 

[Ken Warthen]

Ryan,

I have a routine for parsing. I don't think that's the problem. If I use
the parsing function at the crosstab level I can only return one part of the
string, that's why I was trying to parse the string at the report level,
hoping to be able to display each of the three parts of the string. If it's
any help, here's the crosstab statement.

Ken


TRANSFORM First(fSplitString([VRD],1)) AS V
SELECT qryVolumeAndPromotionsReport.MVRFSMGRegion,
qryVolumeAndPromotionsReport.Customer
FROM qryVolumeAndPromotionsReport
GROUP BY qryVolumeAndPromotionsReport.MVRFSMGRegion,
qryVolumeAndPromotionsReport.Customer
PIVOT qryVolumeAndPromotionsReport.CompanyWeek;

 

[ryguy7272]

Sorry, that doesn't help. However, if the data is non-confidential and if
you want to send it to me, zip the DB (you can't email non-zipped Access
files), and send it to me. I'll try to take care of it in the next day or so.
(e-mail address removed)

Ryan--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.

Ryan,

I have a routine for parsing. I don't think that's the problem. If I use
the parsing function at the crosstab level I can only return one part of the
string, that's why I was trying to parse the string at the report level,
hoping to be able to display each of the three parts of the string. If it's
any help, here's the crosstab statement.

Ken


TRANSFORM First(fSplitString([VRD],1)) AS V
SELECT qryVolumeAndPromotionsReport.MVRFSMGRegion,
qryVolumeAndPromotionsReport.Customer
FROM qryVolumeAndPromotionsReport
GROUP BY qryVolumeAndPromotionsReport.MVRFSMGRegion,
qryVolumeAndPromotionsReport.Customer
PIVOT qryVolumeAndPromotionsReport.CompanyWeek;

Ok, just found an old Crosstab Query that I used a long time ago. I tested
it, and the function works perfect. Change it a bit, to suit your needs,
i.e., use the Pipe Character; like this:
LPosition = InStr(pValue, "|")


Function ParseFirstComp(pValue) As String

Dim LPosition As Integer

'Find postion of underscore
LPosition = InStr(pValue, "|")

'Return the portion of the string before the underscore
If LPosition > 0 Then
ParseFirstComp = Left(pValue, LPosition - 1)
Else
ParseFirstComp = ""
End If

End Function


HTH,
Ryan---