Date Question

[joewhitt]

I'm pretty new to Access but have worked through most of my problems. Now I'm just trying to make it "dumber" for users.

Scenario: Built a database to store information on a non-profit food closet organization. People come in and are either new for the year (everybody is new in the month of January) or have been there before. I have made a report to show new people and old people broken up.



Problem: When going through error check. The only way this works is if the new person isn't checked for the present month, then run report to get numbers, then go back in a mark them for the month. The only other way i can think to do it is have a separate report for each month.

My query has New: IIf([Jan]="No" And [Feb]="No"...,1,0) and so on as a field.
Question: is there a way to have it not count the current month and update that code each month?

Any help will be appreciated.

[June7]

What is it you are error checking for? Why shouldn't new person be checked for the current month?

[joewhitt]

I'm just eliminating human error. By the way I have my If statement, when a new person goes through the line and the helper enters their information for the first time and they go ahead and check that month. When you run the report it wouldn't recognize that contact as a new person.

I just want to know if there is a way to ignore the current month regardless of if it is checked or not.

[June7]

Bad suggestion, removed comments.

[jscriptor09]

"My query has New: IIf([Jan]="No" And [Feb]="No"...,1,0) and so on as a field.
Question: is there a way to have it not count the current month and update that code each month?"


I don't quiet understand the question but I can tell you this:
This month in VBA is: MonthName(Month(Date))
Next month in VBA is: MonthName(Month(Date) + 1)

May be you can use to do what you are trying to do!

[joewhitt]

I have a table called "Attended", in that table are twelve yes/no fields (each one labeled by month). A person that needs food might not show up every month. The state requires that each month you have numbers for new and for recurred(old). If you have been there any month before you are counted as old.

Since it is voluntary work there might be a different person every month that enters the data. If a new person, wanting food, comes in and the volunteer enters info and then checks the current month(lets say July).

By my query which creates a new field called New: (with an if statement checking all twelve months.

I know i can just leave the current month out, but i just don't want to change the code every month to exclude whatever month i am in.

[June7]

I tested some data and think this works:
SELECT * FROM Attended
WHERE (((IIf(IIf(Month(Date())=1,"",[Jan]=No) And IIf(Month(Date())=2,"",[Feb]=No) And IIf(Month(Date())=3,"",[Mar]=No) And IIf(Month(Date())=4,"",[Apr]=No) And IIf(Month(Date())=5,"",[May]=No) And IIf(Month(Date())=6,"",[Jun]=No) And IIf(Month(Date())=7,"",[Jul]=No) And IIf(Month(Date())=8,"",[Aug]=No) And IIf(Month(Date())=9,"",[Sep]=No) And IIf(Month(Date())=10,"",[Oct]=No) And IIf(Month(Date())=11,"",[Nov]=No) And IIf(Month(Date())=12,"",[Dec]=No),1,0))=1));

[joewhitt]

Awesome, that worked!!