View Single Post
  #1 (permalink)  
Old May 5th, 2008, 07:02 PM
MarkGT MarkGT is offline
Authorized User
 
Join Date: Apr 2008
Location: , , .
Posts: 19
Thanks: 0
Thanked 0 Times in 0 Posts
Default Need help with conditional sql statement

I need to create a conditional sql statement. I don't know the syntax for putting "if...else" statements inside sql strings. Is that even possible or do I have to break up the sql and concatenate it with the "if...else" statements? I need to be able to update multiple dates in an access database, but the date fields also need to be able to remain empty. My current update statement works if dates are entered in every field. It does not work if any of the fields are blank. I understand why this is. I just don't know how to build the statement around it. Below is a piece of my sql statement (the whole thing is pretty long) and an idea of what I need to do.

Code:
dim r1, r2, r3, r4, r5, xemp_idx
.
.
.
xemp_idx=request.Form("xemp_idx")
r1=Request.Form("zEmp_idz")
r2=Request.Form("First_Name")  
r3=Request.Form("xLast_Namex") 
r4=Request.Form("Test1_Date")
r5=Request.Form("Test2_Date").
.
.

rsql = " UPDATE S6Test SET Emp_ID='"& r1 &"', First_Name='" & r2 & "', Last_Name='" & r3 & "',
Test1_Date= #" & r4 & "#, Test2_Date=#" & r5 & "# WHERE Emp_ID='"& xemp_idx &"';"
.
.
.

I was thinking about something along the lines of...

rsql = " UPDATE S6Test SET Emp_ID='"& r1 &"', First_Name='" & r2 & "', Last_Name='" & r3 & "'

if request.form("Test1_Date") <> "" then
r4=request.form("Test1_Date")
sql = sql & "Test1_Date=#" & r4 &"#"
else
sql = sql & "Test1_Date=null"
end if

if request.form("Test2_Date") <> "" then
r4=request.form("Test2_Date")
sql = sql & "Test2_Date=#" & r5 &"#"
else
sql = sql & "Test2_Date=null"
end if

sql= sql & "WHERE Emp_ID='"& xemp_idx &"';"

I know some of you aren't too keen on using null values, but they work for my purpose (simple is better right now). Ive tried what I typed above, but it doesn't work. So how could I build the sql statement to encorporate the "if...else" statement?
Reply With Quote