473,322 Members | 1,352 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Copy date part as text

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:
Feb 13 '08 #1
4 2146
Stewart Ross
2,545 Expert Mod 2GB
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
Feb 13 '08 #2
Minion
108 Expert 100+
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")
  2.  
Hope this helps.

- Minion -
Feb 13 '08 #3
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
Stewart Ross
2,545 Expert Mod 2GB
...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

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

Similar topics

3
by: Mike Dundee | last post by:
I am importing data into a new database (the database still has to be set up) and have a problem. The comma delimited text files I am importing have four fields containing date and date/times. ...
4
by: Bogosian | last post by:
I am working on an asp.net project.There is a subdir called OSearch,with a few aspx pages in it which as all other pages on the website use some controls.These controls are stored in a another...
5
by: DraguVaso | last post by:
Hi, I need a SECURE way to copy parts of a file. I'm having files which contains a whole bunch of records. In one 'fysical' file I'm having one or more logical files. What I need to do is to...
0
by: igendreau | last post by:
I have a database with a Header table. Each record in tblHeader has two One-to-Many Relationships: with tblLines and tblKeys. The HeaderID field ties tblHeader to the other two tables. The data...
6
by: Luvin lunch | last post by:
Hi, I'm new to access and am very wary of dates as I have limited experience in their manipulation and I know if they're not done properly things can turn ugly quickly. I would like to use a...
7
by: Ramon | last post by:
Hello, when I send an email using the mail() function, I get a Runtime Notice: date() : It is not safe to rely on the system's timezone settings. Please use the date.timezone setting, the TZ...
13
by: blangela | last post by:
I have decided (see earlier post) to paste my Word doc here so that it will be simpler for people to provide feedback (by directly inserting their comments in the post). I will post it in 3 parts...
4
by: Terry | last post by:
I have a TextBox with a date such as 15/01/2006 which I want to cast into a variable as a short date 15/01/06, also I need to cast a time such as 07:30 A.M. into a variable as a short time. What...
1
by: chris | last post by:
I'm not sure this is even feasible. What I want to do is create a command button. When this command button is pressed, it taes field values and puts them into text format. I think I know how to...
0
by: AlexUhde | last post by:
Hello! I'm relative new to asp.net and I'm trying to figure out how i can have a "auto fill" date in a textbox from a DetailsView in insertmode. I know that with <%# Now %in Text='' I can put...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.