Data Conversion Expression returns #Error

Objective:  Convert txt data representing a time stamp on a particular 
activity into DateTime format.
1.	If the data field contains any data convert it to the DateTime format
2.	If data field is Null or a zero length string would like to return a null.
3.	The data usually come in 0000 format but it could be 000 or 00 or 0 or “ “.

CutT_temp: 
CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)))

The expression works fine if the original txt field contains normal 
(expected) data but returns an #Error if field starts with a zero length 
string or a null.

0
Utf
11/20/2009 3:36:01 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1095 Views

Similar Articles

[PageSpeed] 56

Try this --
CutT_temp: IIF([Cut_Time] Is Null OR [Cut_Time] ="", Null,
CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0))))

-- 
Build a little, test a little.


"D. Stacy" wrote:

> Objective:  Convert txt data representing a time stamp on a particular 
> activity into DateTime format.
> 1.	If the data field contains any data convert it to the DateTime format
> 2.	If data field is Null or a zero length string would like to return a null.
> 3.	The data usually come in 0000 format but it could be 000 or 00 or 0 or “ “.
> 
> CutT_temp: 
> CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)))
> 
> The expression works fine if the original txt field contains normal 
> (expected) data but returns an #Error if field starts with a zero length 
> string or a null.
> 
0
Utf
11/20/2009 4:20:07 PM
So, what time is 23?  23:00 or 00:23.

I proposed a solution to this in another post of yours on the 19th.  Did you 
try the following and did it work or not?  If it failed, can you tell us the 
problem with the solution.  The only thing I can see it that if CutTime was a 
zero-length string (zls) then you would get a time of midnight.  That can be 
handled by testing for that possibility in the IIF statement.

IIF(IsDate(Format(Cut_Time + "00","00:00:00")),CDate(Format(Cut_Time + 
"00","00:00:00")),Null)

You can then apply a format to that time if you want to
Format(IIF(IsDate(Format(Cut_Time + "00","00:00:00")),CDate(Format(Cut_Time + 
"00","00:00:00")),Null),"hh:nn AM/PM")

To test for null or zls or multiple spaces you could use

IIF(Len(Trim(Cut_Time & ""))>0 AND IsDate(Format(Cut_Time & 
"00","00:00:00")),CDate(Format(Cut_Time & "00","00:00:00")),Null)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

D. Stacy wrote:
> Objective:  Convert txt data representing a time stamp on a particular 
> activity into DateTime format.
> 1.	If the data field contains any data convert it to the DateTime format
> 2.	If data field is Null or a zero length string would like to return a null.
> 3.	The data usually come in 0000 format but it could be 000 or 00 or 0 or “ “.
> 
> CutT_temp: 
> CDate(IIf(IsError(FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)),"",FormatDateTime(IIf(Len([Cut_Time])<1,"",Left([Cut_Time],2)+":"+Right([Cut_Time],2)),0)))
> 
> The expression works fine if the original txt field contains normal 
> (expected) data but returns an #Error if field starts with a zero length 
> string or a null.
> 
0
John
11/20/2009 4:56:49 PM
Reply:

Similar Artilces:

