Discussion Locked

Access 2000 - formatting number fields

0 helpful
13 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

If I have a number field, which has: >"L02/"*0 as the format.
How do I make the beginning of the field say L02/ if the year is 2002 , but L03/ when the year changes, without having to change the format of the field. is it possible?

13 replies

by john_wills

When constructing the field, make the year part of it via format(CurrentYear,"yy"). You can fit this into a string concatenation:
"L" & format(CurrentYear,"yy") & "/" &
Or have I misunderstood what you want?

by sej

You have understood what i want but when I enter "L" & format(CurrentYear,"yy") & "/" & *0
into format property, the action of carriage returning changes the line automatically to read "L" & "format(CurrentYear,"yy")" & "/" & *0 which then appears as L format(CurrentYear,"yy")/ . ??

by john_wills

I'm still not 100% sure what you want, but perhaps you should be putting the concatenation I gae you last time in the Control source property, as an expression.

by sej

... then i get ?#name in field.
what i want is to enter a number into the field, and have it automatically prefixed with L02/ for this year and L03/ for next year etc. but also to fill in the preceeding 0s , for example if i enter 1 , the field then reads L02/000001

by john_wills

Use several controls, with VB to concatenate them at the appropriate time; for search use a single control for the whole reference.

by kees.valkenswaard

What is against a query with the following field?
[Suppose your number is in field part]

X: Format(Now();"yy") & "/" & Format([part];"000000")
Say you have 25 in part; X would give 02/000025
However, next year this would read as 03/000025. If you really want to have the text string added, make a new field, say Y. Use an update query where all empty Y would get the value Format(Now();"yy") & "/" & Format([part];"000000")
I have the feeling that something as this would be your solution. Although, I would only add to my table a field with the date [i.e. now()] in it and a string in the query to combine the year and the number, if that is what you want on paper. It is very simple to add a date field e.g. INPDAT with default value =now()
This is anyhow a wise decision as it will always show when the number was added to the database. The field INPDAT you can protect or hide in a form.
The query would contain:
Z: format([INPDAT];?yy?) & ?/? & format([part];?000000?)
This can also be a (calculated) field on your form or in your report.
It you require more or fewer digits amend the six zeros.
It may be possible that you have to replace ; [semi colon] with , [comma]
Good luck.

by kees.valkenswaard

Z: format([INPDAT]; "yy") & "/" & format([part];"000000")
I just noticed that the lack of standardization amoung charachter codes may lead to funny results.

by sej

have tried your answer with regard to X i.e. Format(Now();"yy") & "/" & Format([part];"000000") . Maybe i havent put it in the correct place because it doesn't seem to do anything? I have tried it 'On exit' and 'on lost focus' to no avail.

by kees.valkenswaard

Perhaps I was not clear or you do just not understand the point. I assumed that the year should be the year the data have been entered and not the current year. If that is not the case you would have to incorporate the year in the format. So "02/"000000 as notation in your table would be sufficient. Next year change the 02 into 03 [supposing you want 6 digits]
However, if you want to add the year in which the data have been added it is another story. That was the one I referred to.
Suppose you have a table INV15 with the field part and INPDAT. Part is number and INPDAT is date with default value =now() or =date()
Design a query based on table INV15. Use fields part and INPDAT. Add a third field as mentioned before:
Z: format([INPDAT]; "yy") & "/" & format([part];"000000")
[with the note about perhaps , as replacement for ;] [check other formulas]
Result e.g.
part INPDAT Z
12 21-5-2002 20:52:34 02/000012
345 21-5-2002 20:52:44 02/000345
1267 21-5-2002 20:52:46 02/001267
If necessary make a form based on this query PARTYEAR.
Protect the field INPDAT and Z. So, only numbers can be entered in part. The correct format you see in field Z.
Also reports can be based on the query PARTYEAR.
If it is still not clear mail me atkees.valkenswaard@planet.nl
Do not forget to put also Q&A in the subject line. Else the message will be deleted.
Very good luck.

This discussion is closed to new replies.