Select Statement Array Trouble

keithboesker · 10-20-2012, 05:29 PM

I have a newsletter program I'm trying to use a group list like 1,2,3,4 in my database to send email to one or more groups. So I get the groups say 1,2 but when I try to get the email address from a user say in group 1 I get nothing. I believe it not finding the 1 in the list of 1,2,3 if the user is in those groups. Here's what I have:

Code:

NMGroups = Replace(UploadFormRequest("txtGroup"), " ", "") 'remove white space


strSQL = "SELECT * FROM NewsletterMembers WHERE NMActive = 'True' AND NMGroups IN ('" & NMGroups & "')"
Set sendemails = database.Execute(strSQL)

Thanks for your help in advance!!!!

Old Pedant · 10-21-2012, 02:46 AM

I *TOLD* you that using a delimited list in a single field WILL NOT WORK.

If this line of code:

Code:

NMGroups = Replace(UploadFormRequest("txtGroup"), " ", "")

results in the variable NMGroups having the value of "1,2,3,4" then the *ONLY* records that will match in your query are those where the field in the database is *EXACTLY* 1,2,3,4 !!!

It WILL NOT MATCH "1,2,3". It WILL NOT MATCH "1,2,3,4,5". It WILL NOT MATCH "1".

One more time: Your lack of understanding of correct database design has led you into a corner. You can't get out of this one.