Macro or Formula needed to search data in cells
Hi I am looking for a way to create a formula/macro to do the following: My worksheet setup: A1:A30000 C1:C5000 01 02 03 04 05 06 07 08 09 10 01 04 05 06 08 09 12 22 23 27 02 03 04 05 06 07 08 09 10 11 01 03 05 06 07 08 09 14 22 32 03 04 05 06 07 08 09 10 11 12 04 05 06 07 08 09 10 11 12 13 etc Each cell contains a 10 number sequence. The range A1:A30000 is my randomly generated sequence. And C1:5000 is my database of archived sequences. If I wish to check if the combination in cell A1 is anywhere in the range C1:5000. I use the formula. =IF(COU...

Errors with Distribution Group Wizard and link to Public Folder Ar
I'm having a problem with setting up a Distribution Group with the Public Folder. My objective is to create a number of Distribution Groups that will archive the e-mail to them to a public folder. The DG and PF are both being created, but the link between the two is not. My objective is to use the PF as the store where a team will go to view general e-mail addresses that are being sent (webmaster@mycompany.com, for example). I am using the Add Distribution Group Wizard (ADGW, Server Management\Standard Management\Distribution Groups\Add a Distribution Group) to add the DG. I am...

Setting up Outlook Express to work with an MSN e-mail account
Does anybody know how to set up Outlook Express to work with an MSN e-mail account? There are instructions for Hotmail but they don't work. Any help is appreciated... "J.O." <anonymous@discussions.microsoft.com> wrote in message news:041b01c3db01$0002a750$a501280a@phx.gbl... > Does anybody know how to set up Outlook Express to work > with an MSN e-mail account? There are instructions for > Hotmail but they don't work. Any help is appreciated... This newsgroup is for support of Outlook 97/98/2000/2002/2003 from the Office suite of products. Outlook Expr...

Search for data in a column bring all related items in other colum
I have data in the excel sheet with .A column for OrderNo and B Column for items for that Order. A B ORD001 ITEM1 ITEM2 ITEM3 ORD002 ITEM4 ITEM1 ITEM5 There are more than thousand orders.I want search for an Order so that it brings all items with it. I cannot use filter in A column as it doesn't recognise blank cell.So Option to repeat order nos in A for every item involves a lot of data entry. Any Solution ? I'd fill those empty cells in column a with the previous value. And us...

MSDN Installation 1.2: Javascript Errors
I have installed a CRM Server and separate CRM client (along with the supplied example database). Access through IE6 works fine. Within Sales for outlook, I have 2 issues; 1> It takes 1.5 minutes to start up Outlook (it's only a 1Ghz machine, but CPU usage appears as 1-2% while it is loading). 2> I get various script errors when clicking on the CRM Shortcuts; Errors include messages such as... 'http//crmsvr/sfa/home_comps.aspx - object does not support this property or method' IE script error 'Could not complete the operation due to error 80020101' For testing, ...

Microsoft Outlook 2000 error #2
I have not been able to keep Outlook up on our server, it closes soon after it is opened with this error that I cannot understand. It says: Exception: access violation (0xc0000005) Address: 0x3f99a142 What does it mean and how can I fix it? Does anyone have any idea? Thank you! ...

Ports 465 995 Error 0x800CCC0F Windows Mail
AT&T internet server new outgoing and ingoing email ports 465 and 995 do not work with Windows Mail Vista 64-bit OS. Need to change from old ports 25 and 110. Does anyone know how to fix? -- sumac Posted via http://www.vistaheads.com "sumac" <sumac.424jga@no-mx.forums.vistaheads.com> wrote in message news:sumac.424jga@no-mx.forums.vistaheads.com... > > AT&T internet server new outgoing and ingoing email ports 465 and 995 do > not work with Windows Mail Vista 64-bit OS. Need to change from old > ports 25 and 110. Does anyone know how t...

Why does linked data from blank cells become a zero?
When linking data within the same workbook (to different tabs) any cell that is empty - shows up in the new tab as a ZERO (0).....what am I doing wrong? I want those cells to remain blank. That's just the way it is .. Instead of : =Sheet2!A1 you could use an error trap: =IF(Sheet2!A1="","",Sheet2!A1) Alternatively, we could suppress the display of extraneous zeros in the entire sheet via: Click Tools > Options > View tab > Uncheck "Zero values" > OK -- Rgds Max xl 97 --- GMT+8, 1� 22' N 103� 45' E xdemechanik <at>yahoo<dot&g...

Server Error in '/' Application. 08-10-06
Dear all, Out of a sudden, I'm unable to login to a production server using the default account used for installation. Any help would be appreciated! Microsoft CRM Unhandled Error Details: Server Error in '/' Application. -------------------------------------------------------------------------------- Object reference not set to an instance of an object. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.N...

How change dimensions of data label text box in pie chart?
In Excel 2003, I can't seem to re-shape the data labels text boxes in my pie chart. I can change the font and allignment of the text within the text box. But, if the text defaults onto two or three lines, I cannot stretch the text box out to get the text to stay on one line. Normally, you can pull on the the little handles on the text box. Is there a trick to this that I don't know about? This is a deficiency in Excel's charting capabilities. The text elements seem like regular text boxes, but you are unable to resize or reshape them. Even VBA can't do this. The workar...

Error while synchronizing with net time server
Vista Ultimate SP2 I just noticed that my machine will not synchronize with any of the Internet time servers. It's set to do so automatically, but I see an error saying "An error occurred while Windows was synchronizing with [server name]. This happens is I try to sync manually, and it happens with all server (i.e. time.windows.com, time.nist.gov, time-a.nist.gov, time-b.nist.gov, time-nw.nist.gov). Suggestions? Thanks. Ken Isaacson SILENT COUNSEL, a legal thriller www.KenIsaacson.com Ken Isaacson wrote: > Vista Ultimate SP2 > > I just noticed...

Area, perimeter, and color in shape data
Hi everyone, I have Visio Professional 2007. First, I don't have great skill yet in making shape data. But also, I would like to have the following data show in the shape data for various geometrical shapes (like squares, rectangle, or circles, for example): - the area of the shape - the perimeter of the shape - the color of the shape Now, I understand that I can go to Tools > Add-Ons > Visio Extras > Shape Area and Perimeter to get the first two items. I also know that this information will change dynamically as I alter the shape. So far, so good. ...

Getting data point from an accel chart
I have an excel chart that I want to get the data points(x,y) from. The source data refers to cells in the same sheet, but I don't see any cell in that work sheet. Only the chart. I can run the cursor over the line and get the x,y coordinates for each point. I would like to copy the x,y values, to reproduce the curve in another program ...

Trying to automatically import specific data from Excel into Word:
Thanks in advance for anyone who can help me with this: I'll start by explaining the spreadsheet I created in Excel (an inventory / information worksheet): It is pretty simple; approximately 12 columns. The key column is the item number / UPC / bar code number, then after that there are various information columns including item name, description, condition, picture links, etc. Secondly, I created a template Microsoft Word document that is relatively long, but will have a few lines (give or take...about 7) customized for each item on the inventory. What I am trying to d...

ADDITIONAL INFO RE: Data execution prevention
Since my last post, I have some additional info that might help someone get an insight as to what the problem may actually be. First, I went to start/run and put in "sfc /scannow", and let it run fully. Everything copacetic there. The I checked Admin. tools for the read-out. Under the "Error" heading was this info: Faulting Application rundll32.exe version 5.1.2600.5512, faulting module unknown, version 0.0.0.0. fault address0x00000000 Clicking on the "Online Help" link brought up a blank page - no help. Hoping some terrifically insightful MV...

CRM Integration: data doesn't go from CRM to GP
I'm setting up CRM integration for GP. Setup went ok, data migration from GP to CRM ok. When I add data to GP, it copies to CRM ok. I can't seem to get any CRM data to copy to GP. No messages in event viewer. Services are all started (according to documentation) successfully. I'm pretty sure I've configured properly in Settings and Admin tool. My IntegrationUser account is a member of all the required groups. I've turned on 'create customers always' to test, so I should create customers in GP whenever I create an account in CRM, but that's not happe...

Any tip / clue to troubleshoot errors when upgrade GP10 SP2 to SP4
There are a few error messages during GP 10 SP2 upgrading to SP4. Installation of SP4 was fine, but until the stage when it run GP Utilities, a few error prompted. Another error is related to Dexterity Runtime - Add-In Initialization Error. Could you please provide me some clue or guidance on how identify the reasons for the errors. Many thanks in advance. http://img651.imageshack.us/img651/6400/errorwhenupgradegpsp2to.png http://img52.imageshack.us/img52/6400/errorwhenupgradegpsp2to.png http://img191.imageshack.us/img191/6400/errorwhenupgradegpsp2to.png Error when running GP ...

Data was deleted and saved by mistake..need to go back
I need to go back to the data before it was saved, can anyone help Richard don't get your hopes up. Unless you have a copy or a backup ... it's gone. Regards Trevor "Richard" <anonymous@discussions.microsoft.com> wrote in message news:1084801c3bea6$222d5270$a601280a@phx.gbl... > I need to go back to the data before it was saved, can > anyone help ...

Error when send email from campaign response using template through workflow
Hello, There is an invalid argument error shown in workflow records if send email from campaign response using template. Is it impossible to send email in this way ? ...

Error Handler #2
Has anyone written an error handler for VBA forms. The error handler would display error messages based on bad input and pop up messages dynamically, etc. If not, has anyone developed a clean way to display error messages without using excessive message boxes? Thanks for any help. --- Message posted from http://www.ExcelForum.com/ Instead of message boxes and popups, consider a label on the form itself that displays the error message. In article <Ripan.zrum2@excelforum-nospam.com>, Ripan <<Ripan.zrum2@excelforum-nospam.com>> wrote: > Has anyone written an error ...

Invalid Argument Error when using 1/1/08 as a date
Hi all, Not sure if this is a known issue - can anyone else create a record that contains a date field with the value of 1/1/08 or 1/1/07, save it and then open it again without getting an "Invalid Argument" error. I am able to produce this error across different environments, different entities and different date fields. Would be interesting to hear more about this. Thanks Hi there, Just to let you know, I just created a task in CRM 4 and set the due date to 1/1/08 and it worked without any issues. This is a field displayed as a Date/Time box, on not just Date. I don...

Issue in reading data from Excel
Hi All, I have 3 excel files with me. say,Test.xlsx, saved.xls and original.xls. Here Test.xlsx has got reference to saved.xls and original.xls and displays values from both files. But the cells referring to data from Original.xls shows #N/A in Test.xlsx where as data from Saved.xls shows the correct values. (I am using the formula: =HLOOKUP(A5,'D: \Testing\[Original.xls]Output'!$A$1:$CQ$2,2,FALSE) Here, saved.xls and original.xls has got same columns and same data, but if i check the files size of both, they are different. size of Saved.xls is 26 KB and the size of Original.xls file...

Carriage Return/Newline in CEdit ?
Hi guys, I want my user to be able to "\r\n" into a CEdit to specify he wants a carriage return, but when he enters it it becomes a string literal (is that the right term ?) For example, the user wants these created strings to end with a \r\n : CString left="some stuff"; m_edit.GetWindowText(right); The user types "\r\n" in the edit box to cause a line break. CString output=left+right; // should end with "\r\n" That's a simplified example but I think I explained it right. Take care, Robert Hi Robert, I'm not sure what you want exactl...

Transfer data from Excel col. A to columns B-E in the same sheet
I have an Excel 2003 spreadsheet with only one column of player data: column A. The first three data items in column A are the same for every player: Name, Address and Phone. Every player also has at least one comment but could have any number of comments. Each player’s data is separated from the next by a blank cell in column A. Sometimes, a player’s last few comments are blank resulting in multiple blank cells in column A before the data for the next player starts. I need help writing an Excel 2003 VBA macro to: 1. Copy just the player’s name, but not the Name: label, to c...

Error while installing language pack in MS CRM 4.0
Hello people, Downloaded and installed a couple of language packs succesfully in MS CRM 4.0. However, when I log into the CRM interface to actually activate the language pack I get an error when I try that. When I look at the application log in Windows, I can see the following error coming form the MSCRMWebService: MUI Provisioning failed. Error: Cannot insert duplicate key row in object 'dbo.LocalizedLabel' with unique index 'ndx_LocalizedLabel_ForSingleSelect'. The statement has been terminated. Anyobdy a suggestion ? Many thanks in advance ! Regards, Mav. I have...