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
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
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.
--
Bill Mosca, MS Access MVP
"Andreas" <andreas....@ny.frb.org> wrote in message
news:1163801966.9...@j44g2000cwa.googlegroups.com...
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
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)
?
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Bill Mosca, MS Access MVP" <nos...@domain.com> wrote in message
news:uMvG8ypC...@TK2MSFTNGP06.phx.gbl...