Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Posts
    19

    Unanswered: Insert into - Syntax error in date in query expression '##'.

    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

  2. #2
    Join Date
    Feb 2004
    Posts
    19
    bump.

    Anything?

  3. #3
    Join Date
    Dec 2003
    Posts
    454
    In your case, you need to check if the date is blank.

  4. #4
    Join Date
    Feb 2004
    Posts
    19
    Any other way to do this without a zillion if, else, if else, if, else ?

    Thanks,
    -josh

  5. #5
    Join Date
    Dec 2003
    Posts
    454
    There are five fields with date datatype. Since the blank is allowed, I guess these fields in table can be NULL or '', right?

  6. #6
    Join Date
    Dec 2003
    Posts
    454
    <%
    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 & ")"
    %>

  7. #7
    Join Date
    Feb 2004
    Posts
    19
    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

  8. #8
    Join Date
    Dec 2003
    Posts
    454
    You are right. I forgot to put ") " at the beginning of SQL2.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •