Solved Aesthetic boxes on a report (1 Viewer)

donkey9972

Registered User.
Local time
Today, 09:34
Joined
May 18, 2008
Messages
193
Hi, I know this is probably not advisable, but I was wondering if it is possible to make blank boxes on a report, and as each entry gets filled in it takes up one line of the report. So I have a report that is due monthly, it has 17 entries in columns, and 31 days labelled on the left side. I know one piece of advise will be to simply use excel, but all the data is input into access, and the reports are emailed out, there is about 20 reports. all of them are coming from access except this one. The people receiving them do not want to have to deal with multiple attachments and asked me to try to find some solution, they want things nice and tidy all in one attachment. So is this possible? I have tried to put blank boxes on the report but all that happens is as the data is populated it pushes those boxes down on the report and setting the data on top of it. Anyway, here is to hoping.
 
I am unsure as to how these things should align.

I was wondering if it is possible to make blank boxes on a report, and as each entry gets filled in it takes up one line of the report.

OK, so an entry gets filled and it takes up one line of the report. What happens for entries that aren't filled for some reason, and what do these check boxes have to do with anything? Since what you want isn't that clear, I'm not going to say you can or cannot do it. But I know I can't do it because I don't understand it.
 
Ok, I will try to explain it a bit better. You know on excel you can format the boxes to fit into one page and be displayed as squares all across the page if there is data or not? Something like that but done on access without the use of excel so I can try to get this report inline with all the others that there are. I have attached a picture of an excel spreadsheet as an example.
 

Attachments

  • Capture.JPG
    Capture.JPG
    136.3 KB · Views: 57
Thank you for the link. I am working on another part right now, I was just trying to get the information available to me before I got stuck there. Once I am at that point I will delve more into it. I will mark this as solved for now, it looks like what I am wanting. Again thank you.
 
I would do it with a dummy table. It would have fields

DayLabel
col1
...
col17

In the daylabel field create records with values (Day 1, Day 2)
So this table would look like your image without anything in columns.
1. Create an update query "qryClearColumns" that sets all columns to null. Do this before running the report.
2. You will have to show us how your data is stored. Hopefully it is in a more normalized format. Then you run a query to get the records you want for the report and read the records. You then put the correct values into the table by code. If the data is not normalized then it can be done with an insert query.

If you make this dummy table and give us a sample of real data the code is pretty easy to show you.
 
It would seem to me that you could employ the .CanGrow and .CanShrink properties (both set to NO) to lock down control sizing on your report.
 
I would use a table of numbers [tblNums] with values from 0 to 30 (or much higher). Then assuming you have a form [frmDateSelect] with a text box [txtStartDate] create a query [qselDates] with this SQL:

Code:
SELECT  DateAdd("d",[Num],[Forms]![frmDateSelect].[txtStartDate]) AS AllDates
FROM tblNums
WHERE Num<=30;

Then, add this query to your report's record source with a join that includes all records from [qselDates].
 
Just as a thought, you should be able to create a fake query to generate your 31 days. Code would be something like
SELECT TOP 1 Date() AS Date1, Date()+1 AS Date2, Date()+2 AS Date3 ... to Date()+30 AS Date 31
FROM Tbl_ANY;
Replace the Date() with what ever starting date makes sense for you.
 
Ok, I am at this point now where I need to create these esthetic boxes on 3 separate report. I will need lots of help, so please bear with me. Right now I have created 1 table called DailyRandoms where the data is stored and a dummy table called DailyRandomsDummy with the same number of fields as my DailyRandoms and I only input dummy data into the date field giving it 31 entries. There is only 4 fields on each of these tables, ID, D_Date, 1A and 1B.

I am not exactly sure where to go from this point. I understand I need to do some kind of update query, but I have never made one of those, so I am not sure what needs to happen exactly.
 
Does the link referenced earlier offer guidance to do what you want?

No UPDATE query should be needed.
 
Last edited:
its does some, but what is confusing me is this portion "then change the report's record source to:" Am I supposed to change the "field1, field2, etc. to my actual field names?

I know I change the table names, but not sure about the rest. I know its because i am dense at times. But I am trying.
 
I just tried to input that as the record source for the report and it wants to build it as a query.
 
Yes, use your actual table and field names.

You typed SQL statement into RecordSource property? Why don't you build a query object to make sure it builds the desired dataset? Either save that query and then reference its name in RecordSource or copy/paste the working SQL.

As always, you can provide db for analysis.
 
Ok, I was able to finally get the boxes onto the report as I wanted. I am having 2 issues right now. The first of which is, I want each page of the report to be a different month. So basically if November was the first month of days entered I want that to be the first page, then December the 2nd page, etc.... So as of right now all dates entered are entered on the same page.

Second problem I am having is I need each day to be sequential. So Dec 1, should be in the first line then if nothing is entered until Dec 16, then that day should appear on the 16th line (if that is possible). Worst case I would like to try to get them one after the other, but prefer them to appear on the correct line. Anyway, some of this might just be a pipe dream.
 

Attachments

That does add a twist but most anything is doable with enough code.
Explain more about tbl_DailyRandoms.
Should date be unique? Dates should all be for one month? This table will be purged every month? If so, why are there 1 record for November and 2 for 1/3/2025?

Should report always show 32 lines?
 
Last edited:
Should date be unique?

The dates are all unique, there will never be a duplicate date. There will however be dates sometimes people forget to enter when the business is closed, like for Christmas and New years day, etc... So after the fact I usually go in there and input the missing days on the form. Nut the days I have to go in there and add after the fact I kind of need to try to figure a way to make sure they end up in the correct location on the report. So for example if I enter December 25, 2024 after Dec 26th has been entered, I still need it to appear in the entry before the 26th, which I know is a sorting thing. But with all the other things I am trying to have happen here it might make things even more complicated than they already are.
--------------------------------------
Dates should all be for one month?

All the dates entered should be sorted and grouped for each month/year in order of the 1st being the first record and the last day of the month being the last entry. So for November 2024 there should be 30 entries (provided no one forgot to input anything) and that should be the only thing on the page.
--------------------------------------
This table will be purged every month?

This table will not be purged every month, it will simply be historical data after that month has passed in case it is needed for reference for our contracting department.
--------------------------------------
If so, why are there 1 record for November and 2 for 1/3/2025?

The records that are in there were from when I was making the form, just trash data. I was making sure the records on the form acted the way I wanted them to.
 
Oh I missed that last question, my apologies, I need there to be exactly 31 lines. One for every day of the longest month. If there is only 30 days then there should be 1 blank line at the end.
 
I didn't realize I had 32 in there, I guess playing around with that number I left 32 in there by mistake. Putting 31 gives me the correct number of spaces/lines I need.
 
One more, why is there a 0 record in dummy table?
 

Users who are viewing this thread

Back
Top Bottom