The db I am working with tracks services provided.
The services are authorized with an authorization number that has a start date, end date and # of units authorized.
Authorizations are stored in:
Services provided are stored in:
The common field between these tables is the FK intClntId which resides in tblClnts (this is the table that holds client information; Last name, First name etc).
I have created a report that lists the services provided grouped by date of service ([tblServicesProvided].[dtmStart]) and subgrouped by location. The data source for the report is a query that retrieves information from tblServicesProvided & tblClnts (tblwrapAuths is not in the query). In the group header for the date I want to include the Authorization number ([tblWrapAuths].[AuthNumber]). This number would correspond to intClntID AND would have a start date less than [tblServicesProvided].[dtmStart] AND an end date Greater than [tblServicesProvided].[dtmStart]. I have txtboxes in the group header for ClntID (txtClntID) and the date of service (txtStart). I created an unbound text box in the group header called txtAuth. On the OnFormat event I attached the code:
Private Sub GroupHeader2_Format(Cancel As Integer, FormatCount As Integer)
Dim MyAuthNum As String
If IsNull(DLookup("[AuthNumber]", "tblWrapAuths", "[intClntID] = " & Me.txtClntID.Value & " And [dtmStart] <= " & Me.txtStart.Value & " And [dtmEnd] >= " & Me.txtStart)) Then
MyAuthNum = "None"
MyAuthNum = DLookup("[AuthNumber]", "tblWrapAuths", "[intClntID] = " & Me.txtClntID.Value & " And [dtmStart] <= " & Me.txtStart.Value & " And [dtmEnd] >= " & Me.txtStart)
Me.txtAuth.Value = MyAuthNum
There are authorization numbers that meet the criteria, however all the txAuth textboxes say “None” indicating no results matched the criteria. Not sure if the problem is in my DLookup or if there is a more basic issue I’m not seeing.
Is there any other way to retrieve the AuthNumber for the date header that matches the criteria?