Thank you for your response. The date was converted correctly with the following statement.
-
Creation Date2: DateValue([Creation Date])
-
When the date range is selected as a static value as:
-
Between #6/1/2010# And #6/30/2010#
-
The query selects the proper date ranges. However, when a dynamic value is selected through a form value, no results are yielded.
-
Between [Forms]![F_Waiver_Yr]![txb_date_start] And [Forms]![F_Waiver_Yr]![txb_date_end]
-
Since it didn't execute properly in the query i used VBA to run a SQL statement, where the data gets processed correctly. the code is as follows:
-
Private Sub Extract_POScreenAmt()
-
-
On Error GoTo Err_Hndlr
-
-
'**********************************************
-
Dim dbs As Database
-
Dim strSQL As String
-
Dim strQueryName As String
-
Dim qryDef As QueryDef
-
-
'set variable values
-
Set dbs = CurrentDb
-
strQueryName = "sql_Extract_POScreenAmt"
-
-
'Delete old query first - we want fresh data!
-
dbs.QueryDefs.Delete strQueryName
-
-
'Notice below how we inserted the variable as a parameter value - Visual Basic will evaluate strMonth and insert the value for us.
-
-
-
strSQL = "SELECT POCompletedScreen.[PO #], POCompletedScreen.[PO Total], DateValue([Creation Date]) AS [Creation Date2] " & _
-
"FROM POCompletedScreen " & _
-
"GROUP BY " & _
-
"POCompletedScreen.[PO #], " & _
-
"POCompletedScreen.[PO Total], " & _
-
"DateValue([Creation Date]) " & _
-
"HAVING DateValue([Creation Date]) " & _
-
"Between #" & [Forms]![F_Waiver_Yr]![txb_date_start] & _
-
"# And #" & [Forms]![F_Waiver_Yr]![txb_date_end] & "# " & _
-
"ORDER BY POCompletedScreen.[PO #];"
-
-
-
' "HAVING PRApprovalHistory.PR_Date " & _
-
' "Between #" & [Forms]!F_MktPlace_DataRun![txb_MktPlace_Start_Date] & _
-
' "# And #" & [Forms]!F_MktPlace_DataRun![txb_MktPlace_End_Date] & "# " &
-
-
-
-
'Create query definition
-
Set qryDef = dbs.CreateQueryDef(strQueryName, strSQL)
-
-
-
-
Extract_POScreenAmt_Exit:
-
Exit Sub
-
-
-
Err_Hndlr:
-
MsgBox "[" & Err.Number & "]: " & Err.Description, vbInformation, "Extract_POScreenAmt()"
-
End Sub
-
-