I have filed that has been uploaded from excel file in this format 20110307 , but I need this filed named postdate in date format such as 03/07/2011 . How to transfer text filed into date .I use Format([PostDate],'mm/dd/yy') in update query , but the data completely disappeared off the field.
View RepliesI'm building a report that requires me to concatenate several fields plus additional words, etc. But not all of the fields are the same data type. I have the date formatted the way I want it in a date/time field in one table (dd mmmm yyyy), and I want to append that date into a text field in another table, maintaining the same format.
Now, when I do a normal append or update query, it appends as medium date format (dd-mmm-yy). If I change the field type in the original table from date/time to text, it also shows up in medium date format.
Any ideas on how to make this work, or other options for concatenating fields with different data types?
edit: I don't want to change the data type of the original field to text.
I have a simple query and I want to export it in excel, Comma Separated Value., .CSV
I tried to run DoCmd.TransferText but all of my attempts were futile.
I found it, I must export it as TXT and file suffix insert .csv instead of .txt and it works
Now can I execute this "Saved Exports" through a macro or docmd ?
I insert a picture just to make it more clear.
[URL]] ......
Well I have been searching but have not exactly found the answer I need so:
I have a linked table in my database. One of the fields on the source table is a date field but the data type is Text. So there is data in the field that represents dates but they are formatted as text like this:
090506
101106
120506
I need the field data to behave as a date, any ideas? I can't change the source data type.
Then...at some point I want to produce a query that shows all records with a date earlier than the current days date...in pointers in the right direction on that will be appreciated also.
Thanks!
I'm creating a training database. Individuals carry out their training and I enter their training regime in three fields - year 1, year 2 and year 3. For example, their year 1 training is 1 august 11, year 2 is 1 august 12 and year 3 is 1 august 13. I can then run reports on who is due over the next 3 years and when.
However, I'd like to archive their past training dates. How to do this.
Say individual had following dates in their record as due dates:
Year 1 - 1 august 11
Year 2 - 1 august 12
Year 3 - 1 august 13
They carry out their year 1 training so that field would now have to change to 1 august 2014 (this training just runs and runs). Is there a way to add a command button that when clicked archives the 1 august 11 date already there to year 1 archive field and clears the year 1 field so that new date can be added. I've tried below but probably not right.
Code:
Me.txtboxYear1field = Me.txtboxyear1archivefield
textboxYear1field.Value = ""
I wanted to assign the field "Number of magazine" with special format based on date/time format but showing only year and month in the format: "yyyy-mm".
So in property of this field in format I put yyyy-mm and in input mask I type 0000-00;;-
I also created the form based on the table containing above field and I defined format and input mask for corresponding formant in the same way like at the table.
But if I try to type date for example 2014-01 in text box of the form it comes up with the full date 2014-01-01. Why does it do like this? What do I do incorrectly?
I have a date that imports as text into my table.
It imports as 20041201 which is yyyymmdd.
I would like to convert this text to a actual date format mm/dd/yyyy.
Is this possiable?
Any help would be so great.
Becky
Hi
I have 2003 Access Dbase from which some of the fields must be exported as comma deliminated txt and email to a repository.
I need the end user to see DOB, date format dd/mm/yyyy (15/06/1959) but it must be exported as ,15061959,Town,State....etc
Now I have formatted Table, Form & Query Fields (dd/mm/yyyy) and then carefully chosen the text export options...Removing the / date deliminator etc and saved the export format & kind. Included the correct path etc in to a macro and used Notepad.exe to display.
When it displays... as follows...15061959 0:00:00, IT ADDS A TIME?????
Short term workaround I have end user entering 2x IE 01/01/0001 & 01010001 as seperate text field. If I format date as ddmmyyyy then it displays this way for end user of course and not acceptable for other reports
Is there some way I get around this...or code/query/function I can use to convert for export purposes??????
I am hoping someone can help me, a real novice at Access 2000 - I am trying to construct a couple fields in a table, 1 of which will show a default value equal to a 2-digit year (yy) based on the current date. The second will display a default value equal to a two digit month (mm) based on the current date. Text fields would be ideal, but date field could work if it's the only way. Thank you for helping out a newbie.
View 4 Replies View Relatedhow can I combine two text fields and format them as a date.
Text Field 1: Jan
Text Field 2: 2014
I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).
I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.
Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.
How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?
Hi All,
I have a field named "PICK_DATE" in text format where pickup date is stored in DD/MM/YYYY format.
Now I wanted to generate a report with the order by clause of PICK_DATE. But it is not giving me the desired result. I want to see the latest PICK_DATE at the top of the report in desending order.
Can any one pls help me out to solve this problem ? :confused:
Thanks in advance,
Jignesh
I have a linked database that stores date values as YYMM in text format. I have no control over how that information is kept. I cant seem to find a way to convert YYMM text to date format. Additionally I would like to add the last day of the respective month to that data.
View 6 Replies View RelatedI am building a database using data imported from Excel workbooks. The dates in the workbooks are formatted as text in the YYYYMMDD format. Is there a way to convert this into date format during the import or after? I am pulling in a lot of different workbooks and trying to avoid having to reformat each individual workbook prior to importing them.
View 3 Replies View RelatedSomeone decided to be clever and send a text file with a stupid date format, is it possible to show off a little here and get a working date format, it's a text file I am working with: Dec 18 2014 01:12PM.
Not that is an exactly cut/paste so spaces are as they are here, ideally would like to get 18/12/14 13:12.
I am connecting acces to oracle servers via ODBC .
In the table there is a date time stamp - format data type text:
20130225060621
I would like to convert so that it is recognised as a date so I can calculate against e.g. Now() or another standard date format.
this is what I am currently using (which is OK) BUT I need to calculate against the time also.
Current Check Point Date: CDate(Mid([DZ_ZPKT_AK],7,2) & "/" & Mid([DZ_ZPKT_AK],5,2) & "/" & Mid([DZ_ZPKT_AK],1,4))
I spoke to an IT guy who works in TOAD program and he gave me this SQL:
select to_date(dz_zpkt_ak,'DD/MM/YYYY HH24:MI:SS')from orders where ordnr='4411310';
this is the format I would like but cant make this work.
I have a downtime system that was created by our IT department that records the downtime of the line stops, it has export to excel button that will export the downtime to excel based off the parameters of date and time. I want to do the export then import to access for downtime analysis. The problem I am having is the programmers export it as HTML with .xls file format. and the worksheet has a date associated with it as well as the spreadsheet has a tab that contains a date and I can't figure away to import to a fixed table. So I tried the transfer spreadsheet not knowing it was in HTML, couldn't recognize the format, then I tried transfer HTML, can't find the file because of .xls file format. I know I could re-save it as a excel file but was trying to keep the steps to a min. Is there a way to import even though it has a date attached to the file and spreadsheet tab.
View 7 Replies View RelatedI have a problem converting text to a real date value so I can do some calculations. I have a query that brings in data from an external data source. It appears the data is stored in the external table in text format and looks like this:
20050902 15:40:41
I have tried CDate to convert the text to a date/time format, but no luck. Any ideas?
I have a link table that has a field that represents dates - but they are actually just text. It's a long story but the source is not going to change - so I have to try and deal with it. I need it to behave as a date - and am hoping to do this in a query. The data looks like this:
7/24/12
10/08/13
I have tried various things but it does not seem to totally do the trick - if I sort on it it still does not sort as one would expect from an actual date field. How do I do this?
we would like to enter text that is always in this format,2013 04 11 / 09:15...I have tried using the field as date, with the formatting of yyyy mm dd / hh:mm...but the records keep showing a data entry problems with this format.
View 3 Replies View RelatedI have a continuous form (2003) with 6 text boxes (StartTime, StopTime, Comments etc...). I would like all the text boxes to have a gray background if the StartTime for that row is less than Today().
View 5 Replies View RelatedI have a field in a query that contains numbers and text (text field). The numbers displayed come from a percent calculation and display with many decimals ie, .99898745987245. Is there a way to eliminate the decimals with code in the query field? For example .99898745987245 to equal 99%? I can’t format the field as a number or percent because it has both text and numbers. HELP!!
Thanks
I saw a thread once that explained how to format a date or phone number field after you've entered the data, but I can't find it again.You do it so that you don't have the __/__/__ or the ___-___-____ pop up in the field and don't have to worry about inadvertently starting typing the data in the middle of the field instead of the beginning.Anybody know where that thread is at, or know how to do that?
View 2 Replies View RelatedI have two tables that I import that have both a date field and a time field formated as text. date field looks like yyyymmdd and time looks like hhmmss. I am using two append queries that combine the data into one table and I need the date and time fields to be date and time format respectively. When I run the query as is I get no values in either the date or the time field due to a data tyoe mismatch. My question is how do I convert the input table date and time text type fields to date and tie format for the append query.
Thanks for the help
Tony
I have a CSV file and want to convert it in a text format with some filtered data and with some formatting. This is an everyday task for me. So I made a table and imported the data in to it by the command :
DoCmd.TransferText acImportDelim, "fo Import Specification", "fo", FileName:="C:UserswelcomeDesktopfo.csv", HasFieldNames:=True
Actually I have a column "SERIES", contains various series like "EQ", "BE", "DR", "BZ", "D1" and so on. And one more column with the dates having 4 / 5 current months dates and one next months date and one next to next month's date. And every date has got several thousand records.
now the issue is that : After importing these several thousand records, I want to export it but with a specific date and with a specific series.
The other thing is that, these dates change every month so if hard coded, the problem will occur the next month.
I use this code for export :
DoCmd.TransferText acExportDelim, "NewFnoSpec", "fnoquery", "C:UserswelcomeDesktopFO Output.txt", True
this code is working fine but when the month will change, the code won't work.
Can we have a date & series picker attached to this query, so it can export the records with the specified SERIES & DATE.
I tried putting a textbox on the form named TxtDate and in a Query ( Design mode ) under the date column, in criteria I have put [Forms]![Futures]![TxtDate] and after putting this line, the query becomes empty and no data is there.
I have
Table called "Products"
Field 1= "Product ID" which is a text field (PK) but numbers are used (ie 1 -20)
Field 2= Products -showing our list of 20 products
When I enter a new product, currently I have to look in the table to find the last ID used then use the next one available. I have created a form to be used for data entry to enter new products
What I am trying to do is :- 1, have the form open at data entry level but still able to scroll and see all records and 2, Have the form auto generate the next number available. For example, I have 20 products entered so when the form opens to enter a new product, the ID is automatically at number 21. I know absol nothing about coding. I have looked at other suggestions on the forum incl the DMAX +1 and having tried and not worked, I wondered if its because the field is a text field or I am trying to insert the code in the wrong place.