Same field - Multiple Criteria

[LarryB]

Hi all,

Is it possible to run a basic select query to pull records based on multiple strings entered by a user?

I have a query with field criteria - Like '*' & [Type In MMDBID:-] & '*'
This allows the user to input one MMDBID and the records are retrieved from the db.

I can also use the OR statement in the same field criteria "AB123" OR "BC123", and all records based on those values are pulled back.

But I cannot get the user to input multiple values and I tried amending the SQL string based on the OR criteria above

SQL statement (Like) is below - Any ideas?

SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) Like '*' & [Type In MMDBID:-] & '*') AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False));

Many thanks

LarryB

 

[CJ_London]

If you want the user to input a list you need to use IN ('AB121','AB122','AB123') and build the sql in vba. Note the user needs to supply the quotation marks and commas or you need to handle this in vba

i.e.

S=inputbox() ' user enters 'AB121','AB122','AB123'
if S<>"" then
Sqlstr="SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline]
FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID)
WHERE (((tblFund.MMDBID) IN (" & S & ") AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False))"

Then apply the sql string to your form or report

Alternatively you might find it easier to put the where part of the query into a form filter - you could leave the predetermined elements (Editing and Closed_Fund) in the form form rowsouce or wherever it is.

 

[LarryB]

Many thanks CJ

OK I have decided to use a public function containing the code that I hope to call at a later stage

I can confirm that the user input is updating the Select string with the multiple values. I'm not too worried about the inverts and comma's required

How do I now use the sqlstr variable to open tables / queries based on the sql string?

Ive messed around with docmd.runsql, and I'm looking at other forums that suggest using Currentdb.OpenRecordSet("sql string"). But no joy so far

I have also used docmd.openquery, but that is accessing predefined queries.

 

[CJ_London]

I would assign your VBA generated sql to the recordsource of a subform.

i.e. MySubformName.form.RecordSource=SqlStr

Where MySubformName is the name of your subform control (not the underlying form)

Alternatively as previously suggested, if your form already has the unfiltered recordset as a recordsource, just assign the criteria to the filter of the form

i.e. MySubformName.form.Filter=CriteriaSqlStr ' excluding the word WHERE
MySubformName.form.FilterOn=true ' to trigger the filtering

docmd.runsql(SqlStr) is for running action queries (Append, Insert, Delete) and is the equivalente to Currentdb.Execute(SqlStr) - the primary difference between the two is that currentdb.execute enables you to determine the records affected value (i.e. 10 records updated or 30 records deleted)

rst=Currentdb.OpenRecordSet("sql string") opens a recordset in memory which can the be used in VBA to do things e.g. for each record in rst send an email

Alternatively you can assign the recordset to a form - e.g. me.recordset=rst

 

[LarryB]

Many thanks CJ. Although a form is not what I need in this instance but I just remembered that I have used code before to generate quries and assign SQL statements using VBA

In my case here I want to retrieve data from multiple tables and export to excel.

Here is my code, plus your code below

Dim qdf As DAO.QueryDef
S = InputBox("Type ID's")

If S <> "" Then

Set qdf = CurrentDb.CreateQueryDef("GroupProceedsQuery", "SELECT tblFund.MMDBID, tblFund.[Investment Name], tblCodesLive.[IOE Code], tblCodesLive.[Uptix Code], tblFund.[Red Payment Deadline] FROM (tblFund INNER JOIN tblCodesLive ON tblFund.MMDBID = tblCodesLive.MMDBID) INNER JOIN tblContact ON (tblFund.MMDBID = tblContact.MMDBID) AND (tblCodesLive.MMDBID = tblContact.MMDBID) WHERE ((tblFund.MMDBID) IN (" & S & ") AND ((tblFund.Editing)=False) AND ((tblFund.Closed_Fund)=False))")

DoCmd.OutputTo acOutputQuery, "GroupProceedsQuery", "*.xlsx", "test.xlsx", True

End If

Thanks again CJ

 

[CJ_London]

so long as it does the job

 

[LarryB]

Hi all

I am adding additional code to help make the function fail safe.

This code requires the user to separate the input criteria with quotation marks and comma's, eg "id","id"

How can I check for the required characters in the string? Basically if they do not exist then exit function

I have added additonaly code to check for the query itself and if it exists, delete

For Each qdf In CurrentDb.QueryDefs
If qdf.Name = "GroupProceedsQuery" Then
CurrentDb.QueryDefs.Delete "GroupProceedsQuery"
Exit For
End If
Next

Cheers

LarryB

 

[LarryB]

OK using the instr() function I can find the comma

If InStr(1, S, ",") Then
MsgBox ("There's a comma in my box!")
End If

Struggling with finding the quotation marks "

I'm looking at ways of using ascii code chr(34)

 

[LarryB]

simples!

If InStr(1, S, chr(34)) Then
MsgBox ("There are quotation marks in my box")
End If

 

[CJ_London]

I would use the single quotation mark rather than the double when building sql.

Suggest you have your users just use comma's and use the following:

S=InputBox("Enter values, separated by commas (value1,value2,etc")
S="'" & replace(S,"'","','") & "'"

 

[LarryB]

Thanks again CJ for your input

I have never used replace() before and I'll put this to use.

Thanks again

Regards

LarryB