Is it possible to convert the text field that comes in this form:
:80421
to the following datetime format: MM/DD/YY 12:00:00AM
Please note that the text field contains a colon at the beginning. The number 8 represents a year (2008), the 04 represents a month (April) and the last two characters are days in the month (21). The converted field should look like:
04/21/08 12:00:00AM
Thank you.
Peter
I wondered if someone could help.
I am using the Val () Function to convert a text field to number within a query which still gives me a text output.
P.S I do not have permissions to change within table as using Access as a front end to SQL via link Tables.
Hi, I have the following problem which I hope someone can help me with:
I am importing a CSV file, one of the field in which contains times in the following format: 1200 (ie this is 12:00, th text file does not contain the : separator)
I want these to be recognised as time values by access, which at the moment only treats them as text. Is there any way i can run a query on this table to convert the numbers into times? I can do it in excel using concatenate to add in the :, but access does not have this function.
Thanks in advance!
Hi there,
I have a table with a field called 'dereg_date' which is formatted as text, an example is as follows:
2006-07-07 00:00:00.000
I want to be able to convert this field to a date. I have tried using a query with Cdate([dereg_date]), however it doesn't convert it and just returns #Error for all the records.
Has anyone got any ideas on this? I need this fixed fairly urgently as my superiors are getting quite impatient!
Many thanks in advance!!
Sasha.
Hi All,
I have a table with person's name, clubname, cluburl.
In a report I can show the clubname,but when I export to HTML, can this clubname
become a hyperlink to the clubURL ? (While showing the clubname..)
Anybody ideas ?
My Db had an email_address field which was a hyperlink. For various reasons I had to change it to a text field. However, now every email shows as:
some_email@domain.com#http://some_email@domain.com#
Is there a simple way of cleaning this up so I am only left with the email address? In other words is it possible to create an update query to remove everything between # signs (including the # signs)?
----
I found some vba code on the forum which extracts strings but was wondering if there is an easier way just using an update query since I'm still not too efficient with vba
Dear All:
I have creadted a form with a combo box that has 3 dates in it: February 1, 2005, September 1, 2004 and June 4, 2004.
Is there a way to display in another field on the form(A text field) when one of these dates is selected from the combo box, a text field returns "DATED ON THIS FIRST DAY OF FEBRUARY TWO THOUSAND FIVE"?
I reckon this is a vb issue and any help is appreciated.
Thanking in advance
Dion
I have a date field in a form which is not compulsory.
I then pull this date field into a query to create a report.
If the date is not entered then is it possible for "Date not entered" to be written into the report automatically. I have tried playing around with nulls but have had no luck.
I have a file called CAPTURE.BUF. I am trying to convert it to text. Here is what I ahve done, but I get the message box ever time.
Public Function copyFile()
Dim SourceFile As String
Dim DestinationFile As String
SourceFile = "C:PCPICSWCAPTURE.BUF"
DestinationFile = "C:INVESTMENT_REPORTSCAPTURE.TXT"
On Error Resume Next
FileCopy SourceFile, DestinationFile
If Error > 0 Then
MsgBox "Could not copy mls file."
End If
On Error GoTo 0
End Function
I have a short text field where numbers such as "15.00", "2233.56", "-300.00" are stored. Now I want to convert the text field to numeric so that I have actually 15.00 or -300.00 stored as a double. I am going to do calculations on these fields. How can I achieve this conversion?
View 4 Replies View RelatedUsing 2010 (accdb format) on windows 7.I'm trying to convert a memo field's format from Plain Text to Rich text. The problem is that every time I try to select the Rich Text format option the field immediately changes back to Plain Text.
Just as a test a created a new memo field in the same table and set this to Rich Text (worked fine) then copied the records for the Plain text memo field to the new memo field. Unfortunately this removed all line breaks so instead of my text looking like
I have a string that includes mm/dd/yyyy + person's name. All of this is concatenated and is recognized as a text value. Then I want to convert all of these characters to a number. Since each character has an ASCII code, I would like to find ASCII equivalent.
In Excel, this would involve using something like the CODE ( ) function, but that only gives the numerical equivalent for the first character in the string, but I want all characters at once.
When I try the VAL ( ) function, it returns the first character which is the number of the month.
I have a text file delimited and uploaded the information to a new Access Table. One field "MarketValue" has a number followed with a percentage (i.e. 100%, 54%, etc..), however it converts as a text column.
How can I convert the column to a TRUE percentage field representing the value originally imported.
In a table called Appointments and the field called Plastics we enter the initials of surgeons.
However in a report i want the initials converted to full text - in the example below the initials are HJC
The expression below doesn't work - it just returns everything in the Plastics column.
Any ideas?
Expr1: IIf(HJC([tblAppointment]![Plastics]),"Heather Cleland")
Hi,
Can anybody help me ?
I've a case where ms access displays oracle's numeric field as text where msaccess is linked to oracle.The possible reason could be when we don't specify precision in declaring the filed in oracle table, default precision is 38 which ms access cannot hold. So, it(driver) converts this to text field.But this is with MSORCL32.dll(microsoft driver for oracle).Is there any other possible reason that will result this behavior?
The following are the configuration details:
1.Oracle 9i on Unix server.
2.MSAccess on Windows XP.
MSAccess holds linked data of Oracle server.
Thanks Inadvance.
Ramesh
I am using the funtion:
Clng([text field]) to convert a text field into a number so that I can link in a query. Because it is an informix database, I cannot change the underlying table format.
When I run the query, the data is showing "Error".
I tried changing the other table to text and linking but it doesn't seem to link properly because the query doesn't work.
Your advice appreciated.
I have an option box 1=Yes, 2=Np 3=Unknown. the result is stored in a field in my table. When i run a query I pull up the numbers 1, 2 or 3 but to put in a report I actually want the written answers. How do i convert them back?
View 9 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 RelatedI have a text field like, 11242010, and I need to be able to convert it into 3 int fields, day, month, year. I am trying to do this in a query and have create the following three;
DateD: IIf([DATE] Is Not Null,(CInt(Left(Right([DATE],6),2))))
DateM: IIf([DATE] Is Not Null,(CInt(Left([DATE],2))))
DateY: IIf([DATE] Is Not Null,(CInt(Right([DATE],4))))
Time: IIf([TIME] Is Not Null,[TIME])
When I have a value of Null, i keep getting #Error, I think when it's null.
I have a text field I need to convert to numbers. There are both empty and non empty fields. I then need to show the numbers like this "1.234" and a "*" in the empty fields.
I have tried to convert the fields with Cint, but I get an #Error in the empty fields.
Is there any way to solve this??
Thanks in advance..
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 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.
Hi! This is my first question here.
I want to make query where I want to show data only from time1 to time2 (for a month).
I want to make this in Access because I need Forms.
I made this on the SQL Server but I had to change the time limits in the code what I don't like. How to make this in Access SQL?
On the SQL server it looked like this:
SELECT * FROM TimeSample
WHERE TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) > '10:00' AND
TimeVal - CAST(FLOOR(CAST(TimeVal AS float)) AS datetime) < '17:00'
Any ideas?
Thanks, Bob.
Hey Guys,
Having some trouble doing a couple of things using both Data Definition Language and a Right() Query.
What I am trying to do is convert this field:
August 17, 2006 3:56 PM
into this
15:56:00
Using this:
Right([Travel Date/Time To],InStr([Travel Date/Time To],",")-1)
Now because the time part in my first field can vary from being 3:56 PM to 12:56 PM Right() is only getting those where the time has two digits.
I tried to use DDL to convert my field to DATETIME but cannot convert two columns in one SQL statement.
Can anyone help me out with this? Sorry if the details are sketchy but I'm being kicked out of the office (after 5:00pm you see!).
Cheers.
I'm sure this is simple but my brain is mushy this morning!
I have two datetime fields ADMISSION and DISCHARGE with values such as 03/02/2008 15:00. How do I convert these to dates only? I don't want to just *format* them as dates, which I can do. In this example I would want to convert that date to 03/02/2008 (or 03/02/2008 00:00)
It's an idiosyncrasy of the way the NHS calculates hospital length of stay. Our previous IT system had dates and times in separate fields so I could just ignore the time field and work out length of stay based on dates only. However, our new system returns a combined datetime.
Patient A 03/02/2008 00:15 to 03/02/2008 23:45 = 0 days
but
Patient B 03/02/2008 23:45 to 04/02/2008 00:15 = 1 day
Basically, you count a day if there's a date change, even if that was only half an hour (or even a few minutes) either side of midnight, but you DON'T count a day if there was no date change, even if that was almost 24 hours.
So formatting them as dd/mm/yyyy wont work ,as I will still have partial days when I subtract one from the other and INT([DISCHARGE]-[ADMISSION]) wont always work either as it would calculate 0 for patient B.
Help!
Hello -
I have a table that looks like this:
Code:ID Station DateTime Value 1 ABQ 10/8/2005 1:00 am 102 ABQ 10/8/2005 2:00 am 203 ABQ 10/8/2005 3:00 am 254 ABQ 10/8/2005 4:00 am 5...
I am trying to build a query that, for each record, gives me the values for the previous 2 hours.
So my goal would be a query that returns:
Code:ID Station DateTime Value ValueT-1 ValueT-21 ABQ 10/8/2005 1:00 am 10 Null Null2 ABQ 10/8/2005 2:00 am 20 10 Null3 ABQ 10/8/2005 3:00 am 25 20 104 ABQ 10/8/2005 4:00 am 5 25 20...
I created a table (NewTable) that, for each record, has the DateTime, DateTimeT-1, and DateTimeT-2. So columns in Newtable are ID, Station, DateTime, Value, DateTimeT-1, DateTimeT-2 where DateTimeT-1 = dateadd("h",-1,DateTime) and DateTimeT-2 = dateadd("h",-2,DateTime).
I then joined (using a left join) this new table to the original table on [NewTable].[DateTimeT-1] = [OldTable].[DateTimeT].
so code is
Code:select [NewTable].ID, [NewTable].Station, [NewTable].DateTime, [NewTable].Value, [OldTable].DateTime, [OldTable].Value AS ValueT-1from NewTable left join ON [NewTable].[DateTimeT-1] = [OldTable].[DateTimeT]
The output does not pull all the right records. Some of the records have Null values for ValueT-1 when the datetimeT-1 is in OldTable. So for some reason the join doesn't seem to be working.
Hopefully this is clear. Any thoughts? Help is very much appreciated!
Thank you!