Re: [MS_AccessPros] How to insert dates with Null value?

 

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)

---

Have you tried the highest rated email app?

With 4.5 stars in iTunes, the Yahoo Mail app is the highest rated email app on the market. What are you waiting for? Now you can access all your inboxes (Gmail, Outlook, AOL and more) in one place. Never delete an email again with 1000GB of free cloud storage.

---

Visit Your Group

• Privacy • Unsubscribe • Terms of Use

SPONSORED LINKS

.

__,_._,___