Passing Date Parameters

[Joe Contreras]

Hi All,

I have a form that allows the user to enter a begin date and end date.
The text boxes are formatted to mm/dd/yyyy.

I'm passing these text values to a Crystal .SQLQuery, for example
SELECT a.field, b.field2 FROM table a,table b
WHERE a.id = b.id AND
a.date >= &" text1.value &" AND a.date <= &"text2.value" &

When the query evaluates the date values it returns a datatype error. I'm not sure how to pass these date values because I'm not sure if it's an issue with Oracle or Crystal reports formatting.

Thanks and my work e-mail contrerasjm@groton.pfizer.com

[Marc L'Ecuyer]

With Oracle, you have to use the TO_DATE function in your SQL command :

SELECT a.field, b.field2 FROM table a,table b
WHERE a.id = b.id AND
a.date >= TO_DATE(" & text1.value & ") AND a.date <= TO_DATE(" & text2.value & ")"

Marc

[Ravi Kiran]

If you are using the data value as literal (string) then you can try putting # before & after

This sql works for Jet DBs (may be for others also!)
sqlstr = "SELECT a.field, b.field2 FROM table a,table b WHERE a.id = b.id AND
a.date >= #" text1.value & "# AND a.date <= #" & text2.value &"#"

[Joe Contreras]

Hi, I've managed to solve the mystery. Since the date field in Oracle is Date/Time I need to format my VB variable to Date/Time not just Date, For example CRWConnect = "DSN = Restrac1; UID = crystal;PWD = diamond;DSQ= HIRE"
Me.MousePointer = vbHourglass
CRW7.ReportFileName = "D:\RESTRAC\CRW Reports\New Summary Requisition Activity.Rpt"
CRW7.Connect = CRWConnect
RecSelect1 = "{REQUISITION.Reqcode} = '" & frmReqActivity.cboRequisition.Text & "' "

CRW7.SelectionFormula = RecSelect1 + "AND" + "{REQUISITION.EnteredDate} in DateTime(" & Me.txtBDate.Text & ",00, 00, 00)" _
& " to DateTime(" & Me.txtEDate.Text & ",00, 00, 00)" Which yield the correct results. Thanks again for everyone's help