Search results

The [yr] and [mo] parameters are dynamic. They allow the viewing of data for any year/month regardless of when report is run. In this example those are popup input prompts but could instead reference controls on form for the inputs. VBA complicates. I have also read that VBA behind reports can...

You want Subtract to be the result? You want the difference between consecutive records? This not really a "running" value. This is not simple in Access. Review http://allenbrowne.com/subquery-01.html#AnotherRecord Consider: SELECT TableHours.ID, TableHours.NewHours, TableHours.DateAdd...

Sorry, must be in report or group header/footer, not page. I see you are using VBA approach and not the query I suggested. All dates are displayed. Didn't you want to hide ones without data?

Aggregate functions cannot reference controls, only fields from recordsource. =Sum([1A]) =Sum([1B]}

You need to show report query and exact expression used in textbox. Again, can provide file.

Use =Sum([fieldname}) in a textbox in header or footer section.

You did not try CROSSTAB sql I showed? All employees list because of LEFT JOIN. To only show employees with deliverable data use INNER JOIN. Consider showing all employees then use query as source for report and apply filter to report. Regardless of which SQL is used, you need a JOIN clause...

Your query shows ID fields but sample data you posted does not. And the numbers shown for status categories don't reconcile with the Deliverable data. I built tables and added records to agree with numbers given. Consider this SQL: TRANSFORM Count(Deliverables.JobID) AS JobCount SELECT...

Sample data could be helpful. Build tables in post or attach file (instructions at bottom of my post). Query with multiple tables and no JOIN clauses causes a Cartesian association of records - all records from each table associate with all records of other table(s), greatly multiplying the...

So why are you using wrong query? Why is there no report?

When I use .Send, email is sent immediately, not just sitting in Outbox.

Then better make sure they understand report must always be run on last day of month and NEVER any other time (no "rare occasions"). So, what do you mean by "automate"?

Even if you have an "automated" process, query should probably still refer to form controls for parameters. Controls can have default values based on current date. This allows flexibility to run report on those rare occasions it is outside period of report and someone has to provide year/month.

Well, I couldn't even get to bed before this idea came to me. Will never sleep if I don't get it expressed here. Change the query around. This SQL cannot be built or viewed in query builder Design View only SQL View because of non-standard JOIN clause. [yr] and [mo] are input prompts that can...

Then I will have to give it some more thought tomorrow. Probably a more complex IIf() in textbox although now I think that might also have to be used for all textboxes because also need to suppress data for those extra dates. Suggest you look at building a form where user can select criteria...

Because the extra days are calculated at the end. Say you want to run report for November 2024, run the report on November 30 and the December 1, 2024 date will not have data. Your report would have 31 rows and December 1, 2024 row will be blank. However, if you enter data for December 1, 2024...

Did you read post 12?

You are using "field" when really mean "control". Controls and fields do not have to have same name. In fact, my practice is to name controls different from fields, like: tbxDate, cbxDate, lbxDate.

Hard coded to use current date: Year(Date()), Month(Date()) And February could show 3 extra dates. So, would need filter criteria to restrict to specific year/month. This can be applied to report when opening. The number of rows would be same as number of days, not 31. To prevent display of...

Could build SQL with VBA and set RecordSource (as you are currently). I am trying to eliminate VBA. Why don't you build query object and see what it looks like. Fix data in table so dates are unique.