A little help

[Simonglencross]

Need a little bit of help on this one if possible....


I have the following code which is working well apart from a little
alteration I need to make which I'm a little stuck on. Below is my code so
far, I have three tables
which are as follows...

tblsubscribers
Primary Key MailinglistID
Title
Surname
Address etc etc

tblCatTypes
Primary Key CatID
CatName
CatCost

tblSubscriptions
Primary Key SubscriptionID
Foreign Key MailingistID
CatTypes
CatCost
Currently the below statement looks at what has been selected in a
multiselect list box and returns the names and addresses of the
corresponding subscribers, I ahve added in an additional bit which is WHERE
tblsubscriptions.Catcost = 'Paid' Or tblsubscriptions.Catcost = 'Free' Or
tblsubscriptions.Catcost = 'Trial' but it is not doing what I want it to do.
What I want is for each subscriber address which is found in the select
cattypes to be shown only if it has a corresponding catcost i.e free, paid
or trial. At the moment it shows be all subscribers in the selected cattype
and then also everyone with a catcost of free, trial and paid.


I hope this makes sense . Thanks


strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename, " &
_
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Catcost = 'Paid' Or tblsubscriptions.Catcost =
'Free' Or tblsubscriptions.Catcost = 'Trial' AND tblsubscriptions.cattypes
IN(" & strCriteria & ") " & _
"ORDER BY tblsubscribers.surname; "

 

[Rob Oldfield]

I'd suspect that your SQL statement is getting a bit upset about what order
to do things in.

If you have a condition that might be either true or false then this kind of
thing happens...

condition1 and condition2 or condition3

....will be true if both of the first two are true OR the 3 is while...

condition1 and (condition2 or condition3)

....will return true if condition1 is true and EITHER of 2 or 3 is also.

Basically... ands take precedence over ors unless you use some brackets.
2+3*4=14, (2+3)*4=20

The best plan is to drop a breakpoint into your code after you create
strSQL, use the immediate window to copy it, paste it into a query SQL
window... and see what's wrong with it.

 

[Simonglencross]

Rob,


Thanks you very much for your help the where statement should have been

WHERE (((tblsubscriptions.Cattypes) In ('4')) AND
((tblsubscriptions.Catcost)='Paid' Or (tblsubscriptions.Catcost)='free' Or
(tblsubscriptions.Catcost)='trial'))

Works a treat now!


Thanks again

Simon

I'd suspect that your SQL statement is getting a bit upset about what order
to do things in.

If you have a condition that might be either true or false then this kind of
thing happens...

condition1 and condition2 or condition3

...will be true if both of the first two are true OR the 3 is while...

condition1 and (condition2 or condition3)

...will return true if condition1 is true and EITHER of 2 or 3 is also.

Basically... ands take precedence over ors unless you use some brackets.
2+3*4=14, (2+3)*4=20

The best plan is to drop a breakpoint into your code after you create
strSQL, use the immediate window to copy it, paste it into a query SQL
window... and see what's wrong with it.

Need a little bit of help on this one if possible....


I have the following code which is working well apart from a little
alteration I need to make which I'm a little stuck on. Below is my code so
far, I have three tables
which are as follows...

tblsubscribers
Primary Key MailinglistID
Title
Surname
Address etc etc

tblCatTypes
Primary Key CatID
CatName
CatCost

tblSubscriptions
Primary Key SubscriptionID
Foreign Key MailingistID
CatTypes
CatCost
Currently the below statement looks at what has been selected in a
multiselect list box and returns the names and addresses of the
corresponding subscribers, I ahve added in an additional bit which is WHERE
tblsubscriptions.Catcost = 'Paid' Or tblsubscriptions.Catcost = 'Free' Or
tblsubscriptions.Catcost = 'Trial' but it is not doing what I want it to do.
What I want is for each subscriber address which is found in the select
cattypes to be shown only if it has a corresponding catcost i.e free, paid
or trial. At the moment it shows be all subscribers in the selected cattype
and then also everyone with a catcost of free, trial and paid.


I hope this makes sense . Thanks


strSQL = "SELECT DISTINCT tblSubscribers.Title, tblSubscribers.Forename,

"

&
_
"tblSubscribers.Surname, tblSubscribers.Company, " & _
"tblSubscribers.Address, " & _
"tblSubscribers.City, tblSubscribers.[Country/Region], " & _
"tblSubscribers.PostalCode " & _
"FROM tblSubscribers INNER JOIN tblsubscriptions ON " & _
"tblSubscribers.MailingListID = tblsubscriptions.MailingListID " & _
"WHERE tblsubscriptions.Catcost = 'Paid' Or tblsubscriptions.Catcost =
'Free' Or tblsubscriptions.Catcost = 'Trial' AND tblsubscriptions.cattypes
IN(" & strCriteria & ") " & _
"ORDER BY tblsubscribers.surname; "