Solved

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

Posted on 2011-09-19
24
376 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:Nancy Therrien
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 12
  • 9
  • 2
  • +1
24 Comments
 

Author Comment

by:Nancy Therrien
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 56

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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 

Author Comment

by:Nancy Therrien
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:Nancy Therrien
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:Nancy Therrien
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:Nancy Therrien
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 56

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:Nancy Therrien
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 56

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
 

Author Comment

by:Nancy Therrien
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 56

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:Nancy Therrien
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 56

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:Nancy Therrien
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 56

Expert Comment

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

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:Nancy Therrien
ID: 36575075
ok will try this this afternoon. Thanks, I will keep you posted.
0
 

Author Comment

by:Nancy Therrien
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 56

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 56

Expert Comment

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

MsgBox sfilter
0
 

Author Closing Comment

by:Nancy Therrien
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

627 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