Query with one criteria field, but many criterias

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
 
A

Alex Dybenko

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
 
M

Makelei

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...
 
Top