Access Query limitations

[Andreas]

Hello,

I wrote a function in which a query is run based on a multiselect list
box in a form. The code (below) works just fine as long as I select a
number of accounts from the list box. However, if I select all accounts
in the list box, Access shuts down immediately being unable to run the
query.

Private Sub run_download_request_Click()

On Error GoTo Err_Handler
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

DoCmd.SetWarnings (off)

For Each varItem In Me!Country.ItemsSelected
strCriteria = strCriteria & "fpmcapshist_fpmrequest.senderaba =" &
Chr(34) & Me!Country.ItemData(varItem) & Chr(34) & " And
((FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE) Between
forms.Download_Selection.Start_Date And
Forms.Download_Selection.End_Date) " & " OR "
Next varItem

strCriteria = Left(strCriteria, Len(strCriteria) - 3)


strSQL = "SELECT FPMCAPSHIST_FPMREQUEST.SENDERABA,
FPMCAPSHIST_FPMrequestdata.sequencenumber,
FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE,
FPMCAPSHIST_FPMREQUEST.RECEIVERABA, FPMCAPSHIST_FPMrequest.inputid,
FPMCAPSHIST_FPMREQUEST.RECEIVERNAME,
FPMCAPSHIST_FPMREQUEST.CUSTOMPROPERTY1,
fpmcapshist_fpmrequestdata.data,fpmcapshist_fpmrequest.sendername, " &
_

"CCur(IIf(InStr(1,fpmcapshist_fpmrequestdata.data,'<amount>')=0,Null,Mid(fpmcapshist_fpmrequestdata.data,(InStr(1,fpmcapshist_fpmrequestdata.data,'<amount>')+8),(InStr(1,fpmcapshist_fpmrequestdata.data,'</amount>')-(InStr(1,fpmcapshist_fpmrequestdata.data,'<amount>')+8)))))
AS amount INTO FPMRequest_DownloadTable " & _
"FROM FPMCAPSHIST_FPMREQUEST INNER JOIN
FPMCAPSHIST_FPMREQUESTDATA ON
(FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE =
FPMCAPSHIST_FPMREQUESTDATA.APPLICATIONCYCLEDATE) AND
(FPMCAPSHIST_FPMREQUEST.INPUTID =
FPMCAPSHIST_FPMREQUESTDATA.REQUEST_INPUTID)" & _
"WHERE " & strCriteria

CurrentDb.QueryDefs("FPMRequest_Download").SQL = strSQL


Now, I have the following questions for the community:
1) Are there limits to query conditions? Can I only create so many
conditions in a single query?
2) Does someone have an idea for quering all items at the same time? I
know I could remove the filter, but the external database the query
accesses has way more data than I want to query.

Thanks in advance.

Andreas

 

[aaron.kempf]

yeah.. MDB queries just CRAP OUT when they get too complex.
it's one of the main reasons I hate MDB so much.. that and the fact
that you can't alias a field to have the same name as a field... those
2 things; in combination-- are a total pain in the ass.

I would reccomend:

a) not using MDB for anything; not for a single user and a single
record
b) spit on anyone else that uses it for anything

-Aaron

 

[Bill Mosca, MS Access MVP]

Andreas

If you could post what the final string looks like we might be able to offer
alternatives.

According to the help files under Access Specifications, the number of ANDs
(and I'm assuming ORs) in a WHERE clause is 99, but that might not be your
problem.

 

[aaron.kempf]

yeah.. but everyone knows that MDB queries CRAP OUT when it gets 'too
complex'
stack a couple of queries on top of each other and see if they work

then you should get what im talking about.

if you weren't a flaming wimp and you had the balls to use Access Data
Projects; you wouldn't constantly be subjected to rewriting crap to use
a temp table.

Real databases don't make you use temp tables.
Access makes you use temp tables because it doesn't have an engine; it
doesn't have a brain

-Aaron
ADP Nationalist

 

[Douglas J. Steele]

It would probably be better not to include the date criteria with every item
in the listbox. Right now, it's building a Where clause along the lines of

fpmcapshist_fpmrequest.senderaba = "US" And
((FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE) Between
forms.Download_Selection.Start_Date And Forms.Download_Selection.End_Date)
OR
fpmcapshist_fpmrequest.senderaba = "CA" And
((FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE) Between
forms.Download_Selection.Start_Date And Forms.Download_Selection.End_Date)
OR
fpmcapshist_fpmrequest.senderaba = "MX" And
((FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE) Between
forms.Download_Selection.Start_Date And Forms.Download_Selection.End_Date)

Why not

(fpmcapshist_fpmrequest.senderaba = "US" OR
fpmcapshist_fpmrequest.senderaba = "CA" OR
fpmcapshist_fpmrequest.senderaba = "MX") And
((FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE) Between
forms.Download_Selection.Start_Date And Forms.Download_Selection.End_Date)

or, even better

fpmcapshist_fpmrequest.senderaba IN ("US", "CA", "MX") And
((FPMCAPSHIST_FPMREQUEST.APPLICATIONCYCLEDATE) Between
forms.Download_Selection.Start_Date And Forms.Download_Selection.End_Date)

?