Help with date selection in forms and reports.

[stevekos07]

I should know this but I just can't think of the solution .

I have a report which is a call sheet that returns a list of clients to be called based on a query expression as follows:

CallToday: IIf(Date()>=[NCUFN_Date]
Or Date()=[NoCallFrom]
Or Date()=[NoCallFrom2]
Or Date()>=[NoCallFrom] And Date()<=[NoCallTo]
Or Date()>=[NoCallFrom2] And Date()<=[NoCallTo2]
Or Weekday(Date(),1)=1 And [Sun]=True
Or [Status]=2
Or [Status]=3
Or [Status]=4
Or Weekday(Date(),1)=2 And [Mon]=True
Or Weekday(Date(),1)=3 And [Tue]=True
Or Weekday(Date(),1)=4 And [Wed]=True
Or Weekday(Date(),1)=5 And [Thu]=True
Or Weekday(Date(),1)=6 And [Fri]=True
Or Weekday(Date(),1)=7 And [Sat]=True
Or [P/H]=True And DLookUp("HolDate","tblPublicHolidays","HolDate=Date()"),"No","Yes")

As you can see it's quite a lengthy expression but not too difficult when broken down.

As you can see, this expression is based on today's date - Date(). It produces a call sheet for today and shows which clients need a call today based on all the variables shown.

What I want to do is to be able to print a call sheet for any future date.

How do I manage this? I was thinking of referring to a field on the report or form itself, or to another field in the query. What would be ideal is for a value entry parameter in the query, but I can't think of how to set that up.

As always, your assistance will be greatly appreciated!

 

[pbaldy]

I would have the user enter the date on a form, and change your references to Date() to

Forms!FormName.TextboxName

 

[stevekos07]

I would have the user enter the date on a form, and change your references to Date() to

Forms!FormName.TextboxName

That looks like a perfect solution.... except.... when I insert the textbox reference instead of the Date() reference for the date I get a message saying that the expression is too long and will be truncated!

What I am thinking of is separating out a section of the expression and placing it in a second expression field.

Do you think that will work?

(I'll try it and see!).

 

[pbaldy]

That is a long expression as-is, and there's a limit to how long they can be. One alternative is to create a public function and call it from the query. You can either pass all the fields as inputs, or pass the key field as an input and have the function get the field data.

 

[stevekos07]

That is a long expression as-is, and there's a limit to how long they can be. One alternative is to create a public function and call it from the query. You can either pass all the fields as inputs, or pass the key field as an input and have the function get the field data.

This is where my limited knowledge of VBA programming shows up .

If you have the time, could you give me an example of how you might do this?

I am in the process of sourcing a good course in VBA for Access but for now I usually have to either rely on the standard tools or have had guidance from more advanced users to assist.

 

[pbaldy]

This should get you started:

http://www.baldyweb.com/Function.htm

 

[stevekos07]

Thanks Paul. If I understand this correctly, I can copy the expression into the function after that first naming line? Is it as simple as that?

 

[pbaldy]

Sorry, no, not as simple as that. If you pass all the values to the function, it could be close I suppose. I'd use an If/Then block, and separate each test, since any one met is enough (they could all be one test as they are, but then they all get evaluated). If you're still using the form (passing the date as an input would make the function more flexible):

Public Function Blah(...) As String

Dim InputDate As Date

InputDate = Forms!FormName.TextboxName 

If InputDate  >=[NCUFN_Date]  Then
  Blah = "No"
ElseIf InputDate >= [NoCallFrom] And InputDate <= [NoCallTo] Then
  Blah = "No"
Else
  Blah = "Yes"
End If

End Function

There may be a better method that's escaping me; I could be having late-afternoon brain cramps.

 

[stevekos07]

Thanks for the suggestion Paul. I will do some more study into coding in VBA for these kinds of situations. For the time being I was able to overcome the problem this time by renaming the referenced objects to shorter names and rewriting some of the repetitive Or statements (e.g. instead of [Status]=2 OR [Status]=3 OR [Status]=4 I just rewrote that to [Status]<>1)

This way I was able to shorten the expression enough to get it to pass. I do understand that it is a work around for this situation but not the most efficient or flexible way for this kind of situation .

Cheers,
Steve.

 

[pbaldy]

No worries, glad you got it working.