Hi,
I have got a form where I have to textboxes for receiving the date.
When a butotn is pressed I want the query to run that has the following SQL
SELECT [Batches].* FROM [Batches]WHERE (([ProdDate] BETWEEN #01/11/2013# AND #08/11/2013#));
But it returns no results and i know there are results there as i picked those dates to test it.
The code is this
This code i used from a previous database i wrote last year but the dates and such were different.
Any ideas?
I have got a form where I have to textboxes for receiving the date.
When a butotn is pressed I want the query to run that has the following SQL
SELECT [Batches].* FROM [Batches]WHERE (([ProdDate] BETWEEN #01/11/2013# AND #08/11/2013#));
But it returns no results and i know there are results there as i picked those dates to test it.
The code is this
Code:
Private Sub BuildString()
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("Query1")
Dim strLine As String
Dim strSample As String
Dim strwhere As String
Dim strBetween As String
Dim DFrom As String
Dim DTo As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
strSQL = "SELECT [Batches].* "
strSQL = strSQL & "FROM [Batches]"
strwhere = "("
strBetween = ""
DFrom = Me.txtDF.Value
DTo = Me.txtDT.Value
'If Me.cboDateFrom <> "" Then strwhere = strwhere & "([proddate] BETWEEN " & Format(Me.cboDateFrom.Value, conJetDate) & " AND " & Format(Me.cboDateTo.Value, conJetDate) & ") AND "
If DFrom <> "" Then strwhere = strwhere & "([ProdDate] BETWEEN #" & DFrom & "# AND #" & DTo & "#) AND "
If Me.cboLine.ListIndex > -1 Then strwhere = strwhere & "([Line]='" & Me.cboLine & "') AND "
'If Me.cboSample.ListIndex > -1 Then strwhere = strwhere & "([Sample Nature?]='" & Me.cboSample & "') AND "
'If Me.cboYes.ListIndex > -1 Then strwhere = strwhere & "([Yes]='" & Me.cboYes & "') AND "
'If Me.cboProductType.ListIndex > -1 Then strwhere = strwhere & "([Product Type]='" & Me.cboProductType & "') AND "
If Me.cboProductCode.ListIndex > -1 Then strwhere = strwhere & "([Product Code]='" & Me.cboProductCode & "') AND "
If Me.cboTeam.ListIndex > -1 Then strwhere = strwhere & "([Team]='" & Me.cboTeam & "') AND "
If Me.cboShift.ListIndex > -1 Then strwhere = strwhere & "([Shift]='" & Me.cboShift & "') AND "
If Me.cboWeek.ListIndex > -1 Then strwhere = strwhere & "([Week]='" & Me.cboWeek & "') AND "
'If Me.cboBlendBatch.ListIndex > -1 Then strwhere = strwhere & "([Blend Code Batch/Mix]='" & Me.cboBlendBatch & "') AND "
'If Me.cboPerson.ListIndex > -1 Then strwhere = strwhere & "([Person taking sample]='" & Me.cboPerson & "') AND "
If strwhere <> "(" Then
strwhere = Left(strwhere, Len(strwhere) - 5) & ")"
strSQL = strSQL & "WHERE " & strwhere
End If
strSQL = strSQL & ";"
Debug.Print strSQL
qdf.SQL = strSQL
DoCmd.OpenReport "rptMain", acViewPreview
End Sub
This code i used from a previous database i wrote last year but the dates and such were different.
Any ideas?