Access: lower case from one field into another field, same table 1

[Ankroson]

This should be a simple enough thing, but I can't get it to work: I have an area input on a form that goes in in mixed-case (caps and lower). This is an address line. I need to convert it to an upper-case format in another field, for mailings, and I want to use the same table.

This is on Access (Office) 2010, running an Access (XP pro) data base. I've tried many work-arounds, and I think this should be a very simple thing, but got a mental-job on this one. Anyone?

 

[dhookom]

Don't be concerned about how the value is stored. If you need the values to display in upper-case you can either set the Format property or:

=UCase([YourFieldNameHere])

Duane
Hook'D on Access
MS Access MVP

 

[Ankroson]

Hi Duane: Thanks for the response. This is the structure and formula that I have used before " =UCase([Addr1]) ".

I tried it in the Validation field of the record properties. It came back with error "invalid SQL syntax-cannot use multiple column-level CHECK constraint".

I tried various methods of using the format area, and considered creating a mask but those ideas didn't work as it does'nt draw the information from the other column (Addr1).

I considered the fact that this is a Validation" rather than a conversion, and so I tried the same formula in the "default' field of the properties, and it erred: " database engine does not recognize either the field 'Addr1' in a validation expression, or the default value in the table Main."

(Yes, the field that I am trying to convert from (mixed)lower case is called "Addr1" being the line one of the address fields. The field (column) into which I am trying to convert the data into uppercase is LABEL_1 (exactly that: I want labels printed to be in CAPS, hence this exercise in futility so far), and the table is called Main.

To review the scenario again:

I have a small (population 6200 rows) database. All of the data is in one table. Addr1 and Addr2: 1 =line1 address (building# Street#, Street name) and 2 = line2 address (PO Box#s)are the two address fields of concern.

The only two 'redundant' fields are the other expressions of
Addr1 and Addr2, which are LABEL_1 and LABEL_2, which must >print< in CAPS. I don't much care how it displays on the screen, but it must be stored and printable in CAPS.

The form where data is input to is input normally: caps for first letters of names, etc, and lower-case for the remainder. We need that data in that format for typical correspondence. However, for automated mail-scanning, the address must be in CAPS when run off for the postman. Prior to this, I just exported the whole database to Excel, did an "Upper(xxxx)" to convert the lines to CAPS, and re-imported it or 'updated' it to the LABEL fields.

That's wearing thin, and so am I.

Again: I thank you very much for your input, but it doesn't seem to work in the situation. Other suggestions, please?

 

[dhookom]

Why does the value have to be stored in CAPS? You only need to convert the display of the value in your report. For instance if you want a text box to display uppercase of [Addr1], just set the Control Source to:

=UCase([Addr1])

Make sure the name of the text box is not the name of the field.

You could also simply set the format property to
[tt] Format: >[/tt]


Duane
Hook'D on Access
MS Access MVP

 

[Ankroson]

Hi Duane. It needs to be stored in CAPS for an export and data-merge at my printers, and in a different column than the original text. Setting the Format to > displys it in CAPS but does not print it in Caps. Again: the column itself must be converted to caps, based on the other column. Thanks again.

 

[dhookom]

I still don't see a need to store the value in upper-case. Are you attempting to export this to a file? If so, you can create a query that converts the Addr1 with a column like:

LABEL1: UCase(Addr1)

If you export the query, you will get uppercase Addr1.

If you can't figure this out, please provide more context to what you are doing.

Duane
Hook'D on Access
MS Access MVP

 

[Ankroson]

Hi Duane: Thanks again for your input.

OK: let's start this again.

I don't want to convert to a report, a query, or other. I would like a stored uppercase copy of the [Addr1] as another column in the SAME TABLE as is the column [Addr1], but named [LABEL_1].

Clean and simple, and this is exactly what we need:

2 columns:
a) One in mixed case, as it is input on the form we use, which is [Addr1].
b) The second column being a column which takes the (manual) input from [Addr1], converts it automatically to Upper case, and permanently stores it as printable uppercase in column [LABELS_1).

I have attempted to do this in many ways, and have come back with err codes as were illustrated in previous replies.

TRYING TO AVOID COMPLICATION AND REDUNDANCY IN EFFORT: The simplest way to get what I want is to export to Excel, run an Upper(XXX) into the second column and then either re-import it or run an update query on it. I have done that dozens of times. I'm trying to avoid doing that again and again, as mail runs occur often.

Is this 'automation' within the table possible, or is it something just that cannot be done within the coding parameters of Access?

If it can be done, what is the process for doing so, simply, within the same table. Thanks again.

 

[MajP]

Assuming the value has to be stored.
Is there a reason you can not simply do the following query?

UPDATE Table1 SET Table1.Label_1 = UCase([addr1]);

 

[Ankroson]

Where/how would I build this query into the table itself so that it automatically updates the [Label_1] record when the input is done on the [Addr1] field?

ie: Input to form in textbox 'addr1': automatically updates [Label_1]

??

Thanks, man.

 

[MajP]

Where/how would I build this query into the table itself so that it automatically updates the [Label_1] record when the input is done on the [Addr1] field?

This is one of the reasons that everyone is suggesting that you do not store calculated value. If you "hard wire" and store the value, then you are uncertain if the stored field is up to date based on changes to the input values. If you do your calculations on the fly then you always have the most up to date information.
Imagine using a spread sheet that adds columns 1 and column two and the value is stored in column 3. Instead of a formula in column 3 you hardwire the answer. Then everytime there is an update in column 1 or 2 you have to ensure column 3 is updated through code. If your code is not correct or all events accounted for then it is very possible to update col 1 or col 2 and not fire the event to update 3.

Prior to Access 2010 you did not have data events. So the only way to ensure an update was done was at the form level. But the input data could be altered by a query, code, or table.

However sometimes there are good reasons to store calculated values. I then tend to just do a batch update prior to using the calculated field. If I was doing this, I would just run the update query on all the records just prior to making labels. That way I am ensuring prior to use that I have up to date values. This is inefficient, but it ensures updates are current.

If you want to do it at the form level then you can simply do it in code. I would but it in the after update event of the addr1 control and the on current event of the form. Simply some code like

public sub UpdateLabel1()
me.label_1 = ucase(NZ(me.addr1,""))
end sub

I do not have Access 2010 so do not know exactly how to do it. But you can build a data macro at the table. So if addr1 updates it can update label_1. This makes storing calculated values less problematic.

 

[dhookom]

MajP has the possible solutions for implementing a requirement that we both believe shouldn't be a requirement. You have been presented with the generally accepted solution that attempts "TO AVOID COMPLICATION AND REDUNDANCY IN EFFORT".

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

Yes, I still strongly believe the OP should dynamically format the label, as previously stated.

There are reasons for storing calculated values.
1) The calculation is complex, and it would make a query fail to run, or run to painfully slow. Sometimes you can write code to return a value and store it in a table, that would be very difficult and slow to use a dynamic function.
2) Need it for export. Sometimes the calculated value (especially format) does not come across. But then update prior to export.

I do not think this case fits either the above, unless there is some exporting that was not made clear.

 

[Ankroson]

Then I bow deeply to your suggestions, and accept the simplest and most concrete route for data validfation:

I shall build an update query to obtain the data from the [addr1] field, and macro it to update the uppercase information in the Label_1 field as is needed.

I thank both of you heartily for your suggestions and the information, which I have found to be most valuable. It prevented me from over complicating, rather than simplifying the situation.