Add a hyphen in between a long number

[Guest]

Hello all:

Quick question - I am importing data from an Excel file and one of the
columns is a list of Member IDs consisting of 19 characters. For visible
ease, our vendor has asked us to hyphenate the number after the first 12
characters. In Excel, the left(column name,12) "-" right(column name, 7) will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to run a
query? Do I need to write something in the SQL view to have it display this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria

 

[Douglas J. Steele]

Left([FieldName], 12) & "-" & Right([FieldName], 7)

or

Left([FieldName], 12) & "-" & Mid([FieldName], 13)

 

[BruceM]

It is pretty much the same idea in Access, with some syntax differences.
You could place this in the Control Source of an unbound text box:

=Left([FieldName],12) & "-" & Right([FieldName],7)

One way to do this is to add a text box, click into it, and type the above
(substituting your actual field name). You can also use the text box's
Property Sheet (click the text box to select it, then click View >
Properties, and click the Data tab.

The same expression could be used in a query. Base the form on a query, and
in a blank column in query design view put:

Hypnenated: Left([FieldName],12) & "-" & Right([FieldName],7)

Substitute any name you choose for "Hyphenated" (a name with no spaces would
be best). You can click on View > SQL view to see how this would look in
SQL, or you can write it directly into the SQL if you are familiar with the
syntax.

There's not much difference between the two approaches, as far as I know.
Since most of my forms and reprots are based on queries rather than on
tables, I tend to use queries for expressions of this sort.

You should not store the hyphenated version if the data already exist as
un-hyphenated numbers.

 

[Guest]

Thank you Douglas - I tried to put this in the text box of the report
(Control Source) and it came back with a circular reference error - then I
added it to the format box and the data completely disappeared - should I be
putting this formula in the query that outputs to the report, or somewhere
else I haven't mentioned?

Left([FieldName], 12) & "-" & Right([FieldName], 7)

or

Left([FieldName], 12) & "-" & Mid([FieldName], 13)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello all:

Quick question - I am importing data from an Excel file and one of the
columns is a list of Member IDs consisting of 19 characters. For visible
ease, our vendor has asked us to hyphenate the number after the first 12
characters. In Excel, the left(column name,12) "-" right(column name, 7)
will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to run a
query? Do I need to write something in the SQL view to have it display
this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria

 

[Guest]

You need to rename your textbox so it is not the same as any [FieldName] you
use in the control source.

Dave

Thank you Douglas - I tried to put this in the text box of the report
(Control Source) and it came back with a circular reference error - then I
added it to the format box and the data completely disappeared - should I be
putting this formula in the query that outputs to the report, or somewhere
else I haven't mentioned?

Left([FieldName], 12) & "-" & Right([FieldName], 7)

or

Left([FieldName], 12) & "-" & Mid([FieldName], 13)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello all:

Quick question - I am importing data from an Excel file and one of the
columns is a list of Member IDs consisting of 19 characters. For visible
ease, our vendor has asked us to hyphenate the number after the first 12
characters. In Excel, the left(column name,12) "-" right(column name, 7)
will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to run a
query? Do I need to write something in the SQL view to have it display
this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria

 

[BruceM]

I believe the expression needs to be preceded by an = sign if it is used as
the Control Source for the text box. See my previous post for details.

Thank you Douglas - I tried to put this in the text box of the report
(Control Source) and it came back with a circular reference error - then I
added it to the format box and the data completely disappeared - should I
be
putting this formula in the query that outputs to the report, or somewhere
else I haven't mentioned?

Left([FieldName], 12) & "-" & Right([FieldName], 7)

or

Left([FieldName], 12) & "-" & Mid([FieldName], 13)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Hello all:

Quick question - I am importing data from an Excel file and one of the
columns is a list of Member IDs consisting of 19 characters. For
visible
ease, our vendor has asked us to hyphenate the number after the first
12
characters. In Excel, the left(column name,12) "-" right(column name,
7)
will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to run
a
query? Do I need to write something in the SQL view to have it display
this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria

 

[Guest]

Awesome Bruce - it worked!!!

Have a great week!!!

It is pretty much the same idea in Access, with some syntax differences.
You could place this in the Control Source of an unbound text box:

=Left([FieldName],12) & "-" & Right([FieldName],7)

One way to do this is to add a text box, click into it, and type the above
(substituting your actual field name). You can also use the text box's
Property Sheet (click the text box to select it, then click View >
Properties, and click the Data tab.

The same expression could be used in a query. Base the form on a query, and
in a blank column in query design view put:

Hypnenated: Left([FieldName],12) & "-" & Right([FieldName],7)

Substitute any name you choose for "Hyphenated" (a name with no spaces would
be best). You can click on View > SQL view to see how this would look in
SQL, or you can write it directly into the SQL if you are familiar with the
syntax.

There's not much difference between the two approaches, as far as I know.
Since most of my forms and reprots are based on queries rather than on
tables, I tend to use queries for expressions of this sort.

You should not store the hyphenated version if the data already exist as
un-hyphenated numbers.

Hello all:

Quick question - I am importing data from an Excel file and one of the
columns is a list of Member IDs consisting of 19 characters. For visible
ease, our vendor has asked us to hyphenate the number after the first 12
characters. In Excel, the left(column name,12) "-" right(column name, 7)
will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to run a
query? Do I need to write something in the SQL view to have it display
this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria

 

[Guest]

Hi again Bruce!!

I have an additional question and you were so helpful earlier, hopefully you
can assist on this one as well!!

I am importing a text file into Access, with one of the fields being a
date/time that needs to be formatted as follows: mm/dd/yyyy. Because i am
importing it as text, Access does n;t recognize it as a date and refuses to
display it properly (whether it is a query, report or table) - I try to
change the "format" in the properties section of all three areas - and to no
avail....can someone please help?

Much thanks, in advance!
Victoria

I believe the expression needs to be preceded by an = sign if it is used as
the Control Source for the text box. See my previous post for details.

Thank you Douglas - I tried to put this in the text box of the report
(Control Source) and it came back with a circular reference error - then I
added it to the format box and the data completely disappeared - should I
be
putting this formula in the query that outputs to the report, or somewhere
else I haven't mentioned?

Left([FieldName], 12) & "-" & Right([FieldName], 7)

or

Left([FieldName], 12) & "-" & Mid([FieldName], 13)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Hello all:

Quick question - I am importing data from an Excel file and one of the
columns is a list of Member IDs consisting of 19 characters. For
visible
ease, our vendor has asked us to hyphenate the number after the first
12
characters. In Excel, the left(column name,12) "-" right(column name,
7)
will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to run
a
query? Do I need to write something in the SQL view to have it display
this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria

 

[BruceM]

A big part of the reason I am able at times to provide useful information in
this forum is what I have learned from Douglas and others who are so
consistently generous with their time and expertise. All I did was to
provide some additional details.

Regarding the current question, how are you going about importing the text
data? In what format is the information in the text file? It may help if
you provide a sample line of data (just a few relevant fields) from the text
file. Why are you importing the date field as text? What happens if you
change the field's data type to Date/Time? If you click File > Get External
Data > Import the wizard will probably identify the field as date/time. As
you run through the steps in the wizard you can verify that information.

The Format property determines how the data will be displayed. You can
choose to display a date in any number of formats, but if you need to
display text as date you can use the CDate function before you can apply a
date format. It's probably best to have the data in the correct format to
start.

As you can see, the answer to a question is likely to depend in large part
on the information provided when asking the question. As you continue to
work with Access you will discover that there are often several ways of
accomplishing what you need.

Hi again Bruce!!

I have an additional question and you were so helpful earlier, hopefully
you
can assist on this one as well!!

I am importing a text file into Access, with one of the fields being a
date/time that needs to be formatted as follows: mm/dd/yyyy. Because i am
importing it as text, Access does n;t recognize it as a date and refuses
to
display it properly (whether it is a query, report or table) - I try to
change the "format" in the properties section of all three areas - and to
no
avail....can someone please help?

Much thanks, in advance!
Victoria

I believe the expression needs to be preceded by an = sign if it is used
as
the Control Source for the text box. See my previous post for details.

Thank you Douglas - I tried to put this in the text box of the report
(Control Source) and it came back with a circular reference error -
then I
added it to the format box and the data completely disappeared - should
I
be
putting this formula in the query that outputs to the report, or
somewhere
else I haven't mentioned?

:

Left([FieldName], 12) & "-" & Right([FieldName], 7)

or

Left([FieldName], 12) & "-" & Mid([FieldName], 13)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


message
Hello all:

Quick question - I am importing data from an Excel file and one of
the
columns is a list of Member IDs consisting of 19 characters. For
visible
ease, our vendor has asked us to hyphenate the number after the
first
12
characters. In Excel, the left(column name,12) "-" right(column
name,
7)
will
suffice, however, I can't figure out how to do it in Access.

Is it easier to create a text box in the final report? Do I need to
run
a
query? Do I need to write something in the SQL view to have it
display
this
wy? Can someon eplease HELP!!!!

Much thanks,
Victoria