Help!!!

[accessdummy]

How do I program the database to generate the data mentioned below.


current month/year display as
september 2003 0309
October 2003 0310
December 2004 0412
January 2005 0501

the 4 digits are part of the unique serial number i'm generating.

After generating these digits, how do I extract them into year n month to generate annually and monthly reports?

Thanks

 

[Shadez]

Let me get this right, do you want to know how to store it or how to show it?

am i right in assuming your date format is a follows

Month YYYY DDMM

If the above assumtions are correct, store the data as a date and use a custom date format to display it.

just set the control format to

mmmm yyyy ddyy

 

[accessdummy]

How to show it and how to store it?

I dont want users to meddle around with the fields. The values should be generated by the computer itself.

Example, if the system time is september 2003, upon opening the form, the unique id should show me ABC-0309-00x. x denotes the running number (autonumber).

Also, how do I split the values such that the first 2 digit denotes the year and the last 2 denotes the month?
I need them to generate monthly/annual report.

Say example, I wan to print all the records I have keyed in august 2003. I would use part of the unique ID to generate the report because of the 4 digits I have assigned. So how do I spilt the values?

 

[Shadez]

Hmmmmmmmm,


OK,

1) lets talk about the ID, what do the individual parts meen?

ie

ABC -What is this bit
0309 -Date YYMM
00x -00x does this meen (0012) or (012)


2) from what i can gather so far you will want to store the date part as a date the autonumber part as an auto number and the text part as a string.

These indiviual components can be murged to form your ID field, the date field can be used for date critial reports.

3) Murging fields on a form. There are many ways of doing this,

a) with sql
b) with VBA
c) in the controlsource of an unboud control

As for selecting part of the date you simply use the format command/property, with a custom date format. (i suggest you use help for more info on this)

so

0312

is

YYMM



Hope this helps



ShadeZ

 

[accessdummy]

ABC would refer to the company initial which is fixed anyway. Does not have to be stored in the field because I can just use labelling in my report.

0309 is of course yymm

00x is the running number.

Anyway, how do I merge the YYMM and the 00x together as one data to be stored in the unique id field such tt 00x can automatically x = x+1?

Let's say I successfully merge the YYMM with 00x.. an example 0309001
How do I extract the 1st 2 digits and the 3rd n 4th digit out from this number?

 

[Shadez]

OK, now i have all the info

You need to create a table with the following fields (this table may already exist just missing some or all of the fields)

1)company initals (your ABC field)
2)a date field, (lets call this TheDate)
3)a ID/ autonumber field, this must be an auto number, Note this is also your primary key for the table.

now this will create a table witch look like this

NAN,02/11/02,1
NAN,04/11/02,2
NAN,02/12/02,3
NAN,05/12/02,4
NAN,03/12/02,5

ok next you need to display the info in your format.

Create a form and set the control source to the table then add an unbound textbox to the form

next in the controlsource property enter the following

=[ABC] & "-" & Format([TheDate],"yymm") & "-" & Format([ID],"000")

then just run the form.

Note it is better to store the parts of your ID seperatly, but they can still be shown to the user murged.