DateAdd function problem. 1

[KenGoonan]

I have an Access database that tracks resident population counts on a daily basis. I have a query problem with the DateAdd function where it won't count Saturday. It counts the population every day of the week fine except Saturday. Saturday is blank.

There is a Entry Date and an Exit Date and I use an expression to fix an Effective Begin Date and an Effective End Date that fixes the reporting period beginning and end dates. Some of the Entry dates are prior to, equal to, or after the start of the report period (Sunday).

Some of the Exit dates are before or equal to the report period end date, or null (Which means they are still in residence. The reporting time frame is Sunday through Saturday. There are three categories, 1,2,3, and two gender types, M, and F.

I use the DateAdd function to increment the days, e.g. DateAdd("d",0,([Enter Start Date])) This should equal Sunday. DateAdd("d",1,([Enter Start Date])) should equal Monday, etc.

If the criteria for category and gender are met a 1 or if not a 0 is placed in the row under the appropriate day. The columns are summed in a report giving the daily population count for each day of the week by category and gender. This all works fine until DateAdd("d",6,([Enter Start Date])) which should equal Saturday. I get no value returned for Saturday.

Here are the expressions to count Friday and Saturday:

FriMale: IIf([tbl-Client Information]![Gender]="Male",IIf([EFF_BGN]<=(DateAdd("d",5,[Enter Start Date])) And [EFF_END]>=(DateAdd("d",5,[Enter Start Date])),IIf([tbl-Client Information]![CourtType]="1" Or [tbl-Client Information]![CourtType]="2",1,0)))

SatMale: IIf([tbl-Client Information]![Gender]="Male",IIf([EFF_BGN]<=(DateAdd("d",6,[Enter Start Date])) And [EFF_END]>=(DateAdd("d",6,[Enter Start Date])),IIf([tbl-Client Information]![CourtType]="1" Or [tbl-Client Information]![CourtType]="2",1,0)))


Can anyone help on this.

Thanks,
Ken

 

[dhookom]

Do your BGN and END dates include a time portion? Have you tried to query for just the Saturday dates?

What is your table structure and what are you attempting to do? I think there may be a much simpler solution but I can't tell based on your question.

Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ck1999]

Are there actual records that meet the requirements of satmale. Are there actual values for EFF_end or are they all null.
Does this not work for the current week and any past weeks?


ck1999

 

[KenGoonan]

The result I am trying to get is a daily census count of persons from three categories by gender for each day of the week - Sunday through Saturday. Entry and Exit dates can be before, equal to, or after the report period start and end dates.

 

[ck1999]

It seems to me that the reason you are getting no results has to be in your
[EFF_END]>=(DateAdd("d",6,[Enter Start Date]))

statement.

So the question would be is what is the values of your [eff_end] you said "you used an expression to fix this" so I am guessing you calculated it?

ck1999

 

[KenGoonan]

After running a test query with the DateAdd function to see what dates return for each day of the week based on my EFF_BGN and EFF_END date expression, it's obvious there is a flaw in it. The dates of the days of the week that return are not correct using the DataAdd funtion.

Here is the expression I used for the calculating the dates:

EFF_BGN: IIf([tbl-Client Information]![Entry Date]<[Enter Start Date],[Enter Start Date],[tbl-Client Information]![Entry Date])

EFF_END: IIf([tbl-Client Information]![Exit Date]<[Enter End Date],[tbl-Client Information]![Exit Date],[Enter End Date])

Any Suggestions?

 

[dhookom]

I would create a table of all dates:
[tt][blue]
tblDates
==============
TheDate date/time
[/blue][/tt]
Add records for every date needed into this table. This is fairly easy to do using Excel.

Then create a query with [tbl-Client Information] and tblDates with no join lines. Set the criteria under the field [TheDate] to:
[tt][blue]
Between [Entry Date] and [Exit Date][/blue][/tt]
This will generate one record per date in the range for each client. These can then be grouped and counted by [TheDate].


Duane MS Access MVP
Now help me support United Cerebral Palsy

 

[ck1999]

Can you give examples of the results you are getting and the input used to get those results from your test query.

ck1999

 

[KenGoonan]

I have solved the problem. Thank you all for being interested in helping me. It's nice to know you can get help online, and quickly I might add. I'll be back for sure. I will post my solution on Monday, so if anyone who has a similar problem doing what I am doing they might benefit from the solution.

Basically I had to eliminate the DateAdd function from the last day - Saturday, because the end date was entered as the report end date and I think this confused Access and it gave back no results. I think the DateAdd function moved the end date 1 day ahead and therefore didn't count Saturday correctly. I also had my start date and end date out of order in the query. I needed to have the end date first. Once I put the them in the correct order and removed the DateAdd function from the last day it calculated the populations for each day by category and gender correctly including Saturday.

Duanne, your suggestion about the [tblDates] is one I can use in other queries. Thanks, I never thought of that. It does work for some things we do.

Cheers,

KenGoonan

 

[KenGoonan]

The problem, I discovered, was the DateAdd function on the end date day. Once I removed it from the expression it worked fine. The function in the first day date didn't make any difference since is 0.