By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,652 Members | 1,487 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,652 IT Pros & Developers. It's quick & easy.

excel formula to access formula

P: 2
Due to the size of the data I have to manipulate I need to apply a excel formula in access which determines date of manufacture from a serial number

716001 so I use =VALUE(DATE(VALUE(MID(A1,3,1))+2000,1,1))+VALUE(MI D(A1,4,3))-1 which gives 1/1/2006

How can I get this formula to work in access I know that the field name replaces the cell reference. I am just finding my feet in access and struggling to get this to work. I donít really want to split the 191K of lines down in to excel manageable pieces.

Thanks for any help
Sep 4 '08 #1
Share this Question
Share on Google+
2 Replies


Expert Mod 2.5K+
P: 2,545
Hi. The Access equivalent is very near to the Excel version:
Expand|Select|Wrap|Line Numbers
  1. DateSerial(Mid([SerialNoField], 3, 1)+ 2000, 1, 1) + Val(Mid([SerialNoField], 4, 3]) - 1
Please note that the year read from the serial number as a single digit added to a base of year 2000 will cause a failure with dates from 2010 onwards, as the 0 single digit would result in a date of 2000 being recorded. As it is now September 2008 the failure will occur for the interpretation of any new serial numbers generated following this coding pattern in just over one year's time.

-Stewart
Sep 4 '08 #2

P: 2
Thanks for the help I will try it out. I am sure our S/N structure will change as the extra characters will have a knock on effect on the other parts of the S/N.

thanks again
Sep 4 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.