+ Post New Thread
Results 1 to 13 of 13
Office Software Thread, Access - Date Query in Technical; I'm back for more Access help - hopefully someone out there will answer. I have a database which contains a ...
  1. #1

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163

    Access - Date Query

    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
    Last edited by garethedmondson; 29th January 2009 at 08:08 PM.

  2. #2

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    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

  3. #3

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    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
    Attached Images Attached Images

  4. #4

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,686
    Thank Post
    824
    Thanked 2,570 Times in 2,187 Posts
    Blog Entries
    9
    Rep Power
    731
    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.

  5. Thanks to SYNACK from:

    garethedmondson (29th January 2009)

  6. #5

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    Quote Originally Posted by SYNACK View Post
    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

  7. #6

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,686
    Thank Post
    824
    Thanked 2,570 Times in 2,187 Posts
    Blog Entries
    9
    Rep Power
    731
    That would probably be easiest, can you attach it to a post? You may need to rar or zip it.

  8. Thanks to SYNACK from:

    garethedmondson (29th January 2009)

  9. #7

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    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
    Attached Files Attached Files

  10. #8

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,686
    Thank Post
    824
    Thanked 2,570 Times in 2,187 Posts
    Blog Entries
    9
    Rep Power
    731
    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?
    Attached Images Attached Images
    Attached Files Attached Files
    Last edited by SYNACK; 29th January 2009 at 09:01 PM.

  11. Thanks to SYNACK from:

    garethedmondson (29th January 2009)

  12. #9

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    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

  13. #10

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,686
    Thank Post
    824
    Thanked 2,570 Times in 2,187 Posts
    Blog Entries
    9
    Rep Power
    731
    I have added it and a thumbnail better showing the report edge thing to my last post for you. Glad I could help

  14. Thanks to SYNACK from:

    garethedmondson (29th January 2009)

  15. #11

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    Where is it added? Am I being blind but I cannot see an attachment on your post.

    Whoopse - got it....

  16. #12

    garethedmondson's Avatar
    Join Date
    Oct 2008
    Location
    Gowerton, Swansea
    Posts
    2,200
    Thank Post
    936
    Thanked 315 Times in 184 Posts
    Blog Entries
    11
    Rep Power
    163
    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?

  17. #13

    SYNACK's Avatar
    Join Date
    Oct 2007
    Posts
    10,686
    Thank Post
    824
    Thanked 2,570 Times in 2,187 Posts
    Blog Entries
    9
    Rep Power
    731
    Quote Originally Posted by garethedmondson View Post
    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.

  18. Thanks to SYNACK from:

    garethedmondson (31st January 2009)

SHARE:
+ Post New Thread

Similar Threads

  1. Replies: 8
    Last Post: 15th December 2008, 03:41 PM
  2. Date of Birth
    By sparkeh in forum General Chat
    Replies: 2
    Last Post: 10th November 2008, 03:36 PM
  3. Formatting auto date in MS Access Reports
    By RabbieBurns in forum Windows
    Replies: 4
    Last Post: 24th June 2008, 02:41 PM
  4. Microsoft Access Help! Date Field
    By Pyroman in forum How do you do....it?
    Replies: 1
    Last Post: 2nd May 2008, 03:19 PM
  5. Finally set a date!
    By Gatt in forum General Chat
    Replies: 12
    Last Post: 16th January 2008, 03:57 PM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •