Solved

error message crystal reports9

Posted on 2011-02-23
35
346 Views
Last Modified: 2012-05-11
I an trying to display elapsed time for start and stop of travel time, I am usiing the following function in a formula

 "DATEDIFF('h',{start time},end time)" I receive the error ")" expected and the h is highlighted

Thanks
George
0
Comment
Question by:GeorgeSalet
  • 15
  • 13
  • 5
  • +1
35 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 34967386

Hi please try with  "  instead of ' around the h

DATEDIFF("h",{start time},end time)
0
 
LVL 8

Expert Comment

by:kingjely
ID: 34967404
Hi Sorry,
Put this in your formular

DATEDIFF('h',#2011-01-01#,#2011-01-02#)  you should put this on your report and get 24.00.

If you swap in your ,({start time},end time) and it still gets the error then its your starttime and endtime fields that are wrong.

What are you starttime and endtime fields>?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34967411
You need end time in { }

DATEDIFF('h',{start time},{end time})

Are start time and end time of type datetime?

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 34968684
Single-quotes around the h are fine.  Obviously (as already mentioned), if "end time" is a field, it should be in {}.  But that won't give you the error that you described.  At least not in CR 10.  You'd get an error saying "A number, currency amount, ... is expected here".

 Is that DateDiff function the only thing in the formula, or is there more?  If there's more, I think the problem is probably somewhere else in the formula.

 James
0
 

Author Comment

by:GeorgeSalet
ID: 34976023
apparently I had the quotes wrong, but I am still having problems with my info.
My input information is time, I thought the datdif would work.
field information from the table is
start= 07:59:13 stop = 08:02:02 There is also a date field 2/9/2011 12:00:00am
I need to get the time in hours or fractions of

Thanks for all the help I sure am learnin
George
0
 
LVL 100

Accepted Solution

by:
mlmcc earned 250 total points
ID: 34976329
Crystall will accept either ' ' or " " for strings.

DateDiff requires a datetime field

Try

DATEDIFF('h',DateTime(Today,{start time}),DateTime(Today,{end time}))

Your example would give 0
if you need better accuracy then you need to subtract to get seconds and convert

Local NumberVar myHours;
Local NumberVar myMinutes;
Local NumberVar  mySeconds;
mySeconds := DATEDIFF('s',DateTime(Today,{start time}),DateTime(Today,{end time}));
myHours := mySeconds \ 3600;
mySeconds  := mySeconds  MOD 3600;
myMinutes := mySeconds \ 60;
mySeconds  := mySeconds  MOD 60;

CStr(myHours,0) & ":" & CStr(myMinutes ,0) & ":" & CStr(mySeconds  ,0)

mlmcc






0
 
LVL 34

Expert Comment

by:James0628
ID: 34977617
 > DateDiff requires a datetime field

  Technically, it also accepts dates.  The Help actually says that the arguments are datetime, but they don't have to be.  Of course in this case the arguments are datetime.  I'm just saying that, generally speaking, they can be dates too.

 James
0
 

Author Comment

by:GeorgeSalet
ID: 34988625
Guys
I am using this bit of code and recieving a error:
DATEDIFF('h',DateTime(cdate({DispTech.DispDate}),time({DispTech.DispTime})),DateTime(cdate({DispTech.DispDate})),time({DispTech.TimeOn}))

The error highlights the last table field "DispTech.TimeOn" and says number expected. I don't understand this since I am using a very similar string in the first part of the function "DispTech.DispTime" I tried using the Val() agument to convert to a number that passed the check but would not run.
My previous post shows the exact field properties

The more I work on this the more I don't Know<GRIN>
Thanks
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34988662
I think you have a ) in the wrong place

DATEDIFF ('h',   DateTime(cdate({DispTech.DispDate}),time({DispTech.DispTime}))
                        ,DateTime(cdate({DispTech.DispDate})),time({DispTech.TimeOn}))
                                                                                      ^

Try

DATEDIFF ('h',   DateTime(cdate({DispTech.DispDate}), time({DispTech.DispTime})), DateTime(cdate({DispTech.DispDate}),time({DispTech.TimeOn})))

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 34989175
Mimcc
That worked This is the expression I finally ended up with:
DATEDIFF ('s',DateTime(cdate({DispTech.DispDate}), time({DispTech.DispTime})), DateTime(cdate({DispTech.DispDate}),time({DispTech.TimeOn})))/3600

This new expression created a new problem. The database is selected on invoice = to employee, each invoice may have several "DishTech" records, I need to limit them to one employee,

