Invoice Number

[pushrodengine via AccessMonster.com]

I need an Invoice Number text box in the format of "0000-yy" where the “0000â€
increases +1 automatically.

Thanks

 

[Ken Snell \(MVP\)]

Do not store the "0000-yy" value in a single field. Instead, use two fields
(use Integer data type if numbers will not exceed 9999 in your example) --
one for the "0000" part, and one for the "yy" part. Then concatenate the two
fields with a hyphen when you want to display the result:

SELECT Field0000 & "-" & Fieldyy AS InvoiceNumber,
FieldName1, FieldName2, FieldName3
FROM TableName;

Then you can use standard incrementing expressions to get the next value of
"0000" field for a given "yy" value:
Nz(DMax("Field0000", "TableName", "Fieldyy= & DesiredYYvalue),0) + 1

 

[pushrodengine via AccessMonster.com]

I'm using the code:

Default Value
="0000" & "-" & Right([Date],2)

Currently, the user must manually enter the correct value for “0000â€.

Is there a way to modify this code to automatically increase “0000†+1?

 

[Douglas J. Steele]

Ken already gave you the code:

<quote>
Then you can use standard incrementing expressions to get the next value of
"0000" field for a given "yy" value:
Nz(DMax("Field0000", "TableName", "Fieldyy=" & DesiredYYvalue),0) +
1
</quote>

 

[pushrodengine via AccessMonster.com]

Is this the only method?

 

[pushrodengine via AccessMonster.com]

I have a textbox on the form that displays the last invoice number used named
“LastInvoice†and it has a control source of:

=DMax("[InvoiceNumber]","tblLog")

The "InoviceNumber" is in the format "0000-yy"

Is there a way to code the Default Value of the textbox “InvoiceID†so that
it's the “LastInvoice†textbox value, but increases it’s “0000†value +1.

So now the Default Value of textbox “InvoiceID†is the “LastInvoice†textbox
value “0000†+1 & “-“ & Right([Date],2)

Thanks

 

[Douglas J. Steele]

You could use a recordset rather than DMax, but there's no need to do that.

The important thing, though, is that you follow Ken's advice and split your
existing field into two separate fields, and concatenate the fields into a
single value for display purposes in a query.

Have two separate pieces of information in a single field, such as you
currently have, is actually a violation of database normalization
principles. You've already seen one of the effects of doing that: it's
difficult to work with the data!

BTW, simply reposting your question again in the same thread is NOT going to
get you a different answer. If you need more information about the answer,
or realized that you didn't give us all the details originally, indicate
what's different the second time: don't just repost the same question!

 

[pushrodengine via AccessMonster.com]

I have over 1,000 records so far in the table in the format of "0000-yy".

How exactly do I split the field without losing data or re-entering data?

 

[Douglas J. Steele]

Add two new fields to the table.

Create an update query that updates those two new fields. Let's assume that
the new fields are named NewID and IDYear, and that the old field was named
OldID. Your SQL would be something like:

UPDATE MyTable
SET NewId = Left([OldID], InStr([OldID], "-") - 1),
IDYear = Mid([OldID], InStr([OldID], "-") + 1)

 

[pushrodengine via AccessMonster.com]

Where do I enter:

SELECT Field0000 & "-" & Fieldyy AS InvoiceNumber,
FieldName1, FieldName2, FieldName3
FROM TableName;

Thanks

 

[Douglas J. Steele]

That's the SQL of a query. You create the query the way you create any other
queries, and then use that query instead of the table.

 

[pushrodengine via AccessMonster.com]

Thank You.