ADD = "INSERT INTO Permtracker (Address, plan, Community, IntranetPost_date, submit_date, Acct_pu_date, Est_pu_date, Issue_date) Values ('"& Address &"', '"& plan &"', '"& Community &"', #"& IntranetPost_date &"#, #"& submit_date &"#, #"& Acct_pu_date &"#, #"& Est_pu_date &"#, #"& Issue_date &"#)"


Error is:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression '##'.


That's the error I get when one of the date fields is blank, Anywya to write things different with out writing a huge if statment checking for the million of possbile combos?

Thanks,
-josh

bump.

Anything?

In your case, you need to check if the date is blank.

Any other way to do this without a zillion if, else, if else, if, else ?

Thanks,
-josh

There are five fields with date datatype. Since the blank is allowed, I guess these fields in table can be NULL or '', right?

<%
Dim SQL1, SQL2, SQL
SQL1 = "INSERT INTO Permtracker (Address, plan, Community"
SQL2 = "Values('" & Address & "', '" & plan & "', '" & Community & "'"

If Not IsEmpty(IntranetPost_date) AND Trim(IntranetPost_date) <> "" Then
SQL1 = SQL1 & ", IntranetPost_date"
SQL2 = SQL2 & ", #" & IntranetPost_date & "#"
End If

If Not IsEmpty(submit_date) AND Trim(submit_date) <> "" Then
SQL1 = SQL1 & ", submit_date"
SQL2 = SQL2 & ", #" & submit_date & "#"
End If

If Not IsEmpty(Acct_pu_date) AND Trim(Acct_pu_date) <> "" Then
SQL1 = SQL1 & ", Acct_pu_date"
SQL2 = SQL2 & ", #" & Acct_pu_date & "#"
End If

If Not IsEmpty(Est_pu_date) AND Trim(Est_pu_date) <> "" Then
SQL1 = SQL1 & ", Est_pu_date"
SQL2 = SQL2 & ", #" & Est_pu_date & "#"
End If

If Not IsEmpty(Issue_date) AND Trim(Issue_date) <> "" Then
SQL1 = SQL1 & ", Issue_date"
SQL2 = SQL2 & ", #" & Issue_date & "#"
End If

SQL = SQL1 & SQL2 & ")"
%>

fixed one minor thing..on the 4th line (sql2

SQL1 = "INSERT INTO Permtracker (Address, plan, Community"
SQL2 = "Values('" & Address & "', '" & plan & "', '" & Community & "'"

Added

SQL2 = ") Values('" & Address & "', '" & plan & "', '" & Community & "'"


All in all though that code is a lot cleaner and I like it thanks.

-josh

You are right. I forgot to put ") " at the beginning of SQL2.