Access - Date Query

[garethEds]

I'm back for more Access help - hopefully someone out there will answer.

 

I have a database which contains a list of hotel guests and the gifts they have claimed over a certain amount of time.

 

I now want to print a report on the 28th of each month to see what each member has claimed in that month - for example Marth 29th - April 28th and so on.

 

I have designed the report for it to print but am having trouble with the query that calculates this information. I've tried putting this:

 

Between Date() And DateAdd("m",-1,Date())

 

into the query criteria but cannot get it to show the results. Am I missing something? There should be at least one guest who has claimed gifts in the past month.

 

Any help appreciated,

 

Gareth

Edited January 29, 2009 by garethedmondson

[garethEds]

Well I come back hanging my head in shame. There should have been at least one record shown - but it seems there were not.

 

I set my clock back to 2008 - lo and behold it all worked and the information for 29 Dec 2007 - 29 Jan 2008 turned up.

 

Now - I need to get this into a report - having problems with page breaks at the moment.

 

Gareth

[garethEds]

Back again.....

 

The whole thing is working, but I cannot get the page break to work. Here is a screenshot of what happens.

 

The next record, which starts with an address should be on a new page with the logo header as well.

 

I'm sure I've done this before but cannot for the life remember.

 

I've put a page break in but it doesn't work as I believe it should. I've been reading about contitional page breaks but I'm not sure what exectly the condition would be (end of gift data I think - but how to reference that?)

 

Gareth

[SYNACK]

If you go into the properties for the Detail section of the report in design view and look under the Format tab there is an option to Force New page : after section. If you need to change the grouping order you can find "Sorting and Grouping" under the view menu. The combination of these should allow you to do what are after.

[garethEds]

If you go into the properties for the Detail section of the report in design view and look under the Format tab there is an option to Force New page : after section. If you need to change the grouping order you can find "Sorting and Grouping" under the view menu. The combination of these should allow you to do what are after.

 

Hi Matey,

 

Thanks for the information. I've done this. In Design View there is a section called 'Detail'. I have set this to 'Force New Page - After Section' but what happens then is where guests have more than one record in the Detail list it moves the second to a new page. Did that make sense?

 

Also a middle blank page is being generated. Not sure why because it all fits onto the A4.

 

I can send it over so you can see what I mean - if you want.

 

Gareth

[SYNACK]

That would probably be easiest, can you attach it to a post? You may need to rar or zip it.

[garethEds]

Here it is - When you open it - go to the Macros and click mcr_argraffu_28

 

I have been setting my clock back to 2008 for this to work as I have no 2009 data in there at the moment.

 

The macro will run the report rtp_28th_report which will show the page as it should be in print preview.

 

As you move through them you can see that the page logo header etc doesn't come up on each page and there are several blanks in there and random data on some pages.

 

Hope you can advise and it is appreciated.

 

Gareth

GJE_in_Progress.zip

[SYNACK]

Had a look and think that I have found the solutions:

 

The page is too wide hence the blank pages and needs to have the light gray background of the editing area moved in so that it is right beside the edge of teh logo, you should also set the margins in print setup closer to the edge of the page.

 

The force new page should be set on the footer of the card details section which you will need to enable in the grouping options page for the card details section. This makes it split in the right place.

 

Would you like me to upload the tampered with copy?

GJE_in_Progress.zip

Edited January 29, 2009 by SYNACK

[garethEds]

Hi - whilst I do not want you to do my work for me, I would appreciate seeing your changes so I can compare the two.

 

Again - much appreciated.

 

GJE

[SYNACK]

I have added it and a thumbnail better showing the report edge thing to my last post for you. Glad I could help

[garethEds]

Where is it added? Am I being blind but I cannot see an attachment on your post.

 

Whoopse - got it....

[garethEds]

Right - I see what you have done thanks.

 

Now - why isn't the page repeated every time? The logo is on the first page but not on the second/third and so on.

 

Is this how Access does it?

[SYNACK]

Right - I see what you have done thanks.

 

Now - why isn't the page repeated every time? The logo is on the first page but not on the second/third and so on.

 

Is this how Access does it?

 

I noticed that to and looked at if for a while before I realised it was by design. The logo that shows up on the first page is the report header which is the header for the whole report and not repeated. You will want to cut all of the stuff from that section and paste it in the page header section that is one below it, this one is repeated on every page. You can just resize the height of the report header to nothing by draging the bar up which will stop it from adding a space at the top of the first page of your report.