Concatenate fields in Query or Formula? 3

[mavsman]

I have two tables I need to join and I need to join on a datetime field. One table has this field as CRDATTIM (which is create date time) and the other table has this broken down into three columns: Date column, time column, millisecond column. Do I concatenate those three columns in my query and then do a join or can this be done in a formula? Any help would be appreciated, thanks!

 

[Turkbear]

Hi,
Depending on your database type, a Command rather than using CR's pick and choose method would probably be more efficient.
I do not know the specific method of DateTime conversion for your database's language but in pseudocode the command would be something like:

[COLOR=red]Not Really Code, just an example[/color]
Select fields from table1,table2
where
table1.datetime = ToDateTime(table2.Date,Table2.Time,Table2.Millisecond)

To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[infinitizon]

From your report, click on the select expert >> click on CRDATTIM >> choose formular (on the buttom right not in the drop down) >> now try the formula

DateTimeValue(table.datefield+':'+table.timefield+':'+table.millisecondfield)

 

[lbass]

But CR does not parse time fields into milliseconds, as far as I know, so you might need to link just on datetime at the hh:mm:ss level.

-LB

 

[Turkbear]

Hi,
If the report needs the tables to be linked by the DateTime, a formula cannot be used within Crystal to accomplish the conversion of the 3 fields in the second table into a DateTime, as far as I know, unless a subreport is used and that does not appear to be what is desired.




To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[lbass]

I agree that the linking would have to occur in a command or by using a subreport to link.

-LB