office xp application

  • Thread starter Thread starter ted
  • Start date Start date
T

ted

We are printing labels from a table. However, we have a field with info
separated with comma's. Would like to search for the appropriate info &
transform it to different text. Suggestions for office xp or perhaps there
is something in office 2007 & we could upgrade. TIA
 
Can you provide us with some examples of the data and what you want to do
with the data?
 
Ken Snell (MVP) said:
Can you provide us with some examples of the data and what you want to do
with the data?
Click to expand...
The field looks like this: ,071a,075b,0711e, ...
where the 07 is the year 2007, the number folowing the year is the month &
the alpha represents a region of the usa (1 thru 6)

The logic in basic: we would get the date() & extract the year & month. then
search the field for (ie ',075?,' for 2007 & may)
finding that we would extract the ? (alpha) and turn the a=1,b=2 ...
plan on printing on the label 'Region1' for the a=1 among the fields we
already print. TIA
 
Let me be sure I understand. The initial query to initially select records
would look something like this (searching for the year/date fragment
followed by a letter):

SELECT T.*
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


However, the trick now is to extract the appropriate "year/date" and
"trailing letter" fragment from the FieldWIthCommaValues. The problem is
that 071 will "match" both a 071 and a 0711 string. It's not really possible
to do this with the built-in VBA functions (InStr, etc.), so we'll need to
use a user-defined function to extract the desired fragment, convert the
"region" letter to a number, and return it to the query.

Put the following function in a regular module in your database file:

Public Function GetRegionValue(ByVal strFieldValue As String, _
ByVal strYearMonth As String) As String
Dim lngLoc As Long, lngLong As Long
Dim strTemp As String, strLetter As String
lngLoc = 1
lngLong = Len(strFieldValue)
Do
lngLoc = InStr(lngLoc, strFieldValue, strYearMonth, vbTextCompare)
strTemp = Mid(strFieldValue, lngLoc, Len(strYearMonth) + 1)
strLetter = Right(strTemp, 1)
If strLetter Like "[a-z]" Then Exit Do
lngLoc = lngLoc + 1
If lngLoc > lngLong Then
GetRegionValue = "invalid Region"
Exit Function
End If
Loop
GetRegionValue = "Region " & CStr(Asc(UCase(strLetter)) - _
Asc("A") + 1)
Exit Function
End Function


Then we need to call this function in your query:

SELECT T.*,
GetRegionValue(T.FieldWithCommaValues, Format(Date(), "yym"))
AS RegionNumber
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


I've not tested/debugged this function, so you may find it needs a bit of
tweaking.


I assume that you know that this field's contents violate normalization
rules for a relational database because you are not storing a single value
in a single field in a single record. If you were using a normalized
structure, the query to do what you seek would be so very much easier and
less complex, and wouldn't require the use of the user-defined function.
 
Ok I think I have most of this. However, we currently do a query with:
'docmd.openreport reportname, view, query' in a basic program.
So looks like I have to meld our query with your suggestion.
Currently the report gets fields from each record to print the label.
What I do not understand is how I get 'regionnumber' on the label for
printing.
You have opened a whole new world of label printing to me. Thanks!

Ken Snell (MVP) said:
Let me be sure I understand. The initial query to initially select records
would look something like this (searching for the year/date fragment
followed by a letter):

SELECT T.*
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


However, the trick now is to extract the appropriate "year/date" and
"trailing letter" fragment from the FieldWIthCommaValues. The problem is
that 071 will "match" both a 071 and a 0711 string. It's not really
possible to do this with the built-in VBA functions (InStr, etc.), so
we'll need to use a user-defined function to extract the desired fragment,
convert the "region" letter to a number, and return it to the query.

Put the following function in a regular module in your database file:

