Sub script out of range

[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

 

[Simonglencross]

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

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

 

[MGFoster]

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

 

[Duane Hookom]

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

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

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

 

[Ken Snell [MVP]]

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>

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

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

 

[Simonglencross]

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

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>

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

 

[Simonglencross]

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

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

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

 

[Ken Snell [MVP]]

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>

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

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>

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

 

[Marshall Barton]

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;

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.

 

[Clewis]

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

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;

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.

 

[Marshall Barton]

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

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;

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.