Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Query by date from form

Member
  
Join Date: Aug 2007
Posts: 80
#1: Aug 23 '07
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?

Member
  
Join Date: Oct 2006
Posts: 55
#2: Aug 23 '07

re: Query by date from form

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

Thanks,
Sophie
Member
  
Join Date: Aug 2007
Posts: 80
#3: Aug 23 '07

re: Query by date from form

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRun_Click()
  2.  
  3. Dim queryis As DAO.QueryDef
  4. Dim db As DAO.Database
  5. Dim sign As String
  6. Dim del As Date
  7. Dim pickup As Date
  8. Dim strCriteria As String
  9. Dim strSQL As String
  10.  
  11.  
  12. Set db = CurrentDb()
  13. Set queryis = db.QueryDefs("Delivery")
  14.  
  15. del = Me.DelDate
  16.  
  17. If Me.Combo8.Value = "before" Then
  18.       sign = "< "
  19.       strCriteria = strCriteria & sign & del
  20.       strSQL = "SELECT * FROM Orders " & _
  21.                     "WHERE [Delivery Date] = " & strCriteria & ";"
  22. End If
  23.  
  24. queryis.SQL = strSQL
  25. DoCmd.OpenQuery "Delivery"
  26.  
  27. End Sub
  28.  
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.
Member
  
Join Date: Aug 2007
Posts: 80
#4: Aug 23 '07

re: Query by date from form

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
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT * FROM Orders " & _
  2.                     "WHERE [Delivery Date] = " & strCriteria & ";"
  3.  
to
Expand|Select|Wrap|Line Numbers
  1. srtsql = "SELECT * FROM Orders " & _
  2.              "WHERE [Delivery Date] " & strCriteria & ";"
  3.  
the = sign was messing things up. Here is the code that is working for me now.
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnRun_Click()
  2.  
  3. Dim sign As String
  4. Dim Date1 As String
  5. Dim date2 As String
  6. Dim strCriteria As String
  7. Dim strSQL As String
  8. Dim queryis As DAO.QueryDef
  9. Dim db As DAO.Database
  10.  
  11. Set db = CurrentDb()
  12. Set queryis = db.QueryDefs("Delivery")
  13.  
  14.  
  15. If Me.Combo8.Value = "before" Then
  16.     Date1 = Me.txt1
  17.     Date1 = "#" & Date1 & "#"
  18.     sign = "< "
  19.     strCriteria = strCriteria & sign & Date1
  20.     strSQL = "SELECT * FROM Orders " & _
  21.              "WHERE [Delivery Date] " & strCriteria & ";"
  22. End If
  23. If Me.Combo8.Value = "after" Then
  24.     Date1 = Me.txt1
  25.     Date1 = "#" & Date1 & "#"
  26.     sign = ">"
  27.     strCriteria = strCriteria & sign & Date1
  28.     strSQL = "SELECT * FROM Orders " & _
  29.              "WHERE [Delivery Date] " & strCriteria & ";"
  30. End If
  31. If Me.Combo8.Value = "between" Then
  32.     Date1 = Me.txt1
  33.     Date1 = "#" & Date1 & "#"
  34.     sign = "Between "
  35.     date2 = Me.txt2
  36.     date2 = "#" & date2 & "#"
  37.     strCriteria = strCriteria & sign & Date1 & " AND " & date2
  38.     strSQL = "SELECT * FROM Orders " & _
  39.              "WHERE [Delivery Date] " & strCriteria & ";"
  40. End If
  41.  
  42.  
  43. queryis.SQL = strSQL
  44. DoCmd.OpenQuery "Delivery"
  45. End Sub
  46.  
Also I made the second textbox disabled so you couldnt use it unless the combobox was set to between.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Combo8_AfterUpdate()
  2.  
  3. If Me.Combo8.Value = "before" Then
  4. Me.txt2.Enabled = False
  5. End If
  6.  
  7. If Me.Combo8.Value = "after" Then
  8. Me.txt2.Enabled = False
  9. End If
  10.  
  11. If Me.Combo8.Value = "between" Then
  12. Me.txt2.Enabled = "true"
  13. End If
  14.  
  15. If IsNull(Combo8) = True Then
  16. Me.txt2.Enabled = "false"
  17. End If
  18.  
  19. End Sub
  20.  
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,510 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved