1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2007 Date Filter

Discussion in 'Business Applications' started by ibanezsicko, Oct 28, 2010.

Thread Status:
Not open for further replies.
Advertisement
  1. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi,

    I have a form with a listbox on it which displays all the data in one of my tables. I also have a combo box which displays a list of dates (short date) from the table.

    I'm trying to query the table based the combobox to get all the records that came after that date.

    However, at the moment, when i enter a date, and the list is requeried, the data is not correct.

    For example, there is data for everyday (from 12/10/2010 to 26/10/2010) so if i select 12/10/2010 from the combo box, id expect to see records from 13/10 to 26/10.

    what i actually get is some records starting at 24/10.

    If i change the query so that it looks for records before the selected date, nothing is returned.


    Any suggestions as to whats going on?


    Cheers
    Lewis
     
  2. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Can you sjow a Screenshot of your Query with the Criteria that you are using?
     
  3. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi,

    there is also other combo boxes which filter other data in the table, these all work fine when the 'date1' filter is taken out.

    This code is taken from the Change event of the first combobox(filters by selected user).
    This is a brand new form and so is not perfect. as it is now, i select the filter criteria from the combo boxes and then select a user last (as the requery is done here at the moment).

    eventually, i will have if statements which use alternative queries to filter the results, but for now im just getting everything working using the 'filter user' combobox. unfortunatley, when i have added in the filter 'date1' to the query, i have hit problems.

    (the following query has been split onto seperate lines just now for easier reading. not like this in the code)
    ----------------------
    Private Sub filterUser_Change()

    Dim qry As String

    qry = "SELECT HISTORY.ID, HISTORY.User, HISTORY.Description, HISTORY.Object, HISTORY.[Object ID], HISTORY.[Object Description], HISTORY.[Object Quantity], HISTORY.Subject, HISTORY.[Record Date], HISTORY.[Record Time]
    FROM HISTORY
    WHERE (((HISTORY.User) Like '" & Me.filterUser & "*')
    AND ((HISTORY.Description) Like '" & Me.Description & "*')
    AND ((HISTORY.Object) Like '" & Me.Object & "*')
    AND ((HISTORY.Subject) Like '" & Me.Subject & "*')
    AND ((HISTORY.[Record Date]) > " & Me.date1 & "));"

    Me.List.RowSource = qry
    Me.List.Requery
    Me.List2 = ""

    End Sub
    ----------------------

    See anything wrong?

    Lewis
     
  4. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    When using a Date Variable in VBA SQL you need to add # symbols either side
    AND ((HISTORY.[Record Date]) > #" & Me.date1 & "#));"
     
  5. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    hi, just tried that.

    unfortunately, no luck. result is the same, if i select the earliest date and requery, i get no results. if i select the next earliest, it results in dates from 24/10 and over (when there are dates from 12/10 to 26/10)


    Lewis
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Have you tried doing this in a Query, i.e. not using VBA SQL?
    If you can get it to work in a query you can convert it to VBA SQL code.
    If you can post oe email me a database with the table in it I can probably do it for you.
     
  7. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    ok, ill email you the database.

    the form is called frmHistory

    When you open the database, just rightclick and close the login form that opens.
    when you open the frmHistory form you will get an error, its because there will not be anyone logged in, so click End and it will behave as normal.


    Do you need the db in a particular format?

    What is your email address?


    Lewis
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    I have Private mailed you my email address.
     
  9. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    thank you,

    I've just sent it to you now.






    Lewis
     
  10. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Lewis the problem is the Null search Fields on the form, especially the Subject one, the Subject entries only start 24/10/2010, so even using the Like operator the records start at the 24th.
    So I would suggest that you use VBA code that only builds on the SQL string if the filter field is not null.
     
  11. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    Hi,

    would you mind sending me your version of the database please?


    Thanks
    Lewis
     
  12. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    Do you want to create selective SQL VBA to avoid using the Null fields?
     
  13. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    yep, that was my intention.

    But anything that has the date working is something to build on.



    Lewis
     
  14. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    18,834
    OK, I have to go for a while, so I will send you something later.
     
  15. ibanezsicko

    ibanezsicko Thread Starter

    Joined:
    Mar 19, 2010
    Messages:
    87
    ok, no problem.

    Thank you very much.


    Lewis
     
  16. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/959054