I am wondering if I went about this wrong?  a job starts when a employee gets a dispatch, it then turns into a quote or sale. I want to track each employees sales, travel time, time on job, unbilled time, time doing quotes etc.
Any insight would be greatly appreciated
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34989263
How you go about that depends on the dataset you have and what the information represents.

What columns do you have?

Does a job have several records?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35022833
Sorry about taking so long to get back to you
I don't know it I should create another request as you answered the first question?

I reorganized my report and it is working sorta <GRIN>
i am using this expression to select the records

{DispTech.DispDate} in {?date} to {?edate}and {DispTech.ServiceMan}= {?Emp}

I am grouping on the dispatch number, I am getting records returned with ?Emp  that are not equal to the ?Emp I specified. Once I get this completed The rest should be easy......
Thanks
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35023163
Your selectioon statemen looks like it should work.  Is the report saved with data?

Are you using the right field for selecting?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35023258
Mimcc
I am saving with data. Is that good or bad? the field I am selecting on is the technician that worked on the job
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35023509
Try it without saving the data.

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35024226
I tried saving without saving data, the data changed but still had unwanted records.
I am selecting records from the "report, Selection, records from the menu. Should I be selecting them from the group?
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35024315
That should work.

What are you entering for ?Emp?

mlmcc
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 

Author Comment

by:GeorgeSalet
ID: 35024454
?Emp is the employee number from the employee table that is linked to the Disptech,serviceman field

I am using a left hand outer link?? would that have a effect?

George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35028765
No, the left outer join would get all employees and the related DispTech records.

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35031444
I tried using the employee number instead of a perimeter I received the same results. Each group contains all the employees who worked on that job. for some reason the report is not selecting the records by technician. Could it have to do by the way it is linked??
Open to any and all suggestions
george
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35031581
Are they separate records?

What SQL is being used?
  DATABASE --> SHOW SQL

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35031645
WOW  The more I do this the more I need to learn


 SELECT "SalesEmp"."RegHr", "Sales"."LaborCost", "Sales"."InvAmount", "Sales"."MatCost", "SalesEmp"."Credit", "Sales"."InvType", "Employee"."EmpName", "Sales"."Invoice", "Employee"."Rate", "Employee"."Overhead", "Dispatch"."Dispatch", "DispTech"."DispDate", "DispTech"."DispTime", "DispTech"."TimeOn", "DispTech"."ServiceMan", "Customer"."FirstName", "Customer"."LastName", "Location"."Add1", "Location"."City", "SalesEmp"."Emp", (((("Sales"."InvAmount" - "Sales"."MatCost") / 307) * "SalesEmp"."Credit") * .01), ((("Sales"."InvAmount" - "Sales"."MatCost") * "SalesEmp"."Credit") * .01), ("Sales"."InvAmount"*"SalesEmp"."Credit" *.01

), (("Sales"."MatCost"*"SalesEmp"."Credit")*.01)
 FROM   ((((("service"."dbo"."Employee" "Employee" LEFT OUTER JOIN "service"."dbo"."DispTech" "DispTech" ON "Employee"."EmpNo"="DispTech"."ServiceMan") LEFT OUTER JOIN "service"."dbo"."Dispatch" "Dispatch" ON "DispTech"."Dispatch"="Dispatch"."Dispatch") LEFT OUTER JOIN "service"."dbo"."SalesEmp" "SalesEmp" ON "Dispatch"."Invoice"="SalesEmp"."Invoice") LEFT OUTER JOIN "service"."dbo"."Sales" "Sales" ON "Dispatch"."Invoice"="Sales"."Invoice") INNER JOIN "service"."dbo"."Customer" "Customer" ON "Dispatch"."CustNo"="Customer"."CustNo") INNER JOIN "service"."dbo"."Location" "Location" ON ("Dispatch"."LocNo"="Location"."LocNo") AND ("Customer"."CustNo"="Location"."CustNo")
 WHERE  ("DispTech"."DispDate">={ts '2011-01-01 00:00:00'} AND "DispTech"."DispDate"<{ts '2011-02-01 00:00:00'}) AND "DispTech"."ServiceMan"='0023'
 ORDER BY "Dispatch"."Dispatch"


Thanks
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35031784
Did you try using Employee.EmpNo to filter on?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35032544
I tries selecting records Employee.EmpNo=0023  0023 is one of the employee numbers
same results

George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35032678
Can you provide a screen shot or the report with saved data that shows the issue?

mlmcc
0
 

Author Comment

by:GeorgeSalet
ID: 35033005
Mimcc
attached is a screen shot,
 CR-George-1.pdf
