having problems with IIf and dates

[clean32]

i have a Table with a long list of dates
also a form with a date

part 1

what i want is 4 columns in a query based on these dates

column 1 form date and + 7 days
column 2 between form date=7days and form date +14 days
column 2 between form date=15days and form date +21 days
column 2 between form date=22days and last day of the month.

i was thinking for the last column, form date + 1 month - 1 day??

all help appreciated

 

[Galaxiom]

You just want the dates in those columns (called fields in Access)? Or are there other fields you need too.

Last day of the month is usually done with:

DateSerial(Year([somedate]), Month([somedate]) + 1, 0)

 

[arnelgp]

SELECT iif(datefield between forms!yourFormName!dateControl and dateadd("d",7,forms!yourFormName!dateControl), datefield, null) as column1, iif(datefield between dateadd("d", 8, forms!yourFormName!dateControl) and dateadd("d",14,forms!yourFormName!dateControl), datefield, null) as column2, iif(datefield between dateadd("d", 15, forms!yourFormName!dateControl) and dateadd("d",21,forms!yourFormName!dateControl), datefield, null) as column3, iif(datefield between dateadd("d", 22, forms!yourFormName!dateControl) and dateaserial(year(forms!yourFormName!dateControl), month(forms!yourFormName!dateControl)+1, 0)), datefield, null) as column4 from yourTableName

 

[clean32]

ok maybe i should start again, possibly i am not approaching the problem correctly

i have a fields ID Date

there could be multiple dates for each ID

i need to flip this around so that if the date falls within the first week it is shown in the first field. second week second field and so on.

if i do it how i was first thinking i would have each date in its own row, but i need 1 row for each ID and its corresponding dates in the same row but different feilds

 

[arnelgp]

in order for your request to be accomplished, each column must have at most 1 date, if there are two or more dates on same date range on a particular id, what shall we do? just get the first one and ignore the rest? this can be easily done through vba.

 

[Minty]

You sound like you might have some badly organised data. Can you show us your main table structure and some sample data and finally what you are expecting as a result?

 

[clean32]

in order for your request to be accomplished, each column must have at most 1 date, if there are two or more dates on same date range on a particular id, what shall we do? just get the first one and ignore the rest? this can be easily done through vba.

uummm very good question

how about if we forget about the date ranges and do it by day. i can make this look pretty in a report so it appears to be a week range.
but my thought would be that we would have to show all dates

ID 1 2 3 4 5 6 7 8 9 10 11 12 14 15 16 17 18 19 20 etc

XXX y y y

xx? y y y

if we can do it this way then i will just show the week by colour

 

[clean32]

there we go

 

[arnelgp]

what do you want to show on each column, the date field.
we can using crosstab query and partition() function to show the total count for 1-7, 8-15, etc. days.

 

[clean32]

what do you want to show on each column, the date field.
we can using crosstab query and partition() function to show the total count for 1-7, 8-15, etc. days.

ok number of days between and average days i can do easy.

just i have this report going to some area managers who only just know how to turn on there computers, let alone read a date. do the report has to be realy dum and in a format that they are accustom. or they will complain that they do not understand.with about 2500 records per month. and now they have asked for a report across months. hence the start date in the form.

i could do it all with a macro, condition setvalue, and just step though each record. But setValue has vanished???? problem 1,its a but clunky problem 2

and i was hoping the brains trust could so something better.

the report has two columns of data ( fields) ID and date. next field is 1st week. it has one or more dates and needs to be coloured yellow
if a record has a date in week 1 it will have a record in week 3, fortnightly schedule see. so in short if there are two yellow boxs side by side that makes them happy because thay can ring someone and have a grumble.

on my part its just a pain of a job to do every week, run multiple reports into excel bla bla, if i can get this to work its like a 6 hour job into a 30 mint job