Date Function

[joewhitt]

I don't know much about the Date() function and how to apply it.



Scenario: I have a table called Household which holds main information, all i need from this is the HouseholdID. I also have a table called Attendence which has twelve yes/no fields labeled for each month ([Jan], [Feb],...).

Problem: I need an IIf statement that asks if the current month(my variable)= Yes then return "Yes", else "No".

i.e., Picked Up Current Month: IIf([Jul]=Yes, "Yes", "No") but changes from month to month instead of twelve queries.

Any help will be appreciated.

[June7]

The Date function just returns the current date.

12 months is too many items for a nested IIf. Use Switch function, like:

SELECT Switch(Month(Date())=1,"Jan",Month(Date())=2,"Feb" ,Month(Date())=3,"Mar",Month(Date())=4,"Apr",Month (Date())=5,"May",Month(Date())=6,"Jun",Month(Date( ))=7,"Jul",Month(Date())=8,"Aug",Month(Date())=9," Sep",Month(Date())=10,"Oct",Month(Date())=11,"Nov" ,Month(Date())=12,"Dec") AS CurMon, Switch(Month(Date())=1,[Jan],Month(Date())=2,[Feb],Month(Date())=3,[Mar],Month(Date())=4,[Apr],Month(Date())=5,[May],Month(Date())=6,[Jun],Month(Date())=7,[Jul],Month(Date())=8,[Aug],Month(Date())=9,[Sep],Month(Date())=10,[Oct],Month(Date())=11,[Nov],Month(Date())=12,[Dec]) AS Test
FROM Household;

[joewhitt]

That didn't work. It included all records as a Yes, even though some are not.

I really just need it to return a list of households who have attended the current month. To filter my report of only the people who came that month and not the whole database.

[rpeare]

Do you have a table that is storing months this way?

HouseHoldID Jan Feb Mar Apr ....
1           Yes No  Yes  No
2           No  No  No  Yes

Why wouldn't your attendance table be set up with the

HouseholdID  AttendanceDate
1            1/1/2011
1            3/1/2011
2            4/1/2011

This would accomplish 2 things
1. You could update this table forever without having to reuse any fields (when the new year rolls over)
2. You could use the datepart("m",attendancedate) and datepart("yyyy", attendancedate) to see if a person had attended their monthly meeting for the current month or not

[joewhitt]

because of the users. households attend every month if they want, but user needs to know every month they have attended. (which i decided is a yes/no question, i dont know much about the date and its limitations). i only need the date because i want code to update every month. that way i just write it one time.

my if statement works but i dont want to write twelve of them.

[June7]

I did a test with data and records return with -1 or 0 as entered in the field returned by the Switch function. What I did not include in the query is a WHERE clause.

SELECT ID, Switch(Month(Date())=1,"Jan",Month(Date())=2,"Feb" ,Month(Date())=3,"Mar",Month(Date())=4,"Apr",Month (Date())=5,"May",Month(Date())=6,"Jun",Month(Date( ))=7,"Jul",Month(Date())=8,"Aug",Month(Date())=9," Sep",Month(Date())=10,"Oct",Month(Date())=11,"Nov" ,Month(Date())=12,"Dec") AS CurMon
FROM Household
WHERE (((Switch(Month(Date())=1,[Jan],Month(Date())=2,[Feb],Month(Date())=3,[Mar],Month(Date())=4,[Apr],Month(Date())=5,[May],Month(Date())=6,[Jun],Month(Date())=7,[Jul],Month(Date())=8,[Aug],Month(Date())=9,[Sep],Month(Date())=10,[Oct],Month(Date())=11,[Nov],Month(Date())=12,[Dec]))=True));

This query returns only those records that show attendance for the current month. So if I run this on last day of month I get records. If I run on first day I of course get nothing. If you want to run report to return data for a month other than that currently in, then need user input instead of the Month(Date()) input. Also, with your data structure, need a Year field, assuming you want to keep attendance info for all years. Then include the year as another criteria.

So if this does not work for you then maybe my data sample is different from what you have and I need a sample of your data to test. And maybe I don't understand what you want. If you want to return records that have Yes in any month field, then need an expression like: IIf([Jan]=Yes ... Or [Dec]=Yes,True, False). Then criteria for this woud be =True.

I agree with rpeare about data structure. What you seem to have is not normalized.

[rpeare]

because of the users. households attend every month if they want, but user needs to know every month they have attended. (which i decided is a yes/no question, i dont know much about the date and its limitations). i only need the date because i want code to update every month. that way i just write it one time.

my if statement works but i dont want to write twelve of them.

Anything you can do with your data structure you can do with a normalized data structure and probably more efficiently over the long haul. If June's query works as you want that's great, but there really is no good reason to store the data as you have, your querying, table updating, record addition is going to be far more effective with a normalized structure.

[joewhitt]

Thanks June7 that worked. That's twice you've saved me.

I like how you adjust your knowledge to fit the problem I had instead of showing me you are smarter and more experienced than I am.