Expr1: Changes [field Name]

[Rickinrsm]

I have the following placed in a query in the field line;

Expr1: UCase([OcDownloadRES].[StName])

It works perfect but it changes the field name to Expr1.

I have programs that look for specific field names but these fields are renamed in the query. What do I do?

Thanks much . . .

Rick ~ Access Newbie

 

[lupins46]

With this expression you are creating a new field so you have to give it a name. The name cannot be the same as an existing field. You will either have to modify your code or change the name of the field so that you can then rename it in the query to what the code expects.

 

[Rickinrsm]

How would I modify my code to accomplish the same Ucase without changing the field name? Can I change the name of the expression to the field name first?

Can a Macro accomplish this?

Can I place VBA code on an action button on a form to perform the Ucase on multiple fields?

What would be the easiest/best wat to accomplish this?

Thanks much . . .

Rick ~ Access Newbie

 

[lupins46]

I would have thought the simplest approach is to convert the values to uppercase as they are entered through your form.
In the afterupdate event for each relevant control put:

me.thiscontrolname = Ucase(me.thiscontrolname)

You can write an update query to convert any existing values to uppercase.

 

[dhookom]

I would leave the query and fields alone. Don't UCase() in the query. Provide the formatting in either the data entry form as suggested by lupins46 or just apply the format in the Format property of the text box or the control source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Rickinrsm]

Thanks for your suggestions.

There is never any user data entry.

I import thousands of records to a table. 280 plus fields in some of the imports. But only about 20 fields that I need to normalize.

Many of the text fields are upper and lower case. All of the date fields have a time stamp following the actual date;

00:00:00

I need to remove that also.

Some of the numerice fields are actually in text format within the imported data. I need to change these to either a Longint or currency with no cents.

Basically what I do is . . .

import data
normalize data
remove duplicates
Then run pushbutton programs on a form that perform calculations on the data, place the data in reports and then displayed the calculations on the last page of the report via variables.

I have this working very well in Paradox but I think it's time to migrate to Access 2003.

I know nothing about VBA!!!

Thanks . . .



Rick ~ Access Newbie

 

[dhookom]

If you want to display a value in all caps, you don't need any code. Just set properties of controls.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]