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:

tblWrapAuths:
WrapAuthID (PK)
intClntID (FK)
AuthNumber
dtmStart
dtmEnd

Services provided are stored in:

tblServicesProvided:
intServiceID (PK)
intClntID (FK)
dtmStart
intServiceLocID

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"
Else
MyAuthNum = DLookup("[AuthNumber]", "tblWrapAuths", "[intClntID] = " & Me.txtClntID.Value & " And [dtmStart] <= " & Me.txtStart.Value & " And [dtmEnd] >= " & Me.txtStart)
End If



Me.txtAuth.Value = MyAuthNum

End Sub

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.
Any ideas?
Is there any other way to retrieve the AuthNumber for the date header that matches the criteria?

Try enclosing the explicit date values used in the DLookUp with the date delimiter (#) ...

I tried your suggestion and I recieved the error:


Run-time errpr’3075’:
Syntax error in date expression ‘[intClntID]=29 And #[dtmStart]# <= 11/10/2008 And #[dtmEnd]# >= 11/10/200’.


What's strange is that the values in this expression correspond to the correct values except for the final date value 11/10/200 should be 11/10/2008. It's strange since both date values come from the same control. I noticed that in my original expression I had Me.txtStart.value then Me.txtStart. I tried adding the .value to the second expression and got the same exact error.

Anyone no why it would be changing the year from 2008 to 200 ?

Explicit date values are 11/10/2008 and 11/10/2009, not the Field (value). You need the date delimiter on 11/10/2008 and 11/10/2009, not [dtmStart] and [dtmEnd].

Beware that the explicit date values need to be in the format "mm/dd/yyyy" (or unambiguous formats) and not "dd/mm/yyyy".

I haven't had time to work on this again till now.
I'm not sure Van understands my previos post. In the error I recied

Run-time errpr’3075’:
Syntax error in date expression ‘[intClntID]=29 And #[dtmStart]# <= 11/10/2008 And #[dtmEnd]# >= 11/10/200’.

The explicit date values in this error message (11/10/2008 and 11/10/200) came from the Me.txtStart.Value in my code (NOTE the 11/10/200 is NOT a typo).
Nowhere in my code do I have actual date values the dates all come from fields and controls. I tried to wrap Me.txt.Start in the date delimiter and got a complie error:

Expected: expression

What Van means is that your # delimiters are in the wrong place. It should look like this.



Which can be shorted using the NZ() function to



Note the .Value method is unnecessary here.


Edited by: SerranoG on Mon Jan 5 16:40:44 EST 2009.

AHH HA

It's working perfectly

You guys Rock!!!!!!!!




Be Well
Eddy

You're welcome... Glad we could help...