Solved Aesthetic boxes on a report

[donkey9972]

I saw that after I posted the DB to here. I have since corrected it. It was supposed to be 1 to 31, simple typos from my fat fingers.

 

[June7]

Okay, instead of VBA, consider this as RecordSource:

SELECT tbl_DailyRandoms.ID, Days.D, tbl_DailyRandoms.[1A], tbl_DailyRandoms.[1B]
FROM tbl_DailyRandoms RIGHT JOIN (SELECT DateSerial(Year(Date()), Month(Date()), [ID]) AS D FROM tbl_DailyRandomsDummy) AS Days ON tbl_DailyRandoms.D_Date = Days.D;

It is hard-coded for the current date but suppose that could be modified to reference a form for year and month parameters. I am doing some more testing.

 

[donkey9972]

So from a query?

 

[June7]

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.

 

[donkey9972]

Well for what my date is set as I had to modify it a little for the date

SELECT tbl_DailyRandoms.ID, Days.D_Date, tbl_DailyRandoms.[1A], tbl_DailyRandoms.[1B]
FROM tbl_DailyRandoms RIGHT JOIN (SELECT DateSerial(Year(Date()), Month(Date()), [ID]) AS D_Date FROM tbl_DailyRandomsDummy)  AS Days ON tbl_DailyRandoms.D_Date = Days.D_Date;

But it does work and starts off with the current year. Which part is the hard coded part as you stated? is that the Year(Date())? Also I noticed it is now giving an extra day at the end being 2/1/25, giving it 32 fields. But sadly in the end I need those date fields to be blank until an actual date is entered. I know what I am needing done is really complex. And I am really far in the deep end here. I have 3 forms I have to do with this requirement. which once I get this one, which is the easiest of the 3 done, then the others will simply be modification to make it work for them. I do appreciate the help and guidance you have provided June7. I am learning some things.

 

[June7]

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 dates, can have expression in textbox: =IIf(IsNull([1A]), Null, [D])

If you want 31 rows regardless, run report before any data is input for new month and don't apply filter, just the textbox expression.

 

[donkey9972]

Ok, I am taking this one step at a time, right now I am trying to get the date field to be empty. So I have put the IIF statement you made, and put it in the D_Date field. Renaming the [D] to txtDate as my field is named. It simply gives me #ERROR in that field. So i put a new text box in, put that same IIF statement in it, modified for my field. It works correctly now. I just need to hide my original Date field and use the new text box as my field. So mission accomplished on that one woo hoo.

 

[donkey9972]

Ok, I need to back up a step. So when I go into the report view after getting this working as I stated. The dates I put in the form are 1/1/25 and 1/2/25. However, when it is in print preview it only shows 1/1/25 for both entries as the date. I am confused as to why this is doing this.

 

[donkey9972]

Nevermind I figured it out. I had moved the original date field to a portion of the report that was not the detail section, so it was limiting it.

 

[donkey9972]

Oh that is working so nice, it meets more than one of my things I wanted to accomplish. If someone forgets to input a date then it is skipping a line as I needed it to. then if you go back to the form and input the missing date it is filling it in the correct spot on the report.

 

[June7]

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.

 

[donkey9972]

June7 you are correct, I just haven't made a lot of reports, I am used to just using that term regarding things on forms and other areas. As I said though I am learning. I will adjust myself.

I am uncertain of what you mean on this statement you said "If you want 31 rows regardless, run report before any data is input for new month and don't apply filter, just the textbox expression."

 

[June7]

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 then run report for November, December 1, 2024 row will not be blank.

Do you want 31 rows, always?
What if you want to run report for way back in March 2024 even though it is now Jan 2025?
You have to make decisions based on your needs and business processes.
To filter or not to filter, that is the question!

 

[donkey9972]

So now I understand what you mean by run the report before any data is input. Thank you for clarifying that.

Good questions all of them. Answers to them are as follows. I am not always the only person that will run these reports, and the other users might not always remember to run the report on the last day of each month. Yes it needs to be 31 days always. And it is always a possibility that the reports from the past need to be pulled up and still have 31 rows on them even if it is a month like February.

 

[June7]

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 for the year/month.

 

[donkey9972]

OK, I will see what I can come up with. But thank you for all the help you provided today.

 

[June7]

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 be replaced with references to form controls.
I used Val() in DateSerial function because it did not recognize inputs as numbers.

SELECT DateSerial(Val([yr]), Val([mo]), tbl_DailyRandomsDummy.[ID]) As D, [1A], [1B], Data.ID
FROM tbl_DailyRandomsDummy LEFT JOIN
(SELECT tbl_DailyRandoms.ID, D_Date, tbl_DailyRandoms.[1A], tbl_DailyRandoms.[1B]
FROM tbl_DailyRandoms WHERE Year(D_Date)=[yr] AND Month(D_Date)=[mo]) AS Data
ON Data.D_Date = DateSerial(Val([yr]), Val([mo]), tbl_DailyRandomsDummy.[ID]);

Now use textbox expression and you always have 31 rows.

 

[donkey9972]

I was able to put this together as you have instructed and it works as intended. I am trying to prevent use input for this. The reason being is later on I am automating the processing of the reports and stopping the ports from processing in the middle to input a month and year are not feasible. But I do appreciate you providing this response before you slept.

 

[June7]

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.

 

[donkey9972]

I understand, but the users that use this DB just want a 1 button click solution. Click a button and only the current month/year come up with nothing extra on it.