hej out there Im having a little 2

[cm1234]

hej out there

Im having a little problem,
i need to get a arrival field and a departure field to
show up from one db in a assending order regardless wich one comes first in time, like this:
10,00 arr
10,05 dep
10,10 arr
10,15 dep
the problem is that i can only get the report to show either the arr or dep not both at the same time, at he same time my field/db where the time comes from is listed as a
number how can i convert from number to time???
Hope some one knows what i mean

 

[synapsevampire]

You'll get better results if you post example data, knowing what the output looks like and a written description of the tables leaves much to the imagination.

Presumably you have a single time field which is used for both arrivals and departures. First you need to know the formula for converting the time field, and is it a time field, or a datetime, the rest is easy.

Also useful would be the version of Crystal, the type and version of the database as we may be able to offload some of the processing to the database.

Show the layout of the tables you're working with, converting to a time or datetime is easy, as is sorting rows.

Guessing what's in your database is a tad trickier and not the best use of time

-k

 

[cm1234]

Hej Synapsevampire

Thank for replying to this thread.

First im running on CR 9,0 and the database in this case is
Paradox, the given field is set up as a number field and
needs to come out as a time field if possible.
I hope this does meet some off the requirements you have mentioned.
Best regards
cm1234

 

[basil3legs]

Is the time always in the same field, whether it is an arrival or departure? Also, where is the "arr", "dep" coming from? Are both of these from the same field as each other?

Can you let us know the layout of your database.

Thanks.

 

[cm1234]

Hej basil3legs

no the time comes from two different fields an arr and a
dep field and only God knows why they are in a number field
Im not quit shure what you mean by the layout of the database, could you please give an example so that i could
learn what you and other helpfull souls mean

best regards
cm1234

 

[lbass]

You should be able to write a formula like this:

if {trippoint} = "arr" then timevalue({arr.time}) else
timevalue({dep.time})

Use this instead of the {arr.time} and {dep.time} fields, and you can now sort on this field.

The use of timevalue might not be appropriate--it depends on the nature of your number field. Does it represent hours or fractions of hours from 0 - 24? Or does it represent something like (hours, minutes)? Timevalue should work if one of these are true, but you might have to experiment.

-LB

 

[cm1234]

Hi lbass

I have tried to use the formula you suggested:
if {trippoint} = "arr" then timevalue({arr.time}) else
timevalue({dep.time})
but i get a error saying that the field name is not known (trippoint).
the time field wich in my case is a number field represent something like (hours, minutes), how do iconvert to time??

best regards
cm1234

 

[lbass]

I was just using a made-up name for whatever your field is that has instances = to "arr" and "dep". So substitute the name of your field--unless "arr" and "dep" are actually separate fields. Please let us know if that is the case.

Can you provide an example of your number field, please?

-LB

 

[cm1234]

Hi again lbass

Yes "arr" and "dep" are actually separate fields
that is wy i thought that (trippoint) was a function.
an example of my number field,yes they look like this
4,55
5,00
5,10
I hope you get what i mean.

best regards
cm1234

 

[lbass]

If you have some fields other than the time fields which are identified with arrival or departure, as in the following fake record:

Depart_Pt Arr_Pt Dep_Time Arr_Time
Bos Chic 10:00am 2:00pm
Chic Bos 2:30pm 5:00pm
LA Denver 3:00pm 5:30pm

then you could create a formula like this, call it {@time}, where {?city} is a parameter for some or all cities you want to use in the report:

if {Depart_Pt} = {?city} then {Dep_Time}&" Dep" else
if {Arr_Pt} = {?city} then {Arr_Time}&" Arr" //place in details, along with {Depart_Pt} and {Arr_Pt} and conditionally suppress the city fields with:

{Depart_Pt} <> {?city}//for suppressing {Depart_Pt}

{Arr_Pt} <> {?city} //for suppressing {Arr_Pt}

This should create two records for each line above, one specific to arrivals and one for departures, but with only one date field {@time}.

Then you should be able to sort on {@time}.

Then your results should look like:

Bos 10:00am dep
Chic 2:00pm arr
Chic 2:00pm dep
LA 3:00pm dep
Bos 5:00pm arr
Denver 5:30pm arr

The examples you gave of your time fields look like they are already in time format, i.e., that 4,55 = 4:55, etc.
Or aren't they? Does the 4 mean hours and the 55 minutes? Do they range up to 23:59? How does 10:00pm look in your database? How do you want them to appear?

-LB

 

[Ngolem]

So ...there is an &quot;Arr&quot; string field and a &quot;Dep&quot; string field and a time field common to both and is arrival or departure depending on which is not null....is that it??

Jim Broadbent

 

[cm1234]

Hi out there¨

First to lbass, thank you fot youre input to my task,
and then the formula works nearly 100%, its just the part with the damned number field that wont work.
you wrote:
The examples you gave of your time fields look like they are already in time format, i.e., that 4,55 = 4:55, etc.
Or aren't they?
No they are just plain numbers with dots
Does the 4 mean hours and the 55 minutes? Do they range up to 23:59?
No they go to 23.000,00 and nothing else
How does 10:00pm look in your database? How do you want them to appear?:
I would like them to apear just the way you showed in youre eksampel.... like 10:00pm a.s.o.
10:00pm in my db lokes like 1.000,00 and is a number field,
i desperatly need to get it converted to a time field, that would make my day.

and to Ngolem
also thanks for replying to my task.
No arr and dep are not depending on wich is not null , and
the &quot;time fields&quot; in my db are &quot;number fields instead!!!!

Best regards
cm1234

 

[lbass]

You seem to know what your numbers mean in terms of time, and maybe others might grasp what they mean, but it still is not obvious to me. Can you provide a series of numbers with their related time values so we can get a better idea of what the numbers represent? It would be good if you chose some AM and PM times with associated times. Then someone can probably help you with conversion.

-LB

 

[synapsevampire]

Presumably the numbers are in Euro format, where:

1.000,00 = 1,000.00 US

Note that your latest post conflicts with the earlier post of showing time as 10,10 versus 1.000,00

Posting exact example data and required output will net the best results, vagueness begets vague answers and wastes efforts, yours and theirs.

I think that lb is probably correct in that the number is a simple translation of the time in terms of HHMM, or something akin to that.

Try the following:

If len(totext({table.number},0,&quot;&quot) = 4 then
totext({table.number},0,&quot;&quot[1 to 2]+&quot;:&quot;+totext({table.number},0,&quot;&quot[3 to 4]
else
totext({table.number},0,&quot;&quot[1 to 1]+&quot;:&quot;+totext({table.number},0,&quot;&quot[2 to 3]

This assumes that it really is a number type field, and a few other quantum leaps of faith...

If not, do as LB suggests and post real example data in the real format that it's stored, and it's equivalent datetime for several different times.

Also describe the database in terms of these arrival and departure fields, are they the same field, or different fields?

-k

 

[cm1234]

Hi all

thank you for the advices and time you have spend on helping, hope that one day i can be to any help.

best regards
cm1234