Filter through connection

[Macjnr]

Hi good people,

I have records a fCriteria table that I am connecting to using the code below.

How can I filter records in the table using based on Letter_Code field ?

E.g Filter all records where the code is LTRPLA found in Letter_Code field.

.OpenDataSource Name:=sDBPath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
Revert:=False, _
Format:=wdOpenFormatRTF, _
Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0; " & _
"User ID=Admin;" & _
"Password='';" & _
"Data Source=" & sDBPath & ";" & _
"Mode=Read;", _
SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _
Subtype:=wdMergeSubTypeAccess

Regards,
almacjnr

 

[boblarson]

First of all - are you connecting to another database outside of the one you have this code in?

 

[Macjnr]

Hi boblarson,

No I am not.

The table is within the same database. All I want to do is introduce the where clause and I am not doing well on that front.

the challenge is on

SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _

 

[boblarson]

Hi boblarson,

No I am not.

The table is within the same database. All I want to do is introduce the where clause and I am not doing well on that front.

the challenge is on

SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _

Okay, first of all if it is in the same database, you do not need, nor do you want to use, the connection string like that.

Second, I know you said you are trying to filter records in a table, but you should really just be using a query to pull the applicable records and in reality using a form to display them.

So the SQL statement could be

Select * FROM " & fCriteria

but we need to see how you are building fCriteria to see if you have everything required.

 

[Macjnr]

OK. I have a table letters and has over 10,000 records.

I have also created 10+ queries who's names go into the fCriteria variable hence the reason it works fine when a Select * from fCriteria query as is now is used.

Is there a way I can use one query where I pass the letter_code and the query filters depending on what code has been passed ?

E.g query name qryGlobal and the criteria is changes depending on what I pass as the code

Select * from qryGlobal WHERE Letter_Code is letter_Code

 

[boblarson]

I would just build the SQL string for the general query in code and you can substitute the WHERE clause as you need based on your selections.

 

[Macjnr]

That is exactly my problem.

Can you do me a sample query that would achieve that ? I have tried but in vain

Will greatly appreciate.

 

[boblarson]

What SQL do you have now for qryGlobal? What are some of the things you will be needing to filter on and what are the controls on the form?

 

[Macjnr]

See the SQL view of LTRPLA query. There are 10+ queries similar to this one with the differing in the WHERE clause. Other codes are LTRSDS, LTRFDS etc

SELECT Letters.Letter_Code, Letters.Date, Letters.Router_Account_Number, Letters.Account_Number, Letters.Credit_Card_Number, Letters.Title, Letters.First_Name, Letters.Last_Name, Letters.Address_Line_1, Letters.Address_Line_2, Letters.Address_Line_3, Letters.City, Letters.Pin_Code, Letters.Credit_Card_Limit, Letters.Original_Loan_Amount, Letters.[Card_Over-limit_Amount], Letters.Call_Centre_Numbers, Letters.Balance_Outstanding, Letters.Total_Overdue_Amount, Letters.Overdue_Fee_Amount, Letters.Overdue_Principal_Amount, Letters.Overdue_Interest_Amount, Letters.Minimum_Payment_Amount_for_Cards, Letters.Installment_Amount, Letters.Interest_Rate, Letters.Branch_Number, Letters.Authorised_Overdraft_Amount, Letters.Limit_Expiry_Date, Letters.[Collector's_Name], Letters.[Collector's_User_ID], Letters.Demand_Date, Letters.PO_Box, Letters.Guarantor_Name, Letters.Guarantor_Address, Letters.Guarantor_Town, Letters.Branch_Name
FROM Letters
WHERE (((Letters.Letter_Code)="LTRPLA"));

 

[boblarson]

So, if you have a form where the user can either type it in or select it (I prefer selections as you can ensure that they are valid), you could simply change the part here:

WHERE (((Letters.Letter_Code)="LTRPLA"));

to this:

WHERE (((Letters.Letter_Code)= [Forms]![YourFormNameHere]![YourControlNameHere]));

And that would make it generic actually inside that query.

 

[Macjnr]

boblarson,

the sql statement SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _ is in vba.

The code posted above does not work

 

[boblarson]

boblarson,

the sql statement SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _ is in vba.

The code posted above does not work

I meant to change the QUERY. Then you do not use that other code.

 

[Macjnr]

OK.

See what I have come up with but does not work either

Before

SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _


After
SQLStatement:="SELECT * FROM Letters WHERE (((Letter_Code)="LTRPLA")), _

 

[boblarson]

OK.

See what I have come up with but does not work either

Before

SQLStatement:="SELECT * FROM `" & fCriteria & "`", SQLStatement1:="", _


After
SQLStatement:="SELECT * FROM Letters WHERE (((Letter_Code)="LTRPLA")), _

Get rid of the idea of using SQLStatement:=

Get rid of the notion of using your code at all. It isn't needed.

Change the QUERY to what I showed, and let the user input the Letter code and just open the query.

 

[Macjnr]

OK. done that.

What can I do if I do not want the user to input the code.

The letters codes are predefined and all the use is required to do is click on a button and the code executes a number of other instructions.

Plus the letter codes are not easy to remember

 

[Macjnr]

OK. done that.

What can I do if I do not want the user to input the code.

The letter codes are predefined and all the use is required to do is click on a button and the code executes a number of other instructions.

Plus the letter codes are not easy to remember

 

[boblarson]

OK. done that.

What can I do if I do not want the user to input the code.

The letter codes are predefined and all the use is required to do is click on a button and the code executes a number of other instructions.

Plus the letter codes are not easy to remember

Have them available from a combo box or listbox. They will have to select it somehow, so that would be a way to do it.