• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 428
  • Last Modified:

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

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
Nancy Therrien
Asked:
Nancy Therrien
  • 12
  • 9
  • 2
  • +1
1 Solution
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
I have forgot to say that query is not a part of that form, it is a completly separate query.
Thanks
0
 
HainKurtSr. System AnalystCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
peter57rCommented:
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
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
peter57rCommented:
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
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
then Line 10-14 should be

    if sfilter = "" then
      sfilter = "[NO_JOB]='" & ctl & "'"
    else
      sfilter = sfilter & " or [NO_JOB]='" & ctl & "'"
    end if
0
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
what happens if you hard code filter and call

DoCmd.OpenReport "rpt_requeteHistoriqueAchat", acViewPreview, , , , "[NO_JOB]='a sample job'"
0
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
post the report query...
0
 
HainKurtSr. System AnalystCommented:
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
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
ok will try this this afternoon. Thanks, I will keep you posted.
0
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
 
HainKurtSr. System AnalystCommented:
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
 
HainKurtSr. System AnalystCommented:
what do you have in sfilter in your code @ 36575883

MsgBox sfilter
0
 
Nancy TherrienTI Tech and NetworkAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

  • 12
  • 9
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now