Solved

How to pass multiple criteria in vba to a query and have it export in Excel

Posted on 2011-09-19
24
360 Views
Last Modified: 2012-05-12
Good morning,
I'm trying to figure out since 3 days now, how in VBA can I take what user enter in a form and have it pass into criteria of the query, all of them, and have the result export to excel.
I have join a image of the form which user will fill and it the button Excel Export.

Please help me. Thanks
Nancy Therrien Criteria need for my query
0
Comment
Question by:nancytherrien
  • 12
  • 9
  • 2
  • +1
24 Comments
 

Author Comment

by:nancytherrien
ID: 36560988
I have forgot to say that query is not a part of that form, it is a completly separate query.
Thanks
0
 
LVL 29

Expert Comment

by:Randy Downs
ID: 36560999
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36561008
post the query you use for one criteria...
so we can find a way passing multiple criteria...

for example

select * from myTable where col='myCriteria'
-->
select * from myTable where col in ('myCriteria1','myCriteria2','myCriteria3')

0
 

Author Comment

by:nancytherrien
ID: 36561043
I want to use that which is my form criteria that I now is working when it is question of printing directly and have it open the query.

Dim ctl As Control
Dim sfilter As String
Dim stdocName As String
           
           
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
             sfilter = "[NO_JOB]=" & ctl & ""
              ' [ICI c'est le nom du champs qu'on veut comme critère
             
              'ci-dessous on vérifie si le champ est libre, sinon on exécute la commande
             
            If Len(ctl) > 1 Then
           
           


            'stdocName = "RequeteHistoriqueAchat"
            'DoCmd.OpenQuery stdocName
       
            End If
           
         End If
       
    Next
    DoCmd.Close
    DoCmd.OpenForm "menu"

End Sub
Thanks
Nancy
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36561056
You will need to build the whole sql string for the query in code and then save it to a desgnated query which you can then export.

Essentially the sql sring you will build is something like..

"select * from table where somefield in [value1, value2, value3 etc]"

So the tricky bit is getting all the va;ues input by the users into the [ ].

I don't see that you have any choice but to test every control for a value and then adjust the list if a value is present.
It would make this easier if you have named the controls something like..
field1, field2, field3...........etc to field23, field24

Can you confirm the names you have used.
0
 

Author Comment

by:nancytherrien
ID: 36561072
The name on my form is texte1,texte2,texte3............

This is the query i use:  I want that filter by tblCommandeListe.Ordre

SELECT tblCommandeListe.*, tblCommande.*, IIf(IsNull([SumOfRecep-Qte1]),0,[SumOfRecep-Qte1]) AS Expr2, [Qte]-[Expr2] AS Expr1, tblCommande.N°Commande, tblCommandeListe.[Enlever liste à commander], tblFournisseur.[Fournisseur-Nom], tblCommandeListe.[Qte req], tblCommandeListe.[Date de livraison], IsNull([Date de livraison]) AS Expr3, tblCommandeListe.Ordre
FROM MaxNoPourFiltre INNER JOIN (((tblCommandeListe LEFT JOIN tblCommande ON tblCommandeListe.N°Commande = tblCommande.N°Commande) LEFT JOIN [Reception sum] ON tblCommandeListe.[No item] = [Reception sum].[Recep-No item]) LEFT JOIN tblFournisseur ON tblCommande.fournisseur_id = tblFournisseur.Fournisseur_id) ON MaxNoPourFiltre.[No] = tblCommandeListe.Filtrage
WHERE (((tblCommandeListe.Ordre) Like [Quel Numero]));
0
 
LVL 77

Expert Comment

by:peter57r
ID: 36561141
Here is the structure you should use .
You will need to fix it for your own fieldname and sql string.
This version also assumes that the values being entered are numbers.


' main part of query...
Dim strsql
strsql = " Select ..etc....where fieldname IN "

' build the list
Dim x as integer
dim result as string
result = "["
for x = 1 to 24
if not isnull(me.controls("texte" & x)) then
result = result & me.me.controls("texte" & x) & ","
Next x

' complete the string - remove last comma and add ]
result = left(result, len(result)-1) & "]"

' add the list to the main query

strsql = strsql & result
currentdb.querydefs("exportqueryname").SQL = strsql
0
 

Author Comment

by:nancytherrien
ID: 36561532
If I use your solution, will I still need my seperate form with this code in it ?
Dim ctl As Control
Dim sfilter As String
Dim stdocName As String
           
           
    For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
             sfilter = "[NO_JOB]=" & ctl & ""
              ' [ICI c'est le nom du champs qu'on veut comme critère
             
              'ci-dessous on vérifie si le champ est libre, sinon on exécute la commande
             
            If Len(ctl) > 1 Then
           
           


            'stdocName = "RequeteHistoriqueAchat"
            'DoCmd.OpenQuery stdocName
       
            End If
           
         End If
       
    Next
    DoCmd.Close
    DoCmd.OpenForm "menu"

End Sub


I'm not sure I understand well.
0
 

Author Comment

by:nancytherrien
ID: 36562001
I have took a part of your suggestion and I have done this :