Public Function GetRegionValue(ByVal strFieldValue As String, _
ByVal strYearMonth As String) As String
Dim lngLoc As Long, lngLong As Long
Dim strTemp As String, strLetter As String
lngLoc = 1
lngLong = Len(strFieldValue)
Do
lngLoc = InStr(lngLoc, strFieldValue, strYearMonth, vbTextCompare)
strTemp = Mid(strFieldValue, lngLoc, Len(strYearMonth) + 1)
strLetter = Right(strTemp, 1)
If strLetter Like "[a-z]" Then Exit Do
lngLoc = lngLoc + 1
If lngLoc > lngLong Then
GetRegionValue = "invalid Region"
Exit Function
End If
Loop
GetRegionValue = "Region " & CStr(Asc(UCase(strLetter)) - _
Asc("A") + 1)
Exit Function
End Function


Then we need to call this function in your query:

SELECT T.*,
GetRegionValue(T.FieldWithCommaValues, Format(Date(), "yym"))
AS RegionNumber
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


I've not tested/debugged this function, so you may find it needs a bit of
tweaking.


I assume that you know that this field's contents violate normalization
rules for a relational database because you are not storing a single value
in a single field in a single record. If you were using a normalized
structure, the query to do what you seek would be so very much easier and
less complex, and wouldn't require the use of the user-defined function.

--

Ken Snell
<MS ACCESS MVP>



ted said:
The field looks like this: ,071a,075b,0711e, ...
where the 07 is the year 2007, the number folowing the year is the month
& the alpha represents a region of the usa (1 thru 6)

The logic in basic: we would get the date() & extract the year & month.
then search the field for (ie ',075?,' for 2007 & may)
finding that we would extract the ? (alpha) and turn the a=1,b=2 ...
plan on printing on the label 'Region1' for the a=1 among the fields we
already print. TIA
Click to expand...
Click to expand...
 
You can use this setup with your current method of opening the report. You
need to replace your current query with the one that I've posted (after
changing the generic names to the real names for fields, table, etc.).

If you put the public function in a regular module (Modules object), then
your query can call it and get the appropriate records.

In your report, where you want the Region number information to be
displayed, bind that textbox to the RegionNumber field in my query.
--

Ken Snell
<MS ACCESS MVP>




ted said:
Ok I think I have most of this. However, we currently do a query with:
'docmd.openreport reportname, view, query' in a basic program.
So looks like I have to meld our query with your suggestion.
Currently the report gets fields from each record to print the label.
What I do not understand is how I get 'regionnumber' on the label for
printing.
You have opened a whole new world of label printing to me. Thanks!

Ken Snell (MVP) said:
Let me be sure I understand. The initial query to initially select
records would look something like this (searching for the year/date
fragment followed by a letter):

SELECT T.*
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


However, the trick now is to extract the appropriate "year/date" and
"trailing letter" fragment from the FieldWIthCommaValues. The problem is
that 071 will "match" both a 071 and a 0711 string. It's not really
possible to do this with the built-in VBA functions (InStr, etc.), so
we'll need to use a user-defined function to extract the desired
fragment, convert the "region" letter to a number, and return it to the
query.

Put the following function in a regular module in your database file:

Public Function GetRegionValue(ByVal strFieldValue As String, _
ByVal strYearMonth As String) As String
Dim lngLoc As Long, lngLong As Long
Dim strTemp As String, strLetter As String
lngLoc = 1
lngLong = Len(strFieldValue)
Do
lngLoc = InStr(lngLoc, strFieldValue, strYearMonth, vbTextCompare)
strTemp = Mid(strFieldValue, lngLoc, Len(strYearMonth) + 1)
strLetter = Right(strTemp, 1)
If strLetter Like "[a-z]" Then Exit Do
lngLoc = lngLoc + 1
If lngLoc > lngLong Then
GetRegionValue = "invalid Region"
Exit Function
End If
Loop
GetRegionValue = "Region " & CStr(Asc(UCase(strLetter)) - _
Asc("A") + 1)
Exit Function
End Function


Then we need to call this function in your query:

SELECT T.*,
GetRegionValue(T.FieldWithCommaValues, Format(Date(), "yym"))
AS RegionNumber
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


I've not tested/debugged this function, so you may find it needs a bit of
tweaking.


I assume that you know that this field's contents violate normalization
rules for a relational database because you are not storing a single
value in a single field in a single record. If you were using a
normalized structure, the query to do what you seek would be so very much
easier and less complex, and wouldn't require the use of the user-defined
function.

--

Ken Snell
<MS ACCESS MVP>



ted said:
Can you provide us with some examples of the data and what you want to
do with the data?

The field looks like this: ,071a,075b,0711e, ...
where the 07 is the year 2007, the number folowing the year is the month
& the alpha represents a region of the usa (1 thru 6)

The logic in basic: we would get the date() & extract the year & month.
then search the field for (ie ',075?,' for 2007 & may)
finding that we would extract the ? (alpha) and turn the a=1,b=2 ...
plan on printing on the label 'Region1' for the a=1 among the fields we
already print. TIA

--

Ken Snell
<MS ACCESS MVP>


We are printing labels from a table. However, we have a field with
info separated with comma's. Would like to search for the appropriate
info & transform it to different text. Suggestions for office xp or
perhaps there is something in office 2007 & we could upgrade. TIA

--
_______________________________
In Christ's matchless name
ted & colleen
n6trf kc6rue
Click to expand...
Click to expand...
Click to expand...
 
Or, delete the "query" name from the DoCmd.OpenReport call, and instead use
the query I provided as the RecordSource of the report (it's best if you
create and save the query in the fiile -- name it qryLabelsByRegion or
something like that, and then use the query name as the RecordSource of the
report).

--

Ken Snell
<MS ACCESS MVP>


Ken Snell (MVP) said:
You can use this setup with your current method of opening the report. You
need to replace your current query with the one that I've posted (after
changing the generic names to the real names for fields, table, etc.).

If you put the public function in a regular module (Modules object), then
your query can call it and get the appropriate records.

In your report, where you want the Region number information to be
displayed, bind that textbox to the RegionNumber field in my query.
--

Ken Snell
<MS ACCESS MVP>




ted said:
Ok I think I have most of this. However, we currently do a query with:
'docmd.openreport reportname, view, query' in a basic program.
So looks like I have to meld our query with your suggestion.
Currently the report gets fields from each record to print the label.
What I do not understand is how I get 'regionnumber' on the label for
printing.
You have opened a whole new world of label printing to me. Thanks!

Ken Snell (MVP) said:
Let me be sure I understand. The initial query to initially select
records would look something like this (searching for the year/date
fragment followed by a letter):

SELECT T.*
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


However, the trick now is to extract the appropriate "year/date" and
"trailing letter" fragment from the FieldWIthCommaValues. The problem is
that 071 will "match" both a 071 and a 0711 string. It's not really
possible to do this with the built-in VBA functions (InStr, etc.), so
we'll need to use a user-defined function to extract the desired
fragment, convert the "region" letter to a number, and return it to the
query.

Put the following function in a regular module in your database file:

Public Function GetRegionValue(ByVal strFieldValue As String, _
ByVal strYearMonth As String) As String
Dim lngLoc As Long, lngLong As Long
Dim strTemp As String, strLetter As String
lngLoc = 1
lngLong = Len(strFieldValue)
Do
lngLoc = InStr(lngLoc, strFieldValue, strYearMonth, vbTextCompare)
strTemp = Mid(strFieldValue, lngLoc, Len(strYearMonth) + 1)
strLetter = Right(strTemp, 1)
If strLetter Like "[a-z]" Then Exit Do
lngLoc = lngLoc + 1
If lngLoc > lngLong Then
GetRegionValue = "invalid Region"
Exit Function
End If
Loop
GetRegionValue = "Region " & CStr(Asc(UCase(strLetter)) - _
Asc("A") + 1)
Exit Function
End Function


