Add a word to the beginning of a field? **

[Guest]

In one of my tables, I have a field called page. The field is full of page
numbers, for example , "010," "G-4," "C33," "238," etc.

I'd like to make it so that all of them say, "Catalog Page 010," "Catalog
Page G-4," "Catalog Page C33," "Catalog Page 238," etc.

You wouldn't use an append query, because that would append records to the
table. And, I don't want to search and replace for every page number. How
could I do this?

thanks much

Jarrod

 

[Randy]

In one of my tables, I have a field called page. The field is full of
page
numbers, for example , "010," "G-4," "C33," "238," etc.

I'd like to make it so that all of them say, "Catalog Page 010," "Catalog
Page G-4," "Catalog Page C33," "Catalog Page 238," etc.

You wouldn't use an append query, because that would append records to the
table. And, I don't want to search and replace for every page number.
How
could I do this?

thanks much

Jarrod

Try an Update query on field [Page] with new value equal to "Catalog Page "
& [Page]

-Randy

 

[fredg]

In one of my tables, I have a field called page. The field is full of page
numbers, for example , "010," "G-4," "C33," "238," etc.

I'd like to make it so that all of them say, "Catalog Page 010," "Catalog
Page G-4," "Catalog Page C33," "Catalog Page 238," etc.

You wouldn't use an append query, because that would append records to the
table. And, I don't want to search and replace for every page number. How
could I do this?

thanks much

Jarrod

Jarrod,
This is kind of silly.
You are using repetitive text in a field, which merely wastes space.
Save the page numbers as is.
Whenever you need to show them simply concatenate them with the text
in an unbound control:
="Catalog Page " & [Page]
or place a label in front of the field with the caption of:
"Catalog Page ".

Note: Page is an Access property and you will not get the results you
expect if you retain that name as a Field Name.
I would suggest you change it to something else, i.e. [CatalogPage] or
perhaps [PageNum].

 

[Guest]

Fred,

I can see your point. Although, I'm using this table to manage data used to
create a "products file" for an online store. The file needs to contain all
data that will be displayed on the web.

Jarrod

In one of my tables, I have a field called page. The field is full of page
numbers, for example , "010," "G-4," "C33," "238," etc.

I'd like to make it so that all of them say, "Catalog Page 010," "Catalog
Page G-4," "Catalog Page C33," "Catalog Page 238," etc.

You wouldn't use an append query, because that would append records to the
table. And, I don't want to search and replace for every page number. How
could I do this?

thanks much

Jarrod

Jarrod,
This is kind of silly.
You are using repetitive text in a field, which merely wastes space.
Save the page numbers as is.
Whenever you need to show them simply concatenate them with the text
in an unbound control:
="Catalog Page " & [Page]
or place a label in front of the field with the caption of:
"Catalog Page ".

Note: Page is an Access property and you will not get the results you
expect if you retain that name as a Field Name.
I would suggest you change it to something else, i.e. [CatalogPage] or
perhaps [PageNum].

 

[Albert D. Kallal]

Are you saying that you have a field with ONE value, like:

Page
010
G-4
C33

Or, are you saying you have a field like:

Page
"010," "G-4," "C33," "238,"

Also, are you sure the comma are as above?
Or, do you have field like:

010, G-4, C33, 238

