asked on

Formula doesn't run and comes up as a blank error... simple formula

Hi,

I have a formula that converts a datetime to a time with the format of HH:mm:ss. That formula by itself works fine. The value is converted from time to string. It's names Time.

I then add that formula to another formula (named Concat) that concatenates multiple string values together. This formula will stop the report from running if I add the Time formula to it. There is no error produced; the Time formula within the Concat formula is highlighted and the report will not continue.

The formula will pop up and highligh Time and not mention any real issue. When I check the code, there are no errors....

Mike McCracken

Can you have NULL values?

Can you show the 2 formulas?

mlmcc

susnewyork

ASKER

I forgot to mention the third formula. To be clear:

TimeString: Takes the dateTime field and changes it to a text time format.

Time: Takes TimeString and adds spaces to the end of the string as needed by my company.

Concat: Takes all needed fields and adds them to a text string.

------------------------------------------------------------

TimeString:

totext(time({TAData.DateTime}), "HH:mm:ss")

Open in new window

Time formula:

if length({@timeString}) < 11 then {@timeString} + space(length({@timeString}) - 10) else {@timeString}

Open in new window

Concat formula:

totext({@ClockID}+{@date}+left(totext({@ScanID}),1))+{@time}

Open in new window

--------------------------------------------
I just noticed that when I change the concat formula to exclude the Time formula, the following error appears for the Time formula:

"The number of copies of the string is too large or not an integer"

Mike McCracken

You are missing a )

Try this one

totext({@ClockID}) + {@date} + left(totext({@ScanID}),1)) + {@time}

mlmcc

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

susnewyork

ASKER

Hi James,

I have a similar formula that does the same thing with date. The date doesn't produce an error. The formulas for the date set:

DateString:

totext(date({TAData.DateTime}), "MM/dd/yyyy")

Open in new window

Date:

if length({@dateString}) < 11 then {@dateString} + space(10 - length({@dateString})) else {@dateString}

Open in new window

Concat:

totext({@ClockID}) + {@date} + left(totext({@ScanID}),1) + {@time}

Open in new window

The formula runs without a problem. Thoughts?

susnewyork

ASKER

Ah. Let me try that. The date formula has the order as you said it might have to be.

susnewyork

ASKER

And that was it! Thanks mlmcc.

susnewyork

ASKER

Also, thanks for your help too James.

Mike McCracken

You could have split the points with James. His comment really pointed out the error or problem.

The question can be reopened to allow you to split.

mlmcc

James0628

Good catch on the solution mlmcc. It seems obvious now that you've pointed it out, but for some reason it just didn't click here.

James

susnewyork

ASKER

I'm trying to reopen the question to reassign points. Mlmcc is right.

Is there an easy way to do that? I currently have the question marked for a moderators attention.

Mike McCracken

I could do it but since you requested their help I will let it happen that way.

mlmcc