Query by date from form

[Scotter]

Hi, I've been trying to make a report that will show the deliverys to be made between 2 dates, or the deliverys to be made after a certian date, or the deliveries that were made before a certian date, based on criteria in a form.

At this point I have the report based on the query, and the dates bieng pulled from the form.

I can't seem to get the syntax right. In the query's WHERE statement I have <[forms]![Deliverys]![DelDate]. In the form, the field DelDate's format is set to short Date. That works, but if I leave it this way then i have to make 2 more queries and 2 more reports to be able to have the options im after.

I tried making an unbound textbox and if I hit before in the combo box and typed 08/01/07 in the DelDate field, then the textbox would would display <#08/01/07#. From there I called the field into the criteria of the query, but got an error of "This expression is typed incorrectly, or its too complex to be evaluated. etc...". But If i just type <#08/01/07# directly into the criteria of the query everything works great...

So, basically how can I get that information into the query?

[scolivas]

Scotter, can you supply the SQL code for your query? This sounds familiar - but I need more specific details.

Thanks,
Sophie

[Scotter]

Hi Sophie, I've been messing around with the query and the vb in the form, and just now I've gotten to the point where I have some hope. I went and deleted all the sql from the query so its basically just a blank saved query now. This is the VB code

Private Sub btnRun_Click()
 
Dim queryis As DAO.QueryDef
Dim db As DAO.Database
Dim sign As String
Dim del As Date
Dim pickup As Date
Dim strCriteria As String
Dim strSQL As String
 
 
Set db = CurrentDb()
Set queryis = db.QueryDefs("Delivery")
 
del = Me.DelDate
 
If Me.Combo8.Value = "before" Then
      sign = "< "
      strCriteria = strCriteria & sign & del
      strSQL = "SELECT * FROM Orders " & _
                    "WHERE [Delivery Date] = " & strCriteria & ";"
End If
 
queryis.SQL = strSQL
DoCmd.OpenQuery "Delivery"
 
End Sub
 

I only have the one if statement, so its only for orders before myDate. But the problem I'm having with this is when I run this I get an error that says

Syntax error(missing operator) in query expression '[Delivery Date] = <8/1/2007'

Yet If I type <8/1/07 in the criteria of the query under delivery date everything works fine. What am I doing wrong?

Thanks for the help.

[Scotter]

Ok, I figured it out. I got it to work by changing the variable type of the date to a string and adding #'s, and i had to change

strSQL = "SELECT * FROM Orders " & _
                    "WHERE [Delivery Date] = " & strCriteria & ";"
 

to

srtsql = "SELECT * FROM Orders " & _
             "WHERE [Delivery Date] " & strCriteria & ";"
 

the = sign was messing things up. Here is the code that is working for me now.

Private Sub btnRun_Click()
 
Dim sign As String
Dim Date1 As String
Dim date2 As String
Dim strCriteria As String
Dim strSQL As String
Dim queryis As DAO.QueryDef
Dim db As DAO.Database
 
Set db = CurrentDb()
Set queryis = db.QueryDefs("Delivery")
 
 
If Me.Combo8.Value = "before" Then
    Date1 = Me.txt1
    Date1 = "#" & Date1 & "#"
    sign = "< "
    strCriteria = strCriteria & sign & Date1
    strSQL = "SELECT * FROM Orders " & _
             "WHERE [Delivery Date] " & strCriteria & ";"
End If
If Me.Combo8.Value = "after" Then
    Date1 = Me.txt1
    Date1 = "#" & Date1 & "#"
    sign = ">"
    strCriteria = strCriteria & sign & Date1
    strSQL = "SELECT * FROM Orders " & _
             "WHERE [Delivery Date] " & strCriteria & ";"
End If
If Me.Combo8.Value = "between" Then
    Date1 = Me.txt1
    Date1 = "#" & Date1 & "#"
    sign = "Between "
    date2 = Me.txt2
    date2 = "#" & date2 & "#"
    strCriteria = strCriteria & sign & Date1 & " AND " & date2
    strSQL = "SELECT * FROM Orders " & _
             "WHERE [Delivery Date] " & strCriteria & ";"
End If
 
 
queryis.SQL = strSQL
DoCmd.OpenQuery "Delivery"
End Sub
 

Also I made the second textbox disabled so you couldnt use it unless the combobox was set to between.

Private Sub Combo8_AfterUpdate()
 
If Me.Combo8.Value = "before" Then
Me.txt2.Enabled = False
End If
 
If Me.Combo8.Value = "after" Then
Me.txt2.Enabled = False
End If
 
If Me.Combo8.Value = "between" Then
Me.txt2.Enabled = "true"
End If
 
If IsNull(Combo8) = True Then
Me.txt2.Enabled = "false"
End If
 
End Sub