The format is important here (I can easily post a solution for either
case...but lets determine what the data looks like. If you have all the
quotes...then carefully post an example of what the data looks like. (you
use the split() command here to parse this out for you..but you do need to
post what the data looks like..).

You have:
Page
"010," "G-4," "C33," "238,"

You sure, you don't have:

Page
"010", "G-4", "C33", "238"

 

[John Vinson]

I can see your point. Although, I'm using this table to manage data used to
create a "products file" for an online store. The file needs to contain all
data that will be displayed on the web.

Jarrod

You're apparently making the assumption that the data must exist in a
Table in exactly the format that you want it to appear on the web
page.

That assumption is *incorrect*.

The web page can and should be based on a Query, not directly on a
table.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I'm not necessarily making an assumption. I do understand what you're
saying. But at the same time, maybe I didn't make it clear that the web
pages are based on calls made to a tab-delimited .txt file.

Jarrod

 

[John Vinson]

I'm not necessarily making an assumption. I do understand what you're
saying. But at the same time, maybe I didn't make it clear that the web
pages are based on calls made to a tab-delimited .txt file.

My point stands: it's just as easy, or easier, to export from a Query
to a tab-delimited text file than it is to export from a Table to such
a file.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

"010", "G-4", "C33", "238"

This is what I have. And there's actually more to it. Ultimately, the
numbers preceded by letters should say "Supplement Page" and the ones that
don't have a letter should say "Catalog Page."

Thoughts? Thanks for all the feedback.

Jarrod

 

[Guest]

I see, so you're saying that to create the .txt file I need, I don't need to
make a table and then export it, I could just use the query?

I haven't thought of doing that, probably because I don't understand how to
use controls (which I thought were for forms).

The unbound control "="Catalog Page " & [Page]
" is something that would occur whenever the query results are exported?
How do I create this unbound control?

Also, there is a little more to the task. The field contains data like
this:"010", "G-4", "C33", "238"
The entries where numbers are preceded by a letter should say "Supplement
Page xxx" while the entries that don't contain letters should say "Catalog
Page XXX"

Would I need to also use an IIF?

Thanks for all the feedback.

Jarrod

 

[Albert D. Kallal]

Ok. (it was imporant to get the "exact" format).

I would build a function.

Just make a query, and add a new collum called:


MyG:CataText([ThatFieldNameWithTheDataStringGoesHere)

here is the function you can place in a standard module:


Public Function CataText(v As Variant) As Variant

Dim vBuf As Variant
Dim i As Integer
Dim strResult As String
Dim q As String
Dim strT As String

q = Chr$(34)

If IsNull(v) = True Then
Exit Function
End If

vBuf = Split(v, ",")

For i = 0 To UBound(vBuf, 1)
' grab text inside of the " "
strT = Split(vBuf(i), q)(1)
If IsNumeric(Left(strT, 1)) = True Then
strT = q & "Supplement Page " & strT & q
Else
strT = q & "Catalog Page." & strT & q
End If

If strResult <> "" Then
strResult = strResult & ", "
End If
strResult = strResult & strT
Next i

CataText = strResult

End Function

The above will return your string with eh correct txt as you wanted. If you
put the exprsson in the query..then you don't even need to modify the
data...

You can use that query to export the data, or whatever..

 

[John Vinson]

I see, so you're saying that to create the .txt file I need, I don't need to
make a table and then export it, I could just use the query?
Exactly.

I haven't thought of doing that, probably because I don't understand how to
use controls (which I thought were for forms).
Hum?

The unbound control "="Catalog Page " & [Page]
" is something that would occur whenever the query results are exported?
How do I create this unbound control?

Just put a Textbox on a Form. Leave its Control Source property blank.
That's an "unbound" control.

Also, there is a little more to the task. The field contains data like
this:"010", "G-4", "C33", "238"
The entries where numbers are preceded by a letter should say "Supplement
Page xxx" while the entries that don't contain letters should say "Catalog
Page XXX"

Would I need to also use an IIF?

Iif(IsNumeric([field], "Catalog Page ", "Supplement Page ") & [field]

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

The unbound control "="Catalog Page " & [Page]

Just put a Textbox on a Form. Leave its Control Source property blank.
That's an "unbound" control.

Okay, so I'll leave the page numbers in the same format that I get them
(from the a different db). I'll put a control on the field, so that when I
query my tables to make the .txt file, the page numbers will concatenate with
the proper text and display correctly on the web.

But, how do I actually create the control, on this field, in this query?
I'm in design mode, looking at the query that assembles all the data for the
..txt file. Now what?

Is this like building a function where you input a string in the field row?

Jarrod

 

[Guest]

Some relevant info. I import the "page number" field data from an outside
MAS90 database (That's part of the reason the data needs any work at all
before using). It sits in a table called terms which (after being "cleaned")
get's queried to make the products .txt file.

I see, so you're saying that to create the .txt file I need, I don't need to
make a table and then export it, I could just use the query?
Exactly.

I haven't thought of doing that, probably because I don't understand how to
use controls (which I thought were for forms).
Hum?

The unbound control "="Catalog Page " & [Page]
" is something that would occur whenever the query results are exported?
How do I create this unbound control?

Just put a Textbox on a Form. Leave its Control Source property blank.
That's an "unbound" control.

Also, there is a little more to the task. The field contains data like
this:"010", "G-4", "C33", "238"
The entries where numbers are preceded by a letter should say "Supplement
Page xxx" while the entries that don't contain letters should say "Catalog
Page XXX"

Would I need to also use an IIF?

Iif(IsNumeric([field], "Catalog Page ", "Supplement Page ") & [field]

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

But, how do I actually create the control, on this field, in this query?
I'm in design mode, looking at the query that assembles all the data for the
.txt file. Now what?

Is this like building a function where you input a string in the field row?

Exactly. You'ld just put the IIF() expression in a vacant Field cell
in the query grid.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

A couple more questions:

When I enter this"

Iif(IsNumeric([PageNum], "Catalog Page ", "Supplement Page ") & [PageNum]

It tells me that the expression contains a funtion with the wrong number of
arguments. Is there something that doesn't need to be there, or something
missing?

Also, with the info in this expression, how does the query know where to get
the page number info? Would I need to use the [xxx].[xxx] format to indicate
where that info is? (PageNum is the field, but there's no table specified...)

And how does it know what to name this field?

Jarrod

 

[John Vinson]

A couple more questions:

When I enter this"

Iif(IsNumeric([PageNum], "Catalog Page ", "Supplement Page ") & [PageNum]

It tells me that the expression contains a funtion with the wrong number of
arguments. Is there something that doesn't need to be there, or something
missing?

My error: missing a close parenthesis. Should be

Iif(IsNumeric([PageNum]), "Catalog Page ", "Supplement Page ") &
[PageNum]

Also, with the info in this expression, how does the query know where to get
the page number info? Would I need to use the [xxx].[xxx] format to indicate
where that info is? (PageNum is the field, but there's no table specified...)

Unless you have several fields all named PageNum, it shouldn't be
required. YOu can certainly specify the table name if you wish.

And how does it know what to name this field?

In a Query field, you would put

NewFieldName:

before the expression. If you're just using this as the control source
for a Report textbox, you don't need to put the expression in the
Query at all; just precede the expression with an equals sign = and
use it as the Control Source of a textbox.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I tried entering the expression and it works (thanks). But, I think I forgot
to take one thing into account.

Some of the records do not have any data in this particular field (Some
products are not in any printed catalogs). For those records, the field
needs to remain NULL. The expression we've been discussing, adds "Supplement
Page" to the NULL fields. So scrolling down the column it looks like:

Catalog Page 225
Catalog Page 115
Supplement Page G-6
Catalog Page 027
Supplement Page
Supplement Page
Supplement Page D48

Thoughts?

Jarrod

A couple more questions:

When I enter this"

Iif(IsNumeric([PageNum], "Catalog Page ", "Supplement Page ") & [PageNum]

It tells me that the expression contains a funtion with the wrong number of
arguments. Is there something that doesn't need to be there, or something
missing?

My error: missing a close parenthesis. Should be

Iif(IsNumeric([PageNum]), "Catalog Page ", "Supplement Page ") &
[PageNum]

Also, with the info in this expression, how does the query know where to get
the page number info? Would I need to use the [xxx].[xxx] format to indicate
where that info is? (PageNum is the field, but there's no table specified...)

Unless you have several fields all named PageNum, it shouldn't be
required. YOu can certainly specify the table name if you wish.

And how does it know what to name this field?

In a Query field, you would put

NewFieldName:

before the expression. If you're just using this as the control source
for a Report textbox, you don't need to put the expression in the
Query at all; just precede the expression with an equals sign = and
use it as the Control Source of a textbox.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

Some of the records do not have any data in this particular field (Some
products are not in any printed catalogs). For those records, the field
needs to remain NULL.

So nest the IIF's:

Iif(IsNull([PageNum], "",
Iif(IsNumeric([PageNum]), "Catalog Page ", "Supplement Page ") &
[PageNum]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

I entered this:

PageNum: Iif(IsNull([PageNum]), "", Iif(IsNumeric([PageNum]), "Catalog Page
", "Supplement Page ") & [PageNum]

And I got this error:
"Circular reference caused by alias 'PageNum' in query definition's SELECT
list."

What does that mean?

Thanks for the continuing assistance.

Jarrod

Some of the records do not have any data in this particular field (Some
products are not in any printed catalogs). For those records, the field
needs to remain NULL.

So nest the IIF's:

Iif(IsNull([PageNum], "",
Iif(IsNumeric([PageNum]), "Catalog Page ", "Supplement Page ") &
[PageNum]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps