Sub script out of range

  • Thread starter Thread starter Simonglencross
  • Start date Start date
S

Simonglencross

I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Many Thanks in advance!


Simon :)
 
Still getting subscript out of range??

with the following

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Any further ideas?


Simon





MGFoster said:
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

... etc. ...
Click to expand...
 
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

.... etc. ...
 
I think this all evaluates to:
WHERE tblsubscriptions.Cattypes In ('2', '3', '4', '5', '6', '7', '8')

AND tblsubscriptions.Catcost In ('Paid', 'Free', 'Trial')


--
Duane Hookom
MS Access MVP


Simonglencross said:
Still getting subscript out of range??

with the following

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Any further ideas?


Simon





MGFoster said:
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of
range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

... etc. ...
Click to expand...
Click to expand...
 
Just guessing, but why are you using an IN clause for just one value and
then doing other matching for .Cattypes?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes)='4' Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

--

Ken Snell
<MS ACCESS MVP>

Simonglencross said:
Still getting subscript out of range??

with the following

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Any further ideas?


Simon





MGFoster said:
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of
range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

... etc. ...
Click to expand...
Click to expand...
 
Hi ken sorry I have taken that out now it was a query which I copyed from
another form which has a multiselect list box which passes the select made
to a query. I have changed the query to

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

bu I am still getting subscript out of range when I am trying to create a
report.

The report needed has to have the subscribers names and addresses on out of
tblsubscribers and there catalogue subscriptions which is found in
tblsubscriptions any ideas??

Cheers

Simon :)




Ken Snell said:
Just guessing, but why are you using an IN clause for just one value and
then doing other matching for .Cattypes?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes)='4' Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

--

Ken Snell
<MS ACCESS MVP>

Simonglencross said:
Still getting subscript out of range??

with the following

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Any further ideas?


Simon





