What are the between statement parameters in a Query? Dropping the high-end records.

[dowlingm815]

I have a between statement within a query for a date range as follows:

between #6/1/2010# and 6/30/2010#

What are the between statement parameters rules? I am dropping the records on the high-end of the criteria, 6/30/2010. The data contains time stamp information, would this have an effect of the results and can how can it be corrected without changing the date range to the first day of the next month?

Would appreciate any assistance.

[OldBirdman]

6/30/2010# should have a '#' in front of it, and probably does in the actual code.
With no time, #6/30/2010# is #6/30/2010 12:00:00AM#, or midnight of the morning of June 30. Therefore, all times on June 30 after midnight, which are probably all of them, are not between the two dates. You need to either add one to the upper limit date, OR test DateValue([your date]) Between between #6/1/2010# and #6/30/2010#

[dowlingm815]

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