Copy date part as text

[Mclaren]

Hi

I have a main single record form that has a date field on it pupulated from primary table, i have subform (datasheet view) which is populated from the many table. the subform has a field that udates from the date field in the main form when a new record is added. A second field in the subform populates with the same date however it only shows the month datepart in the following format : mm
I now need the datepart "mm" to populate a third field but as a number. I have set the 3rd fields format as number but i only get zero as a result. I have tried copying the 2nd field into the third field but no luck

In a nut shell i need the fields to show :

a) 2008/02/15
b) 02
c) 02

I need the fields like this as i am creating an export recordset to be used in a 3rd party app that want info in a certain way. any help please:

[Stewart Ross]

You can use the Month function to extract the numeric month value from your original date field. Add a new column to the query on which you base your subform and insert a field like this: MonthNo: Month(DateField ) (change the name DateField to the real name of your date field).

This will not display the month as 02, just as 2, since leading zeros are not normally shown for numbers unless you apply formatting to them.

Stewart

[Minion]

Not sure why you need duplicate information, but that's your design. To get this to work however just enter the following code to the default value of the second field to the following, changing "Text0" to the name of the field that holds the value.
[code=vb]
=Format([Text0],"mm")
[/code]

Hope this helps.

- Minion -

[Mclaren]

the reson for the duplication is that certain fields are being exported in to a excel spread sheet which in turn is imported in a program called Pastel which requires info in a certain format for import. Pastel is a rcognised accounting platform by our auditors, our inhouse system is not.

When i try these 2 options will excel recognise the field as a date or as a number or text ?

[Stewart Ross]

...When i try these 2 options will excel recognise the field as a date or as a number or text ?

The Month() function returns a numeric value; the format function returns a string. Choose whichever is best for what you need to do. - Stewart