hi Kevin,
updating to null is not valid unless you do it this way:
If Nz(ELookup("PartItineraryDate", "tblPartItinerary", "PartID=" &
Me.PartID), 0) = 0 Then
strSQL = "UPDATE tblPart " & "SET [PartStart] = Null"
else
' other statements
end if
strSQL = strSQL & " WHERE PartID = " & Me.PartID
CurrentDb.Execute strSQL
'~~~~
but maybe if it is Null, you don't want to do anything? I don't know your business logic.
also, you should make sure Me.PartID is not null before running ...
~crystal
On 2/9/2019 12:04 AM, Kevin qingqinga@yahoo.com
[MS_Access_Professionals] wrote:
> Dear All,
>
> How to insert dates with null value? Here's my code: My problem is that
> If the dates are null, it fails to insert anything. Please help
>
>
> Dim strPartStart As Date
> Dim strPartEnd As Date
> Dim strPartDays As Integer
> Dim strSQLTWO As String
>
>
> If Nz(ELookup("PartItineraryDate", "tblPartItinerary", "PartID=" &
> Me.PartID), 0) = 0 Then
> strPartStart = Null
> strPartEnd = Null
> strPartDays = Null
> Else
> strPartStart = ELookup("PartItineraryDate", "tblPartItinerary",
> "PartID=" & Me.PartID, "PartItineraryDate ASC")
> strPartEnd = ELookup("PartItineraryDate", "tblPartItinerary",
> "PartID=" & Me.PartID, "PartItineraryDate DESC")
> strPartDays = strPartEnd - strPartStart + 1
> End If
>
> strSQLTWO = "UPDATE tblPart " & "SET [PartStart] = #" &
> strPartStart & "#, " & _
> "[PartEnd] = #" & strPartEnd & "#, " & _
> "[PartDays] = " & strPartDays & " WHERE PartID = " & Me.PartID
>
> CurrentDb.Execute strSQLTWO, dbFailOnError
>
>
> Best Regards,
>
> Kevin
>
>
>
> ------------------------------------
> Posted by: Kevin <qingqinga@yahoo.com>
> ------------------------------------
>
>
> ------------------------------------
>
> Yahoo Groups Links
>
>
>
Posted by: crystal 8 <strive4peace2008@yahoo.com>
Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar