Access Text to number conversion - Help!!!!

[Tristan Williams]

Hi All,
I am really hoping that someone can help me. I am using Access for the basis of my database and I want it to do a calculation but being a novice at this, I am having a complete nightmare. I am recording part codes and batch codes of my products and what I would like to do is have Access automatically calculate the date from the batch code. The batch code works as follows:

EG: Standard Batch code is BG, where B = Feb and G = 2003

The entire system works as follows:

Months (1st letter)

A = Jan
B = Feb
C = Feb
D = Apr
E = May
F = Jun
G = Jul
H = Aug
I = Sep
J = Oct
K = Nov
L = Dec

Years (2nd letter)

Z = 1985
Y = 1986
X = 1987
W = 1988
V = 1989
U = 1990
T = 1991
S = 1992
R = 1993
Q = 1994
P = 1995
O = 1996
N = 1997
M = 1998
L = 1999
K = 2000
I = 2001
H = 2002
G = 2003
F = 2004
E = 2005
D = 2006
C = 2007
B = 2008

All help would be really greatly recieved. If it isn't possible to do this in access, is it possible to do this in Excel?

Hopefully yours



Tristan Williams

 

[nullZero]

One option is to store your Month and Year codes in tables so that you can use the dlookup function to assign batch codes.

dlookup("Month_ID", "tblBatchMonths","Month_Num = " month(date())) & dlookup("Year_ID", "tblBatchYears","year = " year(date()))


hth,
nZ

 

[Norie]

Tristan

Are you trying to work out this code from an existing date field?

If so you could use an expression like this.

BatchNo:Chr(64+Month([YourDate])) & Chr(2075-Year([YourDate]))

 

[Tristan Williams]

No, there is no current date field in the table, only a column for entering the batch codes (this allows us to idividually identify some of the products but is primarily to age them). All I want to do is tell the computer to say:

If the code BG is entered in the Batch Code column, then take the first letter B to mean Feb and the G to mean 2003 then display Feb 2003 in a seperate column

This would need to continue through the codes mentioned in my first post.

Only problem is that I don't know how!!!!

Appreciate all the help to date and all further help.

Sorry to be a pain in the ****, but it is all appreciated.

Also How do I apply the codes to the data, I am a real beginner but would really like to learn.


Please have patience with me.



Cheers



Tristan.

 

[nullZero]

Tristan

Are you trying to work out this code from an existing date field?

If so you could use an expression like this.

BatchNo:Chr(64+Month([YourDate])) & Chr(2075-Year([YourDate]))

That's really cool! Your year is off by one letter though... I ran it for today and it gave me BE instead of BD

 

[nullZero]

No, there is no current date field in the table, only a column for entering the batch codes (this allows us to idividually identify some of the products but is primarily to age them). All I want to do is tell the computer to say:

If the code BG is entered in the Batch Code column, then take the first letter B to mean Feb and the G to mean 2003 then display Feb 2003 in a seperate column

This would need to continue through the codes mentioned in my first post.

Only problem is that I don't know how!!!!

Appreciate all the help to date and all further help.

Sorry to be a pain in the ****, but it is all appreciated.

Also How do I apply the codes to the data, I am a real beginner but would really like to learn.


Please have patience with me.



Cheers



Tristan.

You can reverse Norie's code to the code below if you want to convert from batch number to a date:

BatchDate: CVDate(Asc(Left([batchno],1))-64 & "-" & 2074-Asc(Right([batchno],1)))

You just need to place it in the query editor and replace batchno with your field name.

hth,
nZ

 

[Norie]

NullZero

If you look at the OP's data you'll notice J is missing from the list for the years.

I only noticed it after posting.

I'm not sure if it's a typo or is intentional.