MGFoster said:
Simonglencross wrote:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of
range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or
Click to expand...
(tblsubscriptions.Cattypes)=('3')
Or
(tblsubscriptions.Cattypes)=('4') Or
Click to expand...
(tblsubscriptions.Cattypes)=('5')
Or
(tblsubscriptions.Cattypes)=('6') Or
Click to expand...
(tblsubscriptions.Cattypes)=('7')
Or
(tblsubscriptions.Cattypes)=('8')) AND
Click to expand...
((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

... etc. ...
Click to expand...
Click to expand...
Click to expand...
 
Hi Duane,

Yes you correct it does I'm fairly new to Access so I trying my best :),
still getting subscript out of range though when I am trying to create my
report?? :(
Any ideas??

Simon


Duane Hookom said:
I think this all evaluates to:
WHERE tblsubscriptions.Cattypes In ('2', '3', '4', '5', '6', '7', '8')

AND tblsubscriptions.Catcost In ('Paid', 'Free', 'Trial')


--
Duane Hookom
MS Access MVP


Simonglencross said:
Still getting subscript out of range??

with the following

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Any further ideas?


Simon





MGFoster said:
Simonglencross wrote:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of
range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or
Click to expand...
(tblsubscriptions.Cattypes)=('3')
Or
(tblsubscriptions.Cattypes)=('4') Or
Click to expand...
(tblsubscriptions.Cattypes)=('5')
Or
(tblsubscriptions.Cattypes)=('6') Or
Click to expand...
(tblsubscriptions.Cattypes)=('7')
Or
(tblsubscriptions.Cattypes)=('8')) AND
Click to expand...
((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

... etc. ...
Click to expand...
Click to expand...
Click to expand...
 
Is Cattypes a numeric field or a text field? If it's a numeric field, then
remove the ' characters around the values that you're using as the criteria
values.

If that isn't it, then you'll need to start over with the query. Start with
just the fields from tblSubscribers and see if you get the error. Add more
fields and tables one at a time until you identify what is causing the
error.

--

Ken Snell
<MS ACCESS MVP>


Simonglencross said:
Hi ken sorry I have taken that out now it was a query which I copyed from
another form which has a multiselect list box which passes the select made
to a query. I have changed the query to

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

bu I am still getting subscript out of range when I am trying to create a
report.

The report needed has to have the subscribers names and addresses on out
of
tblsubscribers and there catalogue subscriptions which is found in
tblsubscriptions any ideas??

Cheers

Simon :)




Ken Snell said:
Just guessing, but why are you using an IN clause for just one value and
then doing other matching for .Cattypes?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes)='4' Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid'
Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

--

Ken Snell
<MS ACCESS MVP>

Simonglencross said:
Still getting subscript out of range??

with the following

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8') AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;


Any further ideas?


Simon





Simonglencross wrote:
I'm looking for a little bit of help. I am trying to create a report
using
the following qutie but I am getting the message subscript out of
range!
Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3')
Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5')
Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7')
Or
(tblsubscriptions.Cattypes)=('8')) AND
((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or
(tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;

Should be

WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)='2' Or (tblsubscriptions.Cattypes)='3' Or
(tblsubscriptions.Cattypes)='4' Or (tblsubscriptions.Cattypes)='5' Or
(tblsubscriptions.Cattypes)='6' Or (tblsubscriptions.Cattypes)='7' Or
(tblsubscriptions.Cattypes)='8')

... etc. ...
Click to expand...
Click to expand...
Click to expand...
 
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingListID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...


Regardless of how you write the list of comparisons (as
above or Duane's cleaner syntax), it has nothing to do with
that error message. Subscript out of range means you are
improperly using an array in some VBA code.

Most likely, your problem is in the code you wrote to
construct the SQL statement (although I don't see how an
array would be needed for this). Inspect that code
carefully, make use of Breakpoints, or, if you can't figure
it out, post the relevant lines of code so we can see what
it looks like.
 
Microsoft has confirmed this to be a problem in Access
2000 ("Subscript Out of Range" error). See article ID
306326 reviewed June 25, 2004. I need to locate the FIX.
Anyone know where to look?

Charlotte
-----Original Message-----
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingList ID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...


Regardless of how you write the list of comparisons (as
above or Duane's cleaner syntax), it has nothing to do with
that error message. Subscript out of range means you are
improperly using an array in some VBA code.

Most likely, your problem is in the code you wrote to
construct the SQL statement (although I don't see how an
array would be needed for this). Inspect that code
carefully, make use of Breakpoints, or, if you can't figure
it out, post the relevant lines of code so we can see what
it looks like.
Click to expand...
 
I don't use the report wizards so I can't help with locating
a fix. As that article suggests, skip the wizard with the
bad code.

But that could be an interesting point re Simon's problem.
If he is using the wizard, then it's no wonder he can't find
the offending code (it's not even his code).
--
Marsh
MVP [MS Access]



Microsoft has confirmed this to be a problem in Access
2000 ("Subscript Out of Range" error). See article ID
306326 reviewed June 25, 2004. I need to locate the FIX.
Anyone know where to look?

-----Original Message-----
Simonglencross said:
I'm looking for a little bit of help. I am trying to create a report using
the following qutie but I am getting the message subscript out of range! Has
anyone any ideqas why?

SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,
tblSubscribers.Surname, tblSubscribers.Company, tblSubscribers.Address,
tblSubscribers.City, tblSubscribers.[Country/Region],
tblSubscribers.PostalCode, tblsubscriptions.Cattypes
FROM tblSubscribers INNER JOIN tblsubscriptions ON
tblSubscribers.MailingListID=tblsubscriptions.MailingList ID
WHERE (((tblsubscriptions.Cattypes) In ('4') Or
(tblsubscriptions.Cattypes)=('2') Or (tblsubscriptions.Cattypes)=('3') Or
(tblsubscriptions.Cattypes)=('4') Or (tblsubscriptions.Cattypes)=('5') Or
(tblsubscriptions.Cattypes)=('6') Or (tblsubscriptions.Cattypes)=('7') Or
(tblsubscriptions.Cattypes)=('8')) AND ((tblsubscriptions.Catcost)='Paid' Or
(tblsubscriptions.Catcost)='Free' Or (tblsubscriptions.Catcost)='Trial'))
ORDER BY tblSubscribers.Surname;
Click to expand...


Regardless of how you write the list of comparisons (as
above or Duane's cleaner syntax), it has nothing to do with
that error message. Subscript out of range means you are
improperly using an array in some VBA code.

Most likely, your problem is in the code you wrote to
construct the SQL statement (although I don't see how an
array would be needed for this). Inspect that code
carefully, make use of Breakpoints, or, if you can't figure
it out, post the relevant lines of code so we can see what
it looks like.
Click to expand...
Click to expand...
 
Back
Top