Need help to code 2 rules

[Jimcb8]

I have a table which I store visits by clients to our food pantry.
The table contains the following fields"

Date of visit date field
#members in family number field
Received shampoo yes/no field
Received toothbrush yes/no field

Shampoo rule is the client is entitled to shampoo every 4 week from today
Toothbrush rule is the client is entitled to a new toothbrush every 12 week from today

The client does not visit the panty every week although he could if desired.

Example of table data:
date Family# Shampoo toothbrush
12/01/2012 3 yes yes
12/15/2012 3 no no
12/22/2012 3 no no
12/29/2012 3 yes no
01/16/2013 3 yes no

I want to display a message to the user that the client is entitled to either shampoo or a new toothbrush or both.

Any thoughts?

help is much appreciated

Jim

 

[pbaldy]

Presumably there's also a client field? It you could use a DMax() or open a recordset to find the max date where each field is true and compare each date to today.

 

[Jimcb8]

Paul,
Thanks for the reply. Yes I do have a client id field in the visits table. I guess once I have the dmax date I can subtract it from today and see if it is 28 days (4 weeks) or 84 days of toothbrushes (7 times 12 weeks).
I'm not sure how to code this solution. Could you please show me an example.

Thanks so much for all your help.

Jim

 

[pbaldy]

Along the lines of

If DateDiff("d", Nz(DMax(...), #1/1/2000#), Date()) > 28 Then

Note the use of Nz() to handle if the client has never been in. More on the DMax criteria:

http://www.mvps.org/access/general/gen0018.htm

Your criteria would match the client plus the field being true.

 

[Jimcb8]

Thanks Paul, I'll study up and give it a try.

Thank you so much for your patience and help.

Jim

At this point I owe you at least 5 dinners

 

[pbaldy]

Good, I'm hungry!

Post back if you get stuck.

 

[Jimcb8]

Private Sub Ctl_HouseholdMembers_GotFocus()
If (DateDiff("d", Nz(DMax("[DateAttended]", "Dates of Distribution", "[Shampoo] = 'YES'")), Date) >= 28) Then
MsgBox "Entitled to Shampoo"
End If
End Sub
This is the code I am trying to implement.
I call up a client, then enter the number of family member
I set a breakpoint on the first if statement hit F8
I see that my date in the DateAttended field is today 01/31/2013 which is incorrect.
I should be getting 10/28/2012 in that field, since that is the last date where the Shampoo field is equal to YES

Any thoughts????

 

[pbaldy]

You didn't include the client in the criteria, and if the Shampoo field is a Yes/No field it would be:

"Shampoo = True"

 

[Jimcb8]

Private Sub Ctl_HouseholdMembers_GotFocus()
If (DateDiff("d", Nz(DMax("[DateAttended]", "Dates of Distribution", ("[Shampoo] = True" And "[ClientId] ='"&forms!HomePage!NavigationSubform.form!ClientId&"'")), Date) >= 28) Then
MsgBox "Entitled to Shampoo"
End If
End Sub

For some reason it does not like this syntax. I tried all kind of varions and I either get a compile error or type mismatch????? Can't figure it out.
Thanks again
Jim

 

[pbaldy]

Too many quotes. Try

If (DateDiff("d", Nz(DMax("[DateAttended]", "Dates of Distribution", ("[Shampoo] = True And [ClientId] ='"&forms!HomePage!NavigationSubform.form!ClientId &"'")), Date) >= 28) Then

 

[Jimcb8]

Private Sub Ctl_HouseholdMembers_GotFocus()
Dim Date1 As Date

Date1 = Nz(DMax("[DateAttended]", "Dates of Distribution", "[ClientId]='" & Forms!HomePage!NavigationSubform.Form!ClientID & "'"))

MsgBox "Entitled to Shampoo"

End Sub
The above statement will return a date in variable Date1 =01/31/2013 which is ok.

When I try to add the Shampoo criteria as
And [Shampoo]=True This give me a type mismatch
Tried many variations with " , with Nz around the and criteria etc no luck

Date1 = Nz(DMax("[DateAttended]", "Dates of Distribution", "[ClientId]='" & Forms!HomePage!NavigationSubform.Form!ClientID & "'" And [Shampoo]= True))

I have been working for hours on this and as you see I tried to break down the code from the more complex statement earlier into simple steps, but it still is no good.

Any thoughts. So sorry to be a pain to you.

Jim

 

[pbaldy]

Again a quotes problem. Try:

Date1 = Nz(DMax("[DateAttended]", "Dates of Distribution", "[ClientId]='" & Forms!HomePage!NavigationSubform.Form!ClientID & "' And [Shampoo]= True))

 

[Jimcb8]

Date1 = Nz(DMax("[DateAttended]", "Dates of Distribution", "[ClientId]='" & Forms!HomePage!NavigationSubform.Form!ClientID & "' And [Shampoo]= True"))

This is the syntax that worked. Finally!!!
Thanks so much for your help

Jim

 

[pbaldy]

No problem Jim!