Query with one criteria field, but many criterias

  • Thread starter Thread starter Makelei
  • Start date Start date
M

Makelei

Hi,
I have created a TreeView with checkboxes. I am able to get a query to run
with selectied criterias, BUT when selection exceeds 50 criterias, it will
not run the rest and jums the query.


Here's code I have used:
Any proposals how to proceed when query exceeds that 50 limit?
If xTree.Nodes.Count > 0 Then
For i = 1 To xTree.Nodes.Count
If xTree.Nodes(i).Checked = True Then
strSel = strSel & "((([04-Orders].[Module and Version]) Like
""*" & xTree.Nodes(i) & "*"" ) AND ((Sum([04-Orders].[Need amount]))>0) AND
(([04-Orders].[Cancelled order])=No) AND (([04-Orders].Delivered1)=No) AND
(([04-Orders].Delivered2)=No) AND (([04-Orders].Delivered3)=No)) OR "

lngSel = Len(strSel) - 4
strSel = Left$(strSel, lngSel)

strSQL = "SELECT DISTINCTROW [04-Orders].[Program],
[04-Orders].[Module and Version], "
strSQL = strSQL & " FROM [04-Orders] "
...
strSQL = strSQL & " HAVING ( "
strSQL = strSQL & strSel
strSQL = strSQL & " )"

wkb1a = "...Orders by statuses-" & Format(Now(),
"yyyymmdd-hh-mm-ss") & ".xls"
wkb1b = "...Orders by statuses.xls"
Que1a = "Filt_Needs1"
Que1b = "PRE Needs_Crosstab"

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.OpenQuery Que1a, , acReadOnly
DoCmd.Close acQuery, Que1a, acSaveYes

stDocName = Que1a
CurrentDb.QueryDefs("Filt_Needs1").SQL = strSQL
DoCmd.OpenQuery stDocName

FileCopy wkb1b, wkb1a

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
Que1a, wkb1a
Call Shell("Excel " & Chr$(34) & wkb1a & Chr$(34),
vbMaximizedFocus)
DoCmd.Close acQuery, stDocName, acSaveYes

Else
End If
Next
End If

Thanking for all comments...
BR
MakeLei
 
Hi,
Looks like you have reached query's SQL limit. Try to rethink strSel
creation, looks like:

((Sum([04-Orders].[Need amount]))>0) AND (([04-Orders].[Cancelled
order])=No) AND (([04-Orders].Delivered1)=No) AND
(([04-Orders].Delivered2)=No) AND (([04-Orders].Delivered3)=No))

you can put only once and dont need to copy for each node

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com
 
Hi Alex,
Once again Thank you!

This answerand te other concerning levels will help me to decrees the amount
of needed criterias.

All the best
Markku

Alex Dybenko said:
Hi,
Looks like you have reached query's SQL limit. Try to rethink strSel
creation, looks like:

((Sum([04-Orders].[Need amount]))>0) AND (([04-Orders].[Cancelled
order])=No) AND (([04-Orders].Delivered1)=No) AND
(([04-Orders].Delivered2)=No) AND (([04-Orders].Delivered3)=No))

you can put only once and dont need to copy for each node

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com


Makelei said:
Hi,
I have created a TreeView with checkboxes. I am able to get a query to run
with selectied criterias, BUT when selection exceeds 50 criterias, it will
not run the rest and jums the query.


Here's code I have used:
Any proposals how to proceed when query exceeds that 50 limit?
If xTree.Nodes.Count > 0 Then
For i = 1 To xTree.Nodes.Count
If xTree.Nodes(i).Checked = True Then
strSel = strSel & "((([04-Orders].[Module and Version]) Like
""*" & xTree.Nodes(i) & "*"" ) AND ((Sum([04-Orders].[Need amount]))>0)
AND
(([04-Orders].[Cancelled order])=No) AND (([04-Orders].Delivered1)=No) AND
(([04-Orders].Delivered2)=No) AND (([04-Orders].Delivered3)=No)) OR "

lngSel = Len(strSel) - 4
strSel = Left$(strSel, lngSel)

strSQL = "SELECT DISTINCTROW [04-Orders].[Program],
[04-Orders].[Module and Version], "
strSQL = strSQL & " FROM [04-Orders] "
...
strSQL = strSQL & " HAVING ( "
strSQL = strSQL & strSel
strSQL = strSQL & " )"

wkb1a = "...Orders by statuses-" & Format(Now(),
"yyyymmdd-hh-mm-ss") & ".xls"
wkb1b = "...Orders by statuses.xls"
Que1a = "Filt_Needs1"
Que1b = "PRE Needs_Crosstab"

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
DoCmd.OpenQuery Que1a, , acReadOnly
DoCmd.Close acQuery, Que1a, acSaveYes

stDocName = Que1a
CurrentDb.QueryDefs("Filt_Needs1").SQL = strSQL
DoCmd.OpenQuery stDocName

FileCopy wkb1b, wkb1a

DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9,
Que1a, wkb1a
Call Shell("Excel " & Chr$(34) & wkb1a & Chr$(34),
vbMaximizedFocus)
DoCmd.Close acQuery, stDocName, acSaveYes

Else
End If
Next
End If

Thanking for all comments...
BR
MakeLei
Click to expand...
Click to expand...
 
Back
Top