Autonumber & Text

[Guest]

Hi,
I am trying to create a table that has a Reference field set to include the
letters 'JNL' as a prefix and then an autonumber. This field will be used
just as a reference and not as a counter or anything so I am not worried
about losing numbers in the sequence.

How can I achieve this?

 

[Guest]

Just add the prefix in your queries like --
Reference: "JNL"& [YourAutonumberField]

 

[Guest]

But it is not the query that I am using. I want to store the reference in
the table.
--
Kevin

Just add the prefix in your queries like --
Reference: "JNL"& [YourAutonumberField]

Hi,
I am trying to create a table that has a Reference field set to include the
letters 'JNL' as a prefix and then an autonumber. This field will be used
just as a reference and not as a counter or anything so I am not worried
about losing numbers in the sequence.

How can I achieve this?

 

[Lynn Trapp]

What purpose is this prefix going to serve?

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html

But it is not the query that I am using. I want to store the reference in
the table.
--
Kevin

Just add the prefix in your queries like --
Reference: "JNL"& [YourAutonumberField]

Hi,
I am trying to create a table that has a Reference field set to include
the
letters 'JNL' as a prefix and then an autonumber. This field will be
used
just as a reference and not as a counter or anything so I am not
worried
about losing numbers in the sequence.

How can I achieve this?

 

[Rick Brandt]

But it is not the query that I am using. I want to store the
reference in the table.

If the prefix is always the same there is no point in storing it in the
table.

 

[Guest]

Check out www.candice-tripp.com for autonumber code. You can use that and
then append your "JNL" to the front. I have done similar but I first run a
query to strip off the number portion of the field and then to determine the
Max value. My code looks similar to this on the button to create a new
record. I have added comments to help you understand:

DoCmd.GoToRecord , , acNewRec
DoCmd.SetWarnings False
[ReferenceNumber]=0
/ where reference number is a hidden field on the form
DoCmd.OpenQuery "qryLastReferenceNumber"
/ a make table query as described above
Me.[ReferenceNumber].DefaultValue = DLast("LastRef","tblLastRef")
/ where LastRef in tblLastRef is the result of the make table query
Me.[Reference]="JNL" & Right (([ReferenceNumber]+1000000),6)
Me.Refresh
DoCmd.SetWarnings True


Hope this helps.

But it is not the query that I am using. I want to store the reference in
the table.
--
Kevin

Just add the prefix in your queries like --
Reference: "JNL"& [YourAutonumberField]

Hi,
I am trying to create a table that has a Reference field set to include the
letters 'JNL' as a prefix and then an autonumber. This field will be used
just as a reference and not as a counter or anything so I am not worried
about losing numbers in the sequence.

How can I achieve this?

 

[Joseph Meehan]

Hi,
I am trying to create a table that has a Reference field set to
include the letters 'JNL' as a prefix and then an autonumber. This
field will be used just as a reference and not as a counter or
anything so I am not worried about losing numbers in the sequence.

How can I achieve this?

Are you really sure you want to use autonumber??

Autonumber is designed to provide unique numbers, it is NOT designed to
provide consecutive or contiguous numbers. Using autonumber anywhere that a
user will see them is almost certain to cause confusion and complaints on
down the road.

In addition, if all the records are JNL then don't store it, add it when
it is displayed. You can use a query or a formula to display it the way you
want. Access likes it better than way.

 

[Michael J. Strickland]

Hi,
I am trying to create a table that has a Reference field set to include
the
letters 'JNL' as a prefix and then an autonumber. This field will be used
just as a reference and not as a counter or anything so I am not worried
about losing numbers in the sequence.

How can I achieve this?

1. Create an autonumber field for your table (e.g. ID).

2. In Design View, for the autonumber field, in the format section put:
"JNL"0

or if you want leading zeros on the numbers, add more zeros:

"JNL"000

for 3 leading zeros.


--