UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Update And Inserts Fail When Date Field Is Blank, Access 2016    
 
   
cpsmith58
post Apr 20 2017, 09:27 AM
Post#1



Posts: 110
Joined: 22-February 17



I have seen a lot of people have this but haven't seen a solution that works for me. I have a date field called dtPaid, when I insert a record initially, that bill hasn't been paid, so it's blank (in human terms). Trying to save a blank date in an insert or update doesn't work though.

Date format function blatantly stolen from some smart guy here, I put a hack in to handle blanks, that is not working.
CODE
Public Function quDate(dt As Variant) As String
    If IsNull(dt) Then
        quDate = "##"
    Else
        ' return formatted date
        quDate = "#" & Format(dt, "mm\/dd\/yyyy") & "#"
   End If
End Function



Here is the Insert code, note the problem is in the 3rd field from the bottom.

CODE
strSQL = "INSERT INTO tblFeeRecord "
            strSQL = strSQL & "(fkLoc, fkFeeType, nfeeQty, cFeeAmount, dtDue, dtPaid, txtPaidBy, txtFRNotes) VALUES ("
            strSQL = strSQL & Val(Me.txtfkLoc.Value) & ", "
            strSQL = strSQL & Val(Me.txtfkFeeType.Value) & ", "
            strSQL = strSQL & Val(Me.txtnFeeQty.Value) & ", "
            strSQL = strSQL & Val(Me.txtFeeAmt.Value) & ", "
            strSQL = strSQL & quDate(Me.txtdtDue.Value) & ", "
            strSQL = strSQL & quDate(Me.txtdtDue.Value) & ", "
            strSQL = strSQL & "'" & Me.txtPaidBy.Value & "', "
            strSQL = strSQL & "'" & Me.txtFeeNotes.Value & "')"


I got around it on an insert by doing this, it's clumsy but it works:

CODE
' Creating new record, validation passed and it's not a duplicate
        If IsNull(Me.txtPaidBy.Value) Or Len(Me.txtPaidBy.Value) = 0 Then
            strSQL = "INSERT INTO tblFeeRecord "
            strSQL = strSQL & "(fkLoc, fkFeeType, nfeeQty, cFeeAmount, dtDue, txtPaidBy, txtFRNotes) VALUES ("
            strSQL = strSQL & Val(Me.txtfkLoc.Value) & ", "
            strSQL = strSQL & Val(Me.txtfkFeeType.Value) & ", "
            strSQL = strSQL & Val(Me.txtnFeeQty.Value) & ", "
            strSQL = strSQL & Val(Me.txtFeeAmt.Value) & ", "
            strSQL = strSQL & quDate(Me.txtdtDue.Value) & ", "
            strSQL = strSQL & "'" & Me.txtPaidBy.Value & "', "
            strSQL = strSQL & "'" & Me.txtFeeNotes.Value & "')"
        Else
            strSQL = "INSERT INTO tblFeeRecord "
            strSQL = strSQL & "(fkLoc, fkFeeType, nfeeQty, cFeeAmount, dtDue, dtPaid, txtPaidBy, txtFRNotes) VALUES ("
            strSQL = strSQL & Val(Me.txtfkLoc.Value) & ", "
            strSQL = strSQL & Val(Me.txtfkFeeType.Value) & ", "
            strSQL = strSQL & Val(Me.txtnFeeQty.Value) & ", "
            strSQL = strSQL & Val(Me.txtFeeAmt.Value) & ", "
            strSQL = strSQL & quDate(Me.txtdtDue.Value) & ", "
            strSQL = strSQL & quDate(Me.txtdtDue.Value) & ", "
            strSQL = strSQL & "'" & Me.txtPaidBy.Value & "', "
            strSQL = strSQL & "'" & Me.txtFeeNotes.Value & "')"
        End If


The update doesn't work with the same logic because I am guessing an update requires all fields, so skipping a field like the insert does is not working.

Help!
Go to the top of the page
 
doctor9
post Apr 20 2017, 09:35 AM
Post#2


UtterAccess Editor
Posts: 17,217
Joined: 29-March 05
From: Wisconsin


cpsmith58,

Give this a try:

CODE
Public Function quDate(dt As Variant) As String
    If IsNull(dt) Then
        quDate = "Null"
    Else
        ' return formatted date
        quDate = "#" & Format(dt, "mm\/dd\/yyyy") & "#"
   End If
End Function


Remember, in database terms there's a difference between an empty string and Null.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
cpsmith58
post Apr 20 2017, 10:05 AM
Post#3



Posts: 110
Joined: 22-February 17



So that's the same as returning "Bob", right? it's a string.

I will give it a whirl, thanks!
Go to the top of the page
 
doctor9
post Apr 20 2017, 10:25 AM
Post#4


UtterAccess Editor
Posts: 17,217
Joined: 29-March 05
From: Wisconsin


cpsmith58,

> So that's the same as returning "Bob", right? it's a string.

Not quite. It's the same as returning Bob - note there are no quotation marks. Your finished SQL statement should be like this:

INSERT INTO tblFeeRecord (fkLoc, fkFeeType) VALUES (Null, 17);

No quotes, no octothorpes, just the word Null.

Hope this helps,

Dennis

--------------------
(;,;) Li'l Cthulu says: Please talk about what you're trying to do, as well as how you're doing it.
Changing your real table name to "Table1" and your real form name to "Form1" in your posts makes it more difficult to understand what's going on, not easier.
Guidelines for Posting Questions
Go to the top of the page
 
LPurvis
post Apr 20 2017, 10:26 AM
Post#5


UtterAccess Editor
Posts: 16,012
Joined: 27-June 06
From: England (North East / South Yorks)


Just for ref, there are the old fSQLDate and fSQLDelim function examples, as shown recently here.
You've an equivalent of the fSQLDate now.

Cheers

--------------------
Go to the top of the page
 
cpsmith58
post Apr 20 2017, 02:52 PM
Post#6



Posts: 110
Joined: 22-February 17



Doctor9,

That worked, thanks!!!

(I could have sworn I tried that, grr)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    26th June 2017 - 05:17 PM