Consulting

Results 1 to 6 of 6

Thread: Multiple filter selection

  1. #1
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location

    Multiple filter selection

    Hello everyone,


    I'm just starting to learn VBA Excel/Access and some SQL. I tried during hours to find a solution to my problem online. So far I didn't find a solution, so I'm turning to this forum to try and find some help. I'm probably doing something fundamentally wrong in my code because it just keep not working.


    I'm trying to import a txt file, using this code.
    Public sub IMPORT ()
    Dim DATE1 as String
    
    
    DATE1 = InputBox("Type in today's date using YYYYMMDD format")
    
    
    DoCmd.TransferText transferType:=acImportDelim, TableName:="TREASURY_" & DATE1, FileName:="C:\Documents\AccessTEST\TREASURY_" & DATE1 & ".txt", hasfieldnames:=True

    This works well but then I try to apply some filters and I just don't manage to have multiples filters at the same time.
    I tried multiple things, first using an SQL query with the following code :
    Dim SQLquery1 as String
    SQLquery1 = "SELECT TREASURY_" & DATE1 & ".* " & _
    "FROM "SELECT TREASURY_" & DATE1 & " & _
    "WHERE ((("TREASURY_" & DATE1 & ".PORTFOLIO)='Portfolio_2' Or ("TREASURY_" & DATE1 & ".PORTFOLIO)='Portfolio_5') AND (("TREASURY_" & DATE1 & ".TRANSACTION_DATE) >" & DATE1 & "));"
    
    
    DoCmd.runSQL SQLquery1

    This does not work where as if I create a query and paste this code above (with DATE1 = 20190930 for example), it works.


    Then I tried using the DoCmd.ApplyFilter with the following code :
    DoCmd.Applyfilter , "[PORTFOLIO] = 'Portfolio_2'"
    DoCmd.Applyfilter , "[TRANSACTION_DATE]>" & DATE1

    This code works but it does not add up the conditions, it will first filter on Portfolio_2 and then on Transactions dates > today's date. I did not manage to filter on Portfolio_2 and Portfolio_5 aswell.


    If anyone has an idea on why the SQL query is wrong or how to apply multiple filters using DoCmd.Applyfilter I'd gladly use the help.


    Thanks for the help and sorry if my english is not very good.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Can you clarify.
    The Import of the txt file works OK?
    Then you try and apply SQL filters to the data and that doesn't work?
    If it doesn't work what actually happens?
    When using the docmd filters method, are you applying that to a form?
    Which method would you prefer to use, a query based one or a form based one?

    ps sql dates have to be in USA format ie MM/DD/YYYY this can be achieved using the date string surounded by # symbols.

  3. #3
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location
    Quote Originally Posted by OBP View Post
    Can you clarify.
    The Import of the txt file works OK?
    Then you try and apply SQL filters to the data and that doesn't work?
    If it doesn't work what actually happens?
    When using the docmd filters method, are you applying that to a form?
    Which method would you prefer to use, a query based one or a form based one?

    ps sql dates have to be in USA format ie MM/DD/YYYY this can be achieved using the date string surounded by # symbols.
    Thanks for the answer.
    The import of the txt file works fine.
    Then I try and apply SQL filters to the data and that doesn't work.
    Here is how my code looks like :
    Public sub IMPORT ()
    Dim DATE1 as String
    Dim SQLquery1 as String
    
    
    DATE1 = InputBox("Type in today's date using YYYYMMDD format")
    
    
    
    
    DoCmd.TransferText transferType:=acImportDelim, TableName:="TREASURY_" & DATE1, FileName:="C:\Documents\AccessTEST\TREASURY_" & DATE1 & ".txt", hasfieldnames:=True
    
    
    SQLquery1 = "SELECT TREASURY_" & DATE1 & ".* " & _
    "FROM "SELECT TREASURY_" & DATE1 & " & _
    "WHERE ((("TREASURY_" & DATE1 & ".PORTFOLIO)='Portfolio_2' Or ("TREASURY_" & DATE1 & ".PORTFOLIO)='Portfolio_5') AND (("TREASURY_" & DATE1 & ".TRANSACTION_DATE) >" & DATE1 & "));"
    
    
    
    
    DoCmd.runSQL SQLquery1
    
    
    End sub
    I keep running into an error : "Run-time error '2342': A RunSQL action requires an argument consisting of an SQL statement."
    I don't understand why it is not an SQL argument when it works using a SQL query directly.
    I'd prefer to use an SQL approch but if it's easier to do it using the DoCmd.ApplyFilter or another DoCmd, I'm fine with it.
    After filtering my file, I copy paste it into an excel. I don't directly use excel because my file has more lines than excel can accept.
    My goal is to automatize a process that I'm doing manually so far.

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Try this instead

    SQLquery1 = """SELECT TREASURY_"" & DATE1 & "".* "" & _ " & vbCrLf & _
    """FROM ""SELECT TREASURY_"" & DATE1 & "" & _ " & vbCrLf & _
    """WHERE (((""TREASURY_"" & DATE1 & "".PORTFOLIO)='Portfolio_2' Or (""TREASURY_"" & DATE1 & "".PORTFOLIO)='Portfolio_5') AND ((""TREASURY_"" & DATE1 & "".TRANSACTION_DATE) >"" & DATE1 & ""));"""



    If this doesn't work you can also work directly with the Query that does work by making the Date input a field on a form and use the form field as a query Criteria row.
    This is my preferred method.
    In the Criteria Row of the Transaction Date you enter

    Forms![Formname]![Fieldname]

    where Formname is the actual name of the form and Fieldname is the name of the date field on that form.

    If you really want to stay with a VBA version I can supply a program that will convert a working query SQL to VBA SQL.

  5. #5
    VBAX Newbie
    Joined
    Sep 2019
    Posts
    3
    Location
    I tried with the SQL code you supplied, and I run into this error :"Run-time error '3129': Invalid SQL stqtement, expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'".

    I am going to try with the method you suggested.

    I am interested in using such program if you don't mind sharing it.

    Thank you!

  6. #6
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Here is the conversion program, open the form, copy the SQL from the query that works and paste it in to the form and click the command button.

    One problem that I foresee is that you are using the Date as an input and The query may have trouble interpreting it.
    Attached Files Attached Files

Tags for this Thread

Posting Permissions

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