General discussion

Locked

Access 2000 - formatting number fields

By sej ·
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?

This conversation is currently closed to new comments.

13 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access 2000 - formatting number fields

by john_wills In reply to Access 2000 - formatting ...

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") & "/" & <whatever>
Or have I misunderstood what you want?

Collapse -

Access 2000 - formatting number fields

by sej In reply to Access 2000 - formatting ...

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")/ <whatever> . ??

Collapse -

Access 2000 - formatting number fields

by john_wills In reply to Access 2000 - formatting ...

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.

Collapse -

Access 2000 - formatting number fields

by sej In reply to Access 2000 - formatting ...

... 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

Collapse -

Access 2000 - formatting number fields

by john_wills In reply to Access 2000 - formatting ...

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

Collapse -

Access 2000 - formatting number fields

by sej In reply to Access 2000 - formatting ...

Poster rated this answer

Collapse -

Access 2000 - formatting number fields

by kees.valkenswaard In reply to Access 2000 - formatting ...

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 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.

Collapse -

Access 2000 - formatting number fields

by kees.valkenswaard In reply to Access 2000 - formatting ...

You can either use now() or date()
It is possible that one of these would not always be available. Check yor system(s)

Collapse -

Access 2000 - formatting number fields

by kees.valkenswaard In reply to Access 2000 - formatting ...

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

Collapse -

Access 2000 - formatting number fields

by sej In reply to Access 2000 - formatting ...

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.

Back to Web Development Forum
13 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums