asked on

Optional Date field in access vba Insert

Hi,
i am trying to insert an optional Date variable in a table but i don't want it should have any default date just empty, i tried a few ways but still no success
here is my code

thanks forever

Sub AddUpdateReminder(Optional tbl As String, Optional fld As String, Optional PrimKey As Long, Optional Description As String, _
                       Optional Reminding As String, Optional mValue As String, Optional remdate As String, Optional remtime As String, Optional assigned As String, _
                       Optional remID As Long)

If Not remID > 0 Then       'Insert
remdate = IIf(remdate > "", CDate(remdate), "")
remtime = IIf(Not remtime > "", CDate(remtime), "")

CurrentDb.Execute "Insert Into Reminders(Tbl,Fld,PrimKey,Description,Reminding,mValue, " & _
                    "RemDate,RemTime,Assigned,Created,User) " & _
                    "Values ('" & tbl & "','" & fld & "'," & PrimKey & ",'" & Description & "','" & Reminding & "','" & mValue & "', " & _
                    " " & Nz(remdate, 0) & "," & Nz(remtime, 0) & " ,'" & assigned & "',#" & Now & "#,'" & Cuser & "')"

Else                        'Update

End If

Open in new window

ASKER CERTIFIED SOLUTION

Scott McDaniel (EE MVE )

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Try this:

Sub AddUpdateReminder(Optional tbl As String, Optional fld As String, Optional PrimKey As Long, Optional Description As String, _
                       Optional Reminding As String, Optional mValue As String, Optional remdate As String, Optional remtime As String, Optional assigned As String, _
                       Optional remID As Long)

If Not remID > 0 Then       'Insert
remdate = IIf(IsDate(remdate), Format(DateValue(remdate), "\#yyyy\/mm\/dd\#"), "Null")
remtime = IIf(IsDate(remtime), Format(TimeValue(remtime), "\#hh\:nn\:ss\#"), "Null")

CurrentDb.Execute "Insert Into Reminders(Tbl,Fld,PrimKey,Description,Reminding,mValue, " & _
                    "RemDate,RemTime,Assigned,Created,User) " & _
                    "Values ('" & tbl & "','" & fld & "'," & PrimKey & ",'" & Description & "','" & Reminding & "','" & mValue & "', " & _
                    remdate & "," & remtime ",'" & assigned & "', Now(),'" & Cuser & "')"

Else                        'Update

End If

Open in new window

Also, use Now() inline.

/gustav