I'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 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.
I need help with custom text formating. Everything I read says that @ is "Required text character" and & is "Text character not required", but nothing gives an example of &. Could someone give me examples of the difference between @ and & ?
Not sure if this is a Tables or Forms problem, so please excuse me.
I am creating a database where users will be entering Survey data. The Survey dates are in the format: Month-Year (February 2005, December 2004) etc....
I would like to capture the dates in that format, but retain the functionality that comes with Date type fields. For example to query a date range.
How and where do I specify the Date Format I desire?
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.
I am stumped: After importing from an outside source (an excel spreadsheet) I have to change the short-date format that I have just imported into a very specific custom format: "yyyymmdd" Ex: 20040828. How do I convert into this new format? Help will be greatly appreciated...
can't find how to add today's date to a report formatted as "August 30, 2014." I can't understand why that's not one of the options when inserting a date.
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.
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.
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.
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?
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:
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.
I 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.
Someone 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 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.
I 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.
I 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().
