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

Copy date part as text

P: 7

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:
Feb 13 '08 #1
Share this Question
Share on Google+
4 Replies

Expert Mod 2.5K+
P: 2,545
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.

Feb 13 '08 #2

Expert 100+
P: 108
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.
Expand|Select|Wrap|Line Numbers
  1. =Format([Text0],"mm")
Hope this helps.

- Minion -
Feb 13 '08 #3

P: 7
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 ?
Feb 21 '08 #4

Expert Mod 2.5K+
P: 2,545
...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
Feb 21 '08 #5

Post your reply

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