Query Date range from form using VBA

Tubbzuk

Registered User.
Local time
Today, 01:38
Joined
Dec 21, 2012
Messages
37
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
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?
 
SELECT [Batches].* FROM [Batches]WHERE (([ProdDate] BETWEEN #01/11/2013# AND #08/11/2013#))
If these dates are 1st Nov and 8th Nov, then I think you have a date formatting problem. Try:
SELECT [Batches].* FROM [Batches]WHERE (([ProdDate] BETWEEN #11/01/2013# AND #11/08/2013#))
and view: http://allenbrowne.com/ser-36.html
 
is this by chance the everlasting US date vs Euro date format ???

In queries the dates must be in US format, MM/DD/YYYY, to me it looks like you are talking about the 1st nov to 8th nov

You even commented out the part to make sure it is in US format, WHY?
 
Hi,

thanks for the speedy replies.
remembered a similar issue i had when writing the last one and used the conjetdate thing but now this returns what first looked like just the records in taht date range but then also returned later dates.
the code now looks liek 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 DFrom <> "" Then strwhere = strwhere & "([proddate] BETWEEN " & Format(DFrom, conJetDate) & " AND " & Format(DTo, conJetDate) & ") 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

any ideas?
 
Code:
Dim DFrom As String
Dim DTo As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
..
DFrom = Me.txtDF.Value
DTo = Me.txtDT.Value
If DFrom <> "" Then strwhere = strwhere & "([proddate] BETWEEN " & Format(DFrom, conJetDate) & " AND " & Format(DTo, conJetDate) & ") AND "
...

any ideas?

Yeah I have some, can you see whats wrong with this setup?
 
Hi,

it does the same as the original code. It seems to set the first date in the range but then ignores the end date so i get everything from the first date regardless of what i put in for the second.
 
I am not giving you a fix, I am asking YOU if you can now see what is wrong??
 
no i cannot see what is wrong. this is why i posted on here.
the sql in the query that is output from the code is
SELECT Batches.*
FROM Batches
WHERE (([proddate] BETWEEN #11/04/2013# AND #11/06/2013#));

and i do not know what is wrong with it or why it doesnt return just records with those specific prod dates
 
think i have got it.
it wasnt the code or the sql that was wrong.
where i am imporitng it from excel i had the table format for the prod date field as text. changed it to short date and now it returns the correct results.

Thanks for your help ;-)
 
Dim DFrom As String
...
DFrom = Me.txtDF.Value
...
If DFrom <> "" Then strwhere = strwhere & "([proddate] BETWEEN " & Format(DFrom, conJetDate) & " AND " & Format(DTo, conJetDate) & ") AND "

Not quite or completely....

Your DFROM is defined as a String variable, that means the (implicit) conversion happens at the red line, Implicit conversion are BAD...

This would need to either be a date field OR have the format in it...
Dfrom = Format(DFrom, conJetDate)
 

Users who are viewing this thread

Back
Top Bottom