Total query with date/time fields

[Ruegen]

I have a query showing booking times. We consider booking time a single "booking"

There are up to 5 fields in the booking table - each is a date/time field (1PM show, 2 PM show etc)



I need to add these together for totals however if I make a calculation in the query say expre1: 1stTime + 2ndTime it will obviously add the values - I want to count the columns if they are not null

so if 1st and 2nd time are filled out on a record then that is 2 bookings for the show

but if 1st time is filled only then just 1 booking.

[Ruegen]

Just to make it more clear, each field row has a total of "count" so that it ads the number of count for showTime1st, 2nd etc. But I want to show a column that adds those counted rows together in a sum

[June7]

Use IIf expression.

IIf([CountOfTime1st]>0,1,0) + IIf([CountOfTime2nd]>0,1,0) + IIf([CountOfTime3rd]>0,1,0) + IIf([CountOfTime4th]>0,1,0) + IIf([CountOfTime5th]>0,1,0)

[Ruegen]

Use IIf expression.

IIf([CountOfTime1st]>0,1,0) + IIf([CountOfTime2nd]>0,1,0) + IIf([CountOfTime3rd]>0,1,0) + IIf([CountOfTime4th]>0,1,0) + IIf([CountOfTime5th]>0,1,0)

CountOfTime1st and the rest don't exist. They are made from ShowofTime1st but then total "Count" in the query. So If I try to add, the query gives me a parameter question.

I've got it working on the form, but the query doesn't work by itself.

[Ruegen]

See here

update also IIf([ShowTime1st]>0,1,0)+IIf([ShowTime2nd]>0,1,0)+IIf([ShowTime3rd]>0,1,0)+IIf([ShowTime4th]>0,1,0)+IIf([ShowTime5th]>0,1,0) just = 1 however if I count that I get 11...

[June7]

Sorry, I left 'Show' out of the field names. Try my suggested expression with the correct field names: CountOfShowTime1st, etc

[Ruegen]

Sorry, I left 'Show' out of the field names. Try my suggested expression with the correct field names: CountOfShowTime1st, etc

Yeah I tried, it didn't work but I just used ShowTime1st and then added them (as seen above) and then just put them into count instead of groupby and it seems to work - will let you know if it breaks. I have to add some shows with different times to be sure - but I've detoured on another puzzle (correct syntax for open form multiple parameters).

[Ruegen]

I changed to groupby for ​Expr1: IIf([ShowTime1st]>0,1,0)+IIf([ShowTime2nd]>0,1,0)+IIf([ShowTime3rd]>0,1,0)+IIf([ShowTime4th]>0,1,0)+IIf([ShowTime5th]>0,1,0) and it works

Update- it's not counting ALL the shows on separate bookings...



Ideally because there are two shows at time2nd and two shows at time1st for Australian Folklore there should be 4 shows in total.

um heh... I changed it to sum instead of count and it works

[Ruegen]

just so people can see..