Dim ctl As Control
Dim sfilter As String
Dim stdocName As String
Dim strsql
Dim x As Integer
Dim result As String
   For Each ctl In Me.Controls
        If ctl.ControlType = acTextBox Then
             sfilter = "[NO_JOB]=" & ctl & ""
   If Len(ctl) > 1 Then
            result = result & "," & sfilter
                End If
             End If
       Next
   
    DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, , , , result

this open the report but with no filter in it.  The result variable contain all the number I want, but that does'nt filter at all
    DoCmd.Close
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36562671
your logic is wrong... check it...

maybe you want this:

is [NO_JOB] a numeric value or text? if numeric, this code should work... if not we need to modify a bit...
Dim ctl As Control
Dim sfilter As String
Dim stdocName As String
Dim strsql
Dim x As Integer
Dim result As String

For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
    if sfilter = "" then 
      sfilter = "[NO_JOB]=" & ctl & "" 
    else
      sfilter = sfilter & " or [NO_JOB]=" & ctl & "" 
    end if
  End If
Next
    
DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, , , , sfilter
DoCmd.Close

Open in new window

0
 

Author Comment

by:nancytherrien
ID: 36566498
Hi HainKurt,  I have tried you code and the report won't open, always thinking of openig, wait about 10 minutes before I cancelled it.

The No_Job is string so do the sfilter, do I have to changed anything else ?

Thanks
Nancy
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36566930
then Line 10-14 should be

    if sfilter = "" then
      sfilter = "[NO_JOB]='" & ctl & "'"
    else
      sfilter = sfilter & " or [NO_JOB]='" & ctl & "'"
    end if
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:nancytherrien
ID: 36567239
ok ,  I have tried with your change but my report will open sometime empty and sometime full of stock, not only with my filter.  That is weard.  It also take a long time for it to open.

I have add a mgsbox sfilter so it is showing me on screen what are the criteria and that seems to be good.
Why report won't take those criteria is still the question.
Thanks
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36568228
maybe this is the issue

DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, , , , sfilter

expression.OpenReport(ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

http://msdn.microsoft.com/en-us/library/bb238032(v=office.12).aspx

try

DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, ,sfilter
0
 

Author Comment

by:nancytherrien
ID: 36568716
no still not working, bring back all the result and don't care of the filter.
i'm beginning to be really depress on that one............
Thanks a lot for your help .  It is really appreciated.
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36569226
what happens if you hard code filter and call

DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, , , , "[NO_JOB]='a sample job'"
0
 

Author Comment

by:nancytherrien
ID: 36569255
not much change, did return the same as if it was not filter at all.
I have ask for "8644" or 8644
Do you think that creating a table from the query and filter from there after may help ?

Thanks
result.jpg
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36570812
post the report query...
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36570818
ok, create a query for your report, save it and use that query in that report and pass this filter to report, same way...
maybe you have a very complicated sql in your report...

also in another query, use this

select * from rptQuery

and this one

select * from rptQuery where [NO_JOB]='8644'

and see what you get... if query & filtered quewry is ok, and you pass your filter in

[NO_JOB]='8644' or [NO_JOB]='8640' or [NO_JOB]='3764'

then it should filter...
0
 

Author Comment

by:nancytherrien
ID: 36575075
ok will try this this afternoon. Thanks, I will keep you posted.
0
 

Author Comment

by:nancytherrien
ID: 36575883
ok,
I have made a query from my complex query.  Now the query is more simple.  If I put criteria in the query result is good.    SELECT RequeteHistoriqueAchat.*, RequeteHistoriqueAchat.Ordre, *
FROM RequeteHistoriqueAchat
WHERE (((RequeteHistoriqueAchat.Ordre) Like "*8644*"));  (That's working).

In the report, I have put the name of my new query as Source.

If I go in VBA and try to filter from a form, it still won't work.
Here my code.

Dim ctl As Control
Dim sfilter As String
Dim stDocName As String
Dim strsql
Dim x As Integer
Dim result As String

For Each ctl In Me.Controls
  If ctl.ControlType = acTextBox Then
      If sfilter = "" Then
      sfilter = "[Ordre]='" & ctl & "'"
    Else
      sfilter = sfilter & " or [Ordre]='" & ctl & "'"
    End If
 ' MsgBox sfilter
  End If
Next
'MsgBox sfilter

'sfilter = "8644*"    I have tried to force the value I want, this way and also "*8644*" and none are working........

DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, , , sfilter

Sorry to cause you problem. and thank for your help.
Nancy
0
 
LVL 51

Accepted Solution

by:
HainKurt earned 500 total points
ID: 36589293
sfilter = "8644*"    I have tried to force the value I want, this way and also "*8644*" and none are working........
-->
sfilter = "[Ordre] = '8644'"

or

sfilter = "[Ordre] like '8644*'"
0
 
LVL 51

Expert Comment

by:HainKurt
ID: 36589299
what do you have in sfilter in your code @ 36575883

MsgBox sfilter
0
 

Author Closing Comment

by:nancytherrien
ID: 36598920
Thank you very much for your help.  It is still not working but I think that the problem is in my data table.  If I do a fresh new table and put clean data in it all of the solution proposed are working.
Thanks very much.
Nancy
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now