We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Need some help with an ASP / Access DB SQL scenerio

Matt Pinkston
on
Medium Priority
241 Views
Last Modified: 2012-05-11
I have a DB with a table called registrations
 
People register to tryout for our teams and I need to do a search on players from the previous year who have not registered for this year.  The issue is the team could change.

I triied this but its not working because the "OR" messes up the serahc of the previous ANDS

HELP!!!!!


set conn = server.createobject("ADODB.Connection")
myDSN = "FILEDSN="
myDSN = myDSN & server.mappath("/fpdb/registrations.dsn")
dim strSQL
dim blnk
blnk="  "
strSQL = "SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations WHERE season = '2010' AND program = 'Virus' ORDER BY email"
conn.open myDSN
set rs = conn.execute(strSQL)

while not rs.eof
xdate = rs("bday")
xdate1 = DatePart("m", Now()) & "/" & DatePart("d", Now()) & "/" & DatePart("YYYY", Now())

xage = DateDiff("yyyy", xdate, xdate1)

  If rs("regdate") > #09/30/2008# Then
  If xemail <> rs("email") Then
     If rs("email") <> "" Then 
        strSQL1 = "SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations WHERE email='" & rs("Email") & "' AND season = '2011' AND program = 'Virus' ORDER BY email"
        set rs1 = conn.execute(strSQL1)
        If rs1.EOF Then
           'Record does not exist
           strDistList = strDistList & rs("Email") & "; "    
           response.write rs("lname") & ", " & rs("fname") & " - " & rs("Email") & "<br>"    
           sentto = sentto + 1     
        Else
           Response.Write "Record exists" & "<br>"
        End If
     End If   
  End If  
  End If  
  xemail = rs("email")
  rs.movenext
wend

rs.close
conn.close

Open in new window

Comment
Watch Question

Commented:
What 'OR' are you talking about?  I see two SQL statements in your code:


SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations
 WHERE season = '2010' AND program = 'Virus'
 ORDER BY email

- and -


SELECT DISTINCT email, bday, program, regdate, lname, fname from registrations
 WHERE email='" & rs("Email") & "' AND season = '2011' AND program = 'Virus'
 ORDER BY email

Both of these look okay.

Can you give us more detail? What error message(s) do you get? Do you get results successfully, but they're just not the results you're expecting? And, what about that elusive 'OR' - where is it?

CERTIFIED EXPERT
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
CERTIFIED EXPERT

Commented:
Just noticed that my two queries above were reversed (the top one should have been below and vice versa), but I guess you already figured that out :)
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.