first column in bold is the dispatch number "2102986"
below that is the number 0023 this is dispTech.serviceman feild
to the right of the field is the SalesEmp.Emp number
as you can see they don't match, in fact 0023 only worked once on this job.

I used a database viewer in the dispatch program to list all DispTech records for this dispatch, the screen shot is below
 CR-George-2.pdf

Below is my report with saved data
 HoursLabor-6.rpt

Thanks for all your help
George
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35033372
You have DISPATCH linked to LOCATION
Remove the link and try again

mlmcc
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 35033387
I think the problem is the links between the tables or the order of linking.

mlmcc
0
 
LVL 34

Expert Comment

by:James0628
ID: 35034997
The problem appears to simply be that you're looking at fields that serve different purposes.

 You said that you've tried selecting on DispTech.ServiceMan and on Employee.EmpNo, and got the same results with either field.  You would, because those tables are linked on those fields.  They're going to normally be identical.  Since you used a left outer join from Employee to DispTech, there could be cases where Employee.EmpNo has a value and DispTech.ServiceMan is null, but aside from that, they'll be identical  (BTW, off hand, that outer join seems backwards.  It seems like that should be a right outer join, or a left outer join from DispTech to Employee).

 Meanwhile, on the report, you're comparing DispTech.ServiceMan and SalesEmp.Emp, but they're not directly related, as far as the tables are concerned.  You link from Dispatch.Invoice to SalesEmp.Invoice (the link is based on the Invoice number, not the Emp number).  You appear to have a "dispatch employee number" and a "sales employee number", and they appear to be two different things.

 Basically, it seems like you have one DispTech.ServiceMan for a dispatch (which also means only one Employee.EmpNo, since they're linked), but either multiple Invoice numbers for a dispatch leading to multiple SalesEmp records with different Emp numbers, or a single Invoice number with more than one Emp in SalesEmp, or both.

 The question is, given these different "emp" numbers, which ones do you really want to see?  If you really only want to see a single SalesEmp.Emp, then maybe you just need to compare that with your parameter, instead of DispTech.ServiceMan or Employee.EmpNo.

 James
0
 

Author Comment

by:GeorgeSalet
ID: 35040803
My goals with this report are to:
account for all employee time
account for all sales and costs

attached is a revised link page George-Link.pdf

Link page explains my plan of attack but I will elaborate
the employee number(Employee.Emp) represents the employee on the job
every time a employee goes out on a job(dispatch) a record is created in the DispTech Table. The DispTech.Serviceman is equal to the employee number.
I wanted to list all dispatches the tech worked on
from that list I wanted to mine the amount of hours , travel time and costs from the sales tables
SalesEmp is created for every invoice that is created,(only one per employee per invoice) SalesEmp contains the amount of hours, percentage of credit for the job etc.
Sales table has the invoice information and total amounts.

I think my problems have to do with linking?? since I changed the links around I went from 16 pages to over 35K pages
Not sure where to go from here
George
0
 

Author Comment

by:GeorgeSalet
ID: 35040912
After Thought
record selection:{DispTech.DispDate} in {?date} to {?edate}and{DispTech.ServiceMan}= "0023"
0023 is a employee number
0
 

Author Comment

by:GeorgeSalet
ID: 35041294
I think I got it! could someone verify that I am not breaking the rules? attached is my link layout
 George-Link-2.pdf
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 35043249
It looks OK to me.  You're including a link to SalesEmp.Emp now, so you won't get those other Emp numbers anymore.

 FWIW, I would make some of the links more direct.  You're linking from Dispatch.CustNo to Location.CustNo to Customer.CustNo.  I would probably just link Customer.CustNo directly from Dispatch.CustNo.  Similarly, you're linking from DispTech.Serviceman to Employee.EmpNo to SalesEmp.Emp, and I would probably just link to SalesEmp.Emp from DispTech.Serviceman.

 Those links just seem a bit simpler.  I doubt that it would have any significant effect on the report, one way or the other, unless there are some customers or employees that are missing from the intervening tables.  For example, if there are customers (or CustNo and LocNo combinations) in Dispatch that are not in Location, but are in Customer, then if you link to Customer by way of Dispatch, you won't get those customer records from Customer, but if you link directly from Dispatch to Customer, you could still get those records from Customer.  However, judging from your screenshot, you seem to be using inner joins, so if a customer (or CustNo and LocNo combination) can't be found in Location, they won't be included in the report anyway.

 James
0
 

Author Closing Comment

by:GeorgeSalet
ID: 35051972
Great work!!
Thanks for hanging in there
George
0
 
LVL 34

Expert Comment

by:James0628
ID: 35067034
You're welcome.  Glad I could help.

 James
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now