how to format datetime for crystal reports selectionformula

Hello All,

    I am trying to pass a datetime into a crystal reports selection formula but it doesnt like the format i am passing in. What is the best/easiest way to convert the datetime format of e.g. 12/12/2000 12:00:00 PM into something Cr will like?
Here is the code I am using:


Dim dt As DateTime
        dt = "12/12/2000 12:00:00 PM"
        CrystalReportViewer1.SelectionFormula = "{Nispo.RepId} = 47 AND {Nispo.BillReportDate} = '" & dt & "'"
       
tictocqAsked:
Who is Participating?
 
FicusCommented:

Here I am creating a string of data to set a date scope:
{atom.madestamp} >= DATE(2003,11,11) AND {atom.madestamp} <= DATE(2003,11,11)

'I create the above string by concocting the as follows:

   LStr_DateScope = LStr_DateScope & "{ventas.tourdate} >= DATE(" & _
   Format$(DTP_Start.Value, "yyyy,mm,dd") & ") AND {ventas.tourdate} <= DATE(" & _  
   Format$(DTP_stop.Value, "yyyy,mm,dd") & ")"

'set the record selection critera for report and refresh:
    ReportObject.RecordSelectionFormula = LStr_DateScope
0
 
D_M_DCommented:
Try this...

 CrystalReportViewer1.SelectionFormula = "{Nispo.RepId} = 47 AND {Nispo.BillReportDate} = '" & FormatDateTime(CDate(dt),0) & "'"

--------
D_M_D
0
 
FicusCommented:
Any dates passed to Crystal Reports must be in Crystal Reports Date
format, Date(yyyy,mm,dd).
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
FicusCommented:
OurDate$ = format$(OurDate$,"yyyy,mm,dd")
0
 
tictocqAuthor Commented:
Thanks to you both for responding. D_M_D, tried your suggestion and CR still did not like that format. Ficus could you give me an example in vb.net that includes date and time (datetime).

Thanks,
tictocq
0
 
FicusCommented:
OK.  That above example is using a date time picker for the date values.  Now you want a formula that passes a date and a time...  Lets say where the field in the criteria is = to userdatetime

UserDateTime as datetime

UserDateTime = format$(.field(1),"general date") 'I think general date is the time date format.

Try something like this i dont hace a place to test it right now.  You may need to put the time then the date or not use the DATE() statment in the query, just comparing.

LStr_Formula =  "{table.Xdatetime} = DATE("  & " " & Format$(UserDateTime),"short time") & " " & Format$(Format$(UserDateTime),"short date"), "yyyy,mm,dd")

ReportObject.RecordSelectionFormula = LStr_DateScope


PD. When you say CR don't like it.  What error do you get and where in the code?
0
 
FicusCommented:
Gimmie da Points :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.