Then we need to call this function in your query:

SELECT T.*,
GetRegionValue(T.FieldWithCommaValues, Format(Date(), "yym"))
AS RegionNumber
FROM TableName AS T
WHERE T.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";


I've not tested/debugged this function, so you may find it needs a bit
of tweaking.


I assume that you know that this field's contents violate normalization
rules for a relational database because you are not storing a single
value in a single field in a single record. If you were using a
normalized structure, the query to do what you seek would be so very
much easier and less complex, and wouldn't require the use of the
user-defined function.

--

Ken Snell
<MS ACCESS MVP>




Can you provide us with some examples of the data and what you want to
do with the data?

The field looks like this: ,071a,075b,0711e, ...
where the 07 is the year 2007, the number folowing the year is the
month & the alpha represents a region of the usa (1 thru 6)

The logic in basic: we would get the date() & extract the year & month.
then search the field for (ie ',075?,' for 2007 & may)
finding that we would extract the ? (alpha) and turn the a=1,b=2 ...
plan on printing on the label 'Region1' for the a=1 among the fields we
already print. TIA

--

Ken Snell
<MS ACCESS MVP>


We are printing labels from a table. However, we have a field with
info separated with comma's. Would like to search for the appropriate
info & transform it to different text. Suggestions for office xp or
perhaps there is something in office 2007 & we could upgrade. TIA

--
_______________________________
In Christ's matchless name
ted & colleen
n6trf kc6rue
Click to expand...
Click to expand...
Click to expand...
 
Its more difficult than that. We have to query with our current selects &
with your selects. So we have to think how that will be done. Thanks so much
for your help.
 
Perhaps it would be a good time to restructure the data to make it
normalized, which will greatly faciliate the queries that you need to use?
 
Ken Snell (MVP) said:
Perhaps it would be a good time to restructure the data to make it
normalized, which will greatly faciliate the queries that you need to use?
Click to expand...

I have been thinking about that. What would you suggest? Put each into a sub
field or ... . TIA
 
I would create a new table with two fields in it (if you want to continue to
use the yymA format for the data), or with more fields if you want to split
out the year, month, and region letter into separate fields. If you go with
just two, one field would be a foreign key to hold the primary key value for
the "parent" record from your current table, and the second field would be
ONE value from the comma-separated field in your current table:

tblMemberLabelInfo
MemberID (foreign key to your current table)
MemberLabelInfo

You then would create one record for each value in your current field for
one member. Repeat for each member. You then would delete the current field
from the current table.

(Unless there is some overriding reason to retain your current format for
these values, I'd be inclined to separate the values into three fields, not
one -- one field for year, one field for month, and one field for region
letter or number.)

Then the query to select the records would be something like this:

SELECT T.*,
(Asc(UCase(Right(TT.FieldWithCommaValues, 1))) - Asc("A") + 1)
AS RegionNumber
FROM TableName AS T INNER JOIN NewTableName AS TT
ON T.MemberID = TT.MemberID
WHERE TT.FieldWithCommaValues Like "*" &
Format(Date(), "yym") & "[a-z]*";
 
tblMemberLabelInfo
MemberID (foreign key to your current table)
MemberLabelInfo

You then would create one record for each value in your current field for
one member. Repeat for each member. You then would delete the current
field from the current table.
Click to expand...

Ok I think I will do this. One question, is the memberid just a number as it
is in the mother table or is there another field form that I am unaware of.
I do see how I can fill this table from a basic program so that should be no
problem. While I am at it there are several fields in the mother that should
be converted to this form. That will take some time. I do thank you for your
help.
 
MemberID in this table should be the same data type as is the MemberID field
in the parent table. And the values that go into the MemberID field in this
table should be the same values as are in the parent table -- that allows
the child table to "link" to the correct record in the parent table -- when
the MemberID values in both tables match, those records are related to each
other.
--

Ken Snell
<MS ACCESS MVP>
 
Back
Top