| | |
|
|
|
SQL statement not working properly |
|---|
| message from Antonio on 10 May 2004 |
Good morning, all, I am trying to get the e-mails of all
the customers that subscribe to a title and that are
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
|
| RobFMS replied to Antonio on 10 May 2004 |
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
|
| Antonio replied to RobFMS on 10 May 2004 |
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
indicate FALSE. For
make a slight
and change the
CustomerDB.
|
| Pieter Wijnen replied to Antonio on 11 May 2004 |
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
"Antonio" <anonymous@discussions.microsoft.com> wrote in message
news:ad6101c43698$caa0c800$a001280a@phx.gbl...
|
| Roger Carlson replied to Antonio on 10 May 2004 |
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 '*'"
|
| Antonio replied to Roger Carlson on 10 May 2004 |
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.
all records. It
line. I'd do this:
mySubcode & _
CustomerDB.[E-mail
window where you can
replacing each apostrophe
mySubcode & _
CustomerDB.[E-mail
CustomerDB.
|
|
Archived message: SQL statement not working properly (Microsoft Access Database)
