Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

how to format  datetime for crystal reports selectionformula

Posted on 2003-12-11
10
Medium Priority
?
4,070 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 500 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
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 …
Simple Linear Regression

670 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