I started to write a routine that will allow the users will enter a start date and a price. I calculate the end date by determining what the next date is and then giving that record an end date of the next start date - 1.
Part Start Date End Date Price
123 1/1/2008 3/14/2008 13.5
123 3/15/2008 5/28/2008 13.62
123 5/29/2008 12/31/9999 13.65
If my table looks like this and if I add any new record after the beginning date of 1/1/2008 the code as I have it written works great. My problem is when I decide to add a start date prior to the first date in the table, I am having issues. I tried to do a DLookup and return the earliest date with a Min Function, but I get an error stating that the aggregate function in a where clause can't be in a DLookup.
Here is the code I have:
Private Sub BegDate_BeforeUpdate(Cance
l As Integer)
On Error GoTo BegDate_1
Dim strSQL As String
Dim PDate As Date
Dim OldEndDate As Date
Dim CorEndDate As Date
Dim OldBegDate As Date
Dim Cnt As Integer
Cnt = 0
PDate = Me.BegDate - 1
Me.ID = Me.NO & Format(Me.BegDate, "yyyymmdd")
DoCmd.SetWarnings (False)
OldEndDate = DLookup("[EndDate]", "[AWPPricetbl]", "[NO]= " & Me.NO & " and #" & Me.BegDate & "# > BegDate and #" & Me.BegDate & "# < EndDate")
If OldEndDate = #12/31/9999# Then
strSQL = "UPDATE AWPPricetbl SET AWPPricetbl.EndDate = #" & PDate & "# WHERE (((AWPPricetbl.EndDate)=#1
2/31/9999#
) AND ((AWPPricetbl.[NO])= " & Me.NO & "));"
DoCmd.RunSQL strSQL
Me.EndDate = #12/31/9999#
Else
CorEndDate = Me.BegDate - 1
strSQL = "UPDATE AWPPricetbl SET AWPPricetbl.EndDate = #" & CorEndDate & "# WHERE (((AWPPricetbl.EndDate)=# " & OldEndDate & "#) AND ((AWPPricetbl.[NO])= " & Me.NO & "));"
DoCmd.RunSQL strSQL
Me.EndDate = OldEndDate
End If
BegDate_Exit:
DoCmd.SetWarnings (True)
Exit Sub
BegDate_1:
Cnt = Cnt + 1
Me.EndDate = #12/31/9999#
If Cnt = 2 Then
Resume BegDate_Exit
Else
OldBegDate = DLookup("[BegDate]", "[AWPPricetbl]", "[NO]= " & Me.NO & " and Min(BegDate)")
Me.EndDate = OldBegDate - 1
Resume BegDate_Exit
End If
End Sub
do I have to write a routine to look this up or is there another way to perform this task?
Start Free Trial
