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 & "'"
Who is Participating?

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
Try this...

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

Any dates passed to Crystal Reports must be in Crystal Reports Date
format, Date(yyyy,mm,dd).
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.

OurDate$ = format$(OurDate$,"yyyy,mm,dd")
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 that includes date and time (datetime).

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?
Gimmie da Points :)
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.