Date conversion to number

[Imstac73]

Hi, I have an unbound date field in the header of a continuous form where users can use the date picker to select a date. This field is set to the default value of another bound field that is a number field in the details section. I am trying to get the number field to format the date information as yyyymmdd.

I currently have the following code in my default value of my bound field. The only problem with this is it cuts off the beginning zero for one digit months and days.

=Year([Forms]![Timecard Entry]![WEDate]) & Month([Forms]![Timecard Entry]![WEDate]) & Day([Forms]![Timecard Entry]![WEDate])

Does anyone know a way to get it formatted to include the beginning zero?

 

[BSman]

Since your date format starts with the year, the trick is to treat everything as a string initially. Add 100 to the month, convert it to a string, get the right two characters, and append it to the year. Do the same thing with the day. Once you've built the string YYYYMMDD, you can convert that to a number for your storage.

But why convert to a number? You can't do any useful arithmetic on the values (for example, the number of days between two dates, or adding a month to a date) without converting back to a date. Or could you store the date as a Julian date, which is a number that is the year and the day of the year (1 to 366)? That could still be treated as a date and conversion should be easier that what I described above.

Bob

 

[dRahme]

Hello,

=Format([Forms]![Timecard Entry]![WEDate],"yyyymmdd") ?

 

[Imstac73]

The table I am updating is being uploaded into our Accounting system; we don't have the option to change the field type for the date.

 

[MajP]

You probably do not need to wrap dRahme's answer since textboxs accepts a variant, but to ensure you can use this everywhere you may want to convert to long:

clng(format([Forms]![Timecard Entry]![WEDate],"yyyymmdd"))

 

[Imstac73]

Thanks BSman, that worked!