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.

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 :

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 :

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.

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.

Quote:

---

Originally Posted by OBP

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 :

Code:

---

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.

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.

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!

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.