Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628

    Unanswered: Filter help required

    I am trying to filter a form based on two values, early and late.

    What i need is for all records < early's value to be filtered out and all records > late value to be filtered out.

    Could anyone please help me to figure out the correct syntax for this?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Are you saying that you want to see all records that are either early or late
    or
    Do you want to see all records that are not early and are not late?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    select columnlist...... from atable
    where dateColumn between earlydate and latedate

    you may need to do soem tidyingup and / or include the # symbol to encapsulate the date

  4. #4
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    I basically wish to see everything that is between the Early and Late Variables.

    To explain further:

    Early = Forms!subMainMenu_date!EarliestDateBox.Value (is a text box using dd-mmm-yy format)
    Late = Forms!subMainMenu_date!LatestDateBox.Value (is a text box using dd-mmm-yy format)

    I am trying to filter a form based on a query showing all records to ignore everything older than the early date and also ignore everything newer than the Late date.

  5. #5
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Does this look correct to you guys? I'd rather get it right here before i messup my database with it if you know what i mean.

    Code:
    Dim Early As Date
    Dim Late As Date
    Dim strSQL As String
    
    Early = Forms!Main_Menu!subMainMenu_date!EaliestDateBox.Value
    Late = Forms!Main_Menu!subMainMenu_date!LatestDateBox.Value
    
    strSQL = "SELECT AllNCR.NCR_nr, AllNCR.date_entry, AllNCR.Description, AllNCR.Status, AllNCR.Prod_name, AllNCR.Prod_cat, AllNCR.Client" & _
    "FROM AllNCR" & _
    "WHERE AllNCR.date_entry='" & < & [Early] & "  & _
                     "AND date_entry='" & > & [Late] & ";"
    End Sub
    Please correct me if i've made any mistakes. Im pretty new to SQL, which is kind of obvious.

  6. #6
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Wrongo ...

    SELECT blah FROM blah WHERE (blah BETWEEN #DateLiteralHere# AND #DateLiteralHere#);
    Back to Access ... ADO is not the way to go for speed ...

  7. #7
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    So there's no way i can use variables to provide the #DateLiteralHere# elements?

    To me i would have thought i could get away with:

    WHERE (date_entry BETWEEN #Early# AND #Late#);

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    yes you can use variables to form your SQL where clause

    select columnlist...... from atable
    where dateColumn between " & earlydate & " and " & latedate

    you may need to do some tidying up and / or include the # symbol to encapsulate the date

  9. #9
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    thanks healdem.

    Now for a really oibvious question, that i wouldn't ask if i was actually awake:

    Because this statement is run from a seperate form, i take it that i use the command:

    Forms!Formname.RecordSoure = strSQL
    Forms!Formname.Requery (this is because the form is already open)

  10. #10
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    forget the last question, i got it. Man Coffee is wonderful stuff.

    Right, im getting an error now stating that it cant find the form of which i want to change the recordset, here's the code i've used:

    Code:
    Private Sub Label20_Click()
    
    Dim Early As String
    Dim Late As String
    Dim strSQL As String
    
    Early = Me![EarliestDateBox].Value
    Late = Me![LatestDateBox].Value
    
    strSQL = "SELECT AllNCR.NCR_nr, AllNCR.date_entry, AllNCR.Description, AllNCR.Status, AllNCR.Prod_Name, AllNCR.Prod_cat, AllNCR.Client " & _
             "FROM AllNCR" & _
             "WHERE date_entry BETWEEN " & Early & " AND " & Late & ";"
    
    Forms!NCRsByUserDate.RecordSource = strSQL '**Error occurs here**
    Forms!NCRsByUserDate.Rquery
    
    End Sub

    Any suggestions?

  11. #11
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Quote Originally Posted by cruickshanks
    I basically wish to see everything that is between the Early and Late Variables.


    I am trying to filter a form based on a query showing all records to ignore everything older than the early date and also ignore everything newer than the Late date.
    Hi cruickshanks.....
    Not understanding Early and Late dates. Is EarlyDate like the First date and Late date the Last date? How do you determine Early and Late dates? It's a bit early/late here for me too.....but no coffee What I was getting at is to create a simple DialogForm with 2 TextBoxes for you to Input the dates you wish to find data between....a date range kinda thingy. I have one of those if you want to see it.

    have a nice one all, bout to go count some sheep or something.......
    BUD

  12. #12
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    Quote Originally Posted by Bud
    Hi cruickshanks.....
    Not understanding Early and Late dates. Is EarlyDate like the First date and Late date the Last date? How do you determine Early and Late dates? It's a bit early/late here for me too.....but no coffee What I was getting at is to create a simple DialogForm with 2 TextBoxes for you to Input the dates you wish to find data between....a date range kinda thingy. I have one of those if you want to see it.

    have a nice one all, bout to go count some sheep or something.......
    BUD
    It seemed to me something that should have been straightforward, but i seem to be sticking with this.

    I've attached a screenshot of my form. Now, the way i have got it set up is the Uppermost menu bar is a subform, the left most menu bar (the one with the date textboxes) is a subform (which subform depends on what the user is trying to do), and the table is a subform, which im trying to filter.

    To be honest, it could be the date format that's causing a problem, which wouldn't be too bad a thing to change if need be.
    Attached Thumbnails Attached Thumbnails Image1.jpg  

  13. #13
    Join Date
    Mar 2005
    Posts
    14

    Wink

    Hi

    This code works for me:

    Dim Text3
    Dim Text4
    Text3 = Format(Text0, "mm/dd/yy")
    Text4 = Format(Text2, "mm/dd/yy")

    Forms!form4.RecordSource = "SELECT Table1.* FROM Table4 WHERE Date1>=#" & Text0 & "# AND Date2<=#" & Text2 & "#"
    Forms!form4.Requery

    For some reason Access insists on using American date formats in code.

    I hope it helps.

  14. #14
    Join Date
    Jul 2004
    Location
    Inverurie
    Posts
    628
    sounds about right for a Microsoft product.

    thanks for that Baldy, i'll change the date's over and try it the way you've suggested.

  15. #15
    Join Date
    Mar 2005
    Posts
    14
    OK. I noticed a reference to table1 in my code. It should be table4.

    I was only using one table, honestly. (mmmm... coffee).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •