Solved

how to format  datetime for crystal reports selectionformula

Posted on 2003-12-11
10
4,007 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Displaying an arrayList in a listView using the default adapter is rarely the best solution. To get full control of your display data, and to be able to refresh it after editing, requires the use of a custom adapter.
An introduction to basic programming syntax in Java by creating a simple program. Viewers can follow the tutorial as they create their first class in Java. Definitions and explanations about each element are given to help prepare viewers for future …
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

758 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

22 Experts available now in Live!

Get 1:1 Help Now