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

Convert date to number

P: 3
Hi

Can anyone help me how to convert date to number.

If date is 04-15-2019, I want to store it as 20190415 in number format.

I have used =Format ([DateField],"yyyymmdd") and tried to store if as string but even this doesn't seem to work.

Any help is appreciated.
Dec 9 '19 #1

✓ answered by ADezii

To the best of my knowledge, you cannot Set the Default Value of a Field in a Table to a Formatted Expression involving another Field in the same Table. That being said, and as stated by cactusdata, you can generate this Number within a Query, an example of which would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID1, Table1.DateField, Table1.SerialNum, Table1.CustNo, 
  2. Format$([DateField],"yyyymmdd") & Format([SerialNum],"00") AS DateConv
  3. FROM Table1;
Expand|Select|Wrap|Line Numbers
  1. ID1    DateField    SerialNum    CustNo    DateConv
  2. 1      12/10/2019       2        12345     2019121002
  3.  

Share this Question
Share on Google+
8 Replies


ADezii
Expert 5K+
P: 8,675
You can coerce the Formatted Expression into a LONG, as in:
Expand|Select|Wrap|Line Numbers
  1. Dim strTheDate As String
  2. Dim lngTheDate As Long
  3.  
  4. strTheDate = "4-15-2019"
  5.  
  6. lngTheDate = CLng(Format("4-15-2019", "yyyymmdd"))
  7.  
Dec 9 '19 #2

P: 3
I have tried using =CLng(Format[DateField], "yyyymmdd")) in default value of table properties. However error shows up. The error message Is-The database engine does not recognize either the field 'DateField' in a validation expression, or the default value in table 'Table1'.

Can you give me any advice.
Dec 9 '19 #3

cactusdata
P: 79
Expand|Select|Wrap|Line Numbers
  1. Format([DateField],"yyyymmdd")
will work, even for Null values, so if "it doesn't work" (whatever that means), something else is going on.

To convert to a number, use Val:

Expand|Select|Wrap|Line Numbers
  1. Val(Format([DateField],"yyyymmdd"))
That said, you should always store dates as DateTime and, when retrieved and for display, format for the current purpose.
Dec 9 '19 #4

NeoPa
Expert Mod 15k+
P: 31,662
CactusData is spot-on. If you have the problems as described then they are not connected with your question. It's hard to help with details when you don't share the details. Val() or CLng() should both work fine for what you're describing. What else you're doing wrong is outside of the scope of this discussion - especially as you don't share what else you're doing.
Dec 10 '19 #5

P: 3
I wish to create a database to enter transactions at my shop.
Each customer is assigned a unique serial number for a transaction. Uptill now I used manual register for entries. Everyday serial number starts from one. To give each transaction a unique identification number I thought of combining date with serial number.

Example a customer assigned serial number 02 today would be given a unique serial number 2019121002. This could be tracked even a year later.
So i wanted to convert date to number and add it to serial number.

However the database engine can't recognise the field. I have attached the table structure and error msg pics.

Thanks.
Attached Images
File Type: jpg Screen pic 1.jpg (213.9 KB, 20 views)
File Type: jpg Screen 2.jpg (187.4 KB, 20 views)
Dec 10 '19 #6

cactusdata
P: 79
Don't ever use calculated fields. It's a dead end.

Write a query for this.
Dec 10 '19 #7

ADezii
Expert 5K+
P: 8,675
To the best of my knowledge, you cannot Set the Default Value of a Field in a Table to a Formatted Expression involving another Field in the same Table. That being said, and as stated by cactusdata, you can generate this Number within a Query, an example of which would be:
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.ID1, Table1.DateField, Table1.SerialNum, Table1.CustNo, 
  2. Format$([DateField],"yyyymmdd") & Format([SerialNum],"00") AS DateConv
  3. FROM Table1;
Expand|Select|Wrap|Line Numbers
  1. ID1    DateField    SerialNum    CustNo    DateConv
  2. 1      12/10/2019       2        12345     2019121002
  3.  
Dec 10 '19 #8

twinnyfo
Expert Mod 2.5K+
P: 3,385
OR..... Instead of insisting that the field is a numerical value, you could have it as short text of 13 characters. Then your serial number number could be: "2019-12-10-02".

Conversion back to dates is simple:
Expand|Select|Wrap|Line Numbers
  1. dtOrderDate = cDate(Left([SerialNumber], 10))
It just depends upon what info you need to track in this Serial Number.
Dec 10 '19 #9

Post your reply

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