Convert date to number

[lumiere]

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.

[ADezii]

You can coerce the Formatted Expression into a LONG, as in:

Dim strTheDate As String
Dim lngTheDate As Long
 
strTheDate = "4-15-2019"
 
lngTheDate = CLng(Format("4-15-2019", "yyyymmdd"))
 

[lumiere]

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.

[cactusdata]

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:

Val(Format([DateField],"yyyymmdd"))

That said, you should always store dates as DateTime and, when retrieved and for display, format for the current purpose.

[NeoPa]

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.

[lumiere]

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.

[cactusdata]

Don't ever use calculated fields. It's a dead end.

Write a query for this.

[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:

SELECT Table1.ID1, Table1.DateField, Table1.SerialNum, Table1.CustNo, 
Format$([DateField],"yyyymmdd") & Format([SerialNum],"00") AS DateConv
FROM Table1;

ID1    DateField    SerialNum    CustNo    DateConv
1      12/10/2019       2        12345     2019121002
 

[twinnyfo]

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:

dtOrderDate = cDate(Left([SerialNumber], 10))

It just depends upon what info you need to track in this Serial Number.