OK i have this record in the table Users in my database

User_ID: 31
FirstName:  John
LastName:  Doe
DOB:  12-01-1950

Now I want to check if that record realy exists in the database with the following sql-query:

firstname = "John"
lastname = "Doe"
dob = "12-01-1950"

sql_UserExists = ""
sql_UserExists = sql_UserExists & "SELECT User_ID "
sql_UserExists = sql_UserExists & "FROM Users "
sql_UserExists = sql_UserExists & "WHERE Firstname = '"& HQ(firstname) &"' "
sql_UserExists = sql_UserExists & "AND LastName = '"& HQ(lastname) &"' "
sql_UserExists = sql_UserExists & "AND DOB = "& CDate(dob) &" "

the HQ-function removes the ' from the string

and I check it with:

SET rs_UserExists = Server.CreateObject("ADODB.Recordset")
rs_UserExists.Open sql_UserExists, Conn
UserExists = NOT(rs_UserExists.BOF AND rs_UserExists.EOF)
rs_UserExists.Close
SET rs_UserExists = Nothing

now UserExists has the value FALSE, but when I remove the line:

sql_UserExists = sql_UserExists & "AND DOB = "& CDate(dob) &" "

it has the value TRUE, so something must be wrong with the last line, but I cannot see what it is...

I also included the Session.LCID = 1043 statement for the way dates are represented

Depending on what database you are using, you need to wrap the data value in single quotes or # signs

SQL:
sql_UserExists = sql_UserExists & "AND DOB = '"& CDate(dob) &"' "

Access:
sql_UserExists = sql_UserExists & "AND DOB = #"& CDate(dob) &"# "

Tnx I, i use Acces...and it works with the #'s!!!

Are there more of those #'s or ''s you have to use with integers, strings or dates?

Is there a list availabe somewhere?

In either:
- Strings get single quotes
- Numbers dont

Access Dates
- wrapped in #'s

SQL Dates
- wrapped in single quotes