error message crystal reports9

Hi please try with  "  instead of ' around the h

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

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>?

You need end time in { }

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

Are start time and end time of type datetime?

mlmcc

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

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

 > 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

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

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

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

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

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

Your selectioon statemen looks like it should work.  Is the report saved with data?

Are you using the right field for selecting?

mlmcc

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

Try it without saving the data.

mlmcc

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

That should work.

What are you entering for ?Emp?

mlmcc

?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

No, the left outer join would get all employees and the related DispTech records.

mlmcc

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

Are they separate records?

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

mlmcc

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

Did you try using Employee.EmpNo to filter on?

mlmcc

I tries selecting records Employee.EmpNo=0023  0023 is one of the employee numbers
same results

George

Can you provide a screen shot or the report with saved data that shows the issue?

mlmcc

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

You have DISPATCH linked to LOCATION
Remove the link and try again

mlmcc

I think the problem is the links between the tables or the order of linking.

mlmcc

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

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

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

I think I got it! could someone verify that I am not breaking the rules? attached is my link layout
 George-Link-2.pdf

Great work!!
Thanks for hanging in there
George

You're welcome.  Glad I could help.

 James