Solved

how to format  datetime for crystal reports selectionformula

Posted on 2003-12-11
10
4,011 Views
Last Modified: 2008-01-09
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 & "'"
       
0
Comment
Question by:tictocq
  • 5
10 Comments
 
LVL 10

Expert Comment

by:D_M_D
ID: 9921444
Try this...

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

--------
D_M_D
0
 
LVL 3

Expert Comment

by:Ficus
ID: 9922146
Any dates passed to Crystal Reports must be in Crystal Reports Date
format, Date(yyyy,mm,dd).
0
 
LVL 3

Expert Comment

by:Ficus
ID: 9922177
OurDate$ = format$(OurDate$,"yyyy,mm,dd")
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:tictocq
ID: 9929467
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
 
LVL 3

Accepted Solution

by:
Ficus earned 125 total points
ID: 9932951

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
 
LVL 3

Expert Comment

by:Ficus
ID: 9932976
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
 
LVL 3

Expert Comment

by:Ficus
ID: 10337125
Gimmie da Points :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I know it’s not a new topic to discuss and it has lots of online contents already available over the net. But Then I thought it would be useful to this site’s visitors and can have online repository on vim most commonly used commands. This post h…
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

26 Experts available now in Live!

Get 1:1 Help Now