Override Default date

[Imdabaum]

I'm not sure what I messed up on in this statement, but I'm trying to add a note into a table.


Dim today as Date
today = Date
sqlUpdate = "INSERT INTO tblPropertiesUpdates (PropertyID, PropertyNumber, NoteDate, entryType, RER, MemoPart, Memo) VALUES (" & Me.txtID &", '" & Me.PropertyNumber & "', " & FormatDateTime(today, vbShortDate) & ", '" & Me.cboNoteType.Column(1) & "', '" & Me.RER & "', '" & Me.notePart & "', '" & Me.newNote & "');"


The table's date is set as a shortdate, but anytime I insert a statement the default date of 12/30/1899 is entered instead. Any ideas what I'm missing here?
:banghead:

[moa]

I think fromatDateTime returns a string and you're treating it like a Date in the SQL statement (no quote marks). Don't know if you can insert a string into NoteDate either though.

[Imdabaum]

No NoteDate is a Time/Date field with a short date format.

I found that if I put # before and after the date it works. Now the problem is that it's inserted correctly but the listbox that allows the users to edit the notes afterwards won't work if their message contains any sort of apostrophe.