SQL statement not working properly

  • Thread starter Thread starter Antonio
  • Start date Start date
A

Antonio

Good morning, all, I am trying to get the e-mails of all
the customers that subscribe to a title and that are
currently in an active status.
the sql I have in my code is:

sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 ='" &
mySubcode & "' & _
"'AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.
[E-mail Address] HAVING CustomerDB.[E-mail Address]
Like '*'"

The problem is that lists all the e-mail addresses for all
the customers subscribing to the title, even if their
account is inactive. Also, I have titles that have
apostrophe in it and I get an error message when I do a
search on those. Any idea? Thanks, Antonio
 
Instead of indicating 'No' for your Inactive Customer, indicate FALSE. For
boolean data types, use True or False.

For your titles that have apostrophe's, you will need to make a slight
change BEFORE you save the data. Use the REPLACE function and change the
apostrophe to TWO apostrophe's. That is:

Replace( SubscriptionTitle1, chr(39), chr(39) & chr(39) )

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
 
I'm not sure what's happening with your query returning all records. It
does look like you have an extra ( "' ) in the third line. I'd do this:

sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 ='" & mySubcode & _
"' AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.[E-mail
Address] HAVING CustomerDB.[E-mail Address]
Like '*'"
Debug.Print sql

The Debug.Print will print the string to the immediate window where you can
see how the Access is evaluating it.

Next, the apostrophe problem can be corrected by replacing each apostrophe
with TWO double quotes:
sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 =""" & mySubcode & _
""" AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.[E-mail
Address] HAVING CustomerDB.[E-mail Address]
Like '*'"
 
Rob, thank you for your time, but it's not working. It
keeps displaying all the customers that subscribe to the
title, even if their account has expired.
The Account inactive field is a checkbox, set to Yes/No.
Antonio
-----Original Message-----
Instead of indicating 'No' for your Inactive Customer, indicate FALSE. For
boolean data types, use True or False.

For your titles that have apostrophe's, you will need to make a slight
change BEFORE you save the data. Use the REPLACE function and change the
apostrophe to TWO apostrophe's. That is:

Replace( SubscriptionTitle1, chr(39), chr(39) & chr(39) )

HTH

--
Rob

FMS Professional Solutions Group
http://www.fmsinc.com/consulting

Software Tools for .NET, SQL Server, Visual Basic & Access
http://www.fmsinc.com

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Good morning, all, I am trying to get the e-mails of all
the customers that subscribe to a title and that are
currently in an active status.
the sql I have in my code is:

sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 ='" &
mySubcode & "' & _
"'AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.
[E-mail Address] HAVING CustomerDB.[E-mail Address]
Like '*'"

The problem is that lists all the e-mail addresses for all
the customers subscribing to the title, even if their
account is inactive. Also, I have titles that have
apostrophe in it and I get an error message when I do a
search on those. Any idea? Thanks, Antonio
Click to expand...


.
Click to expand...
 
Roger, the apostrophe is now working, thank you. I still
can't seem to get only the e-mails for active accounts,
though. Thank you for your suggestions.
-----Original Message-----
I'm not sure what's happening with your query returning all records. It
does look like you have an extra ( "' ) in the third line. I'd do this:

sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 ='" & mySubcode & _
"' AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.[E-mail
Address] HAVING CustomerDB.[E-mail Address]
Like '*'"
Debug.Print sql

The Debug.Print will print the string to the immediate window where you can
see how the Access is evaluating it.

Next, the apostrophe problem can be corrected by replacing each apostrophe
with TWO double quotes:
sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 =""" & mySubcode & _
""" AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.[E-mail
Address] HAVING CustomerDB.[E-mail Address]
Like '*'"

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org

Good morning, all, I am trying to get the e-mails of all
the customers that subscribe to a title and that are
currently in an active status.
the sql I have in my code is:

sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 ='" &
mySubcode & "' & _
"'AND CustomerDB.InactiveCustomer = No GROUP BY CustomerDB.
[E-mail Address] HAVING CustomerDB.[E-mail Address]
Like '*'"

The problem is that lists all the e-mail addresses for all
the customers subscribing to the title, even if their
account is inactive. Also, I have titles that have
apostrophe in it and I get an error message when I do a
search on those. Any idea? Thanks, Antonio
Click to expand...


.
Click to expand...
 
a good tip is to use HAVING only for Aggregates (ie Sum/Count)
To avoid it happening in the designer allways make a sepparate column for
your criteria (Where instead of Group By in totals)

sql = "SELECT CustomerDB.[E-mail Address] FROM
CustomerDB WHERE CustomerDB.SubscriptionTitle1 ='" & mySubcode & "' & _
"'AND CustomerDB.InactiveCustomer = False " & _
"AND CustomerDB.[E-mail Address] Like '*'" & _
" GROUP BY CustomerDB.[E-mail Address] "

in this particular case I would also change
"AND CustomerDB.[E-mail Address] Like '*'" & _
to
"AND CustomerDB.[E-mail Address] Is Not Null " & _

HTH

Pieter
 
Back
Top