Results 1 to 10 of 10

Thread: Microsoft Access Help

  1. #1
    Senior Member
    Join Date
    Aug 2004
    Location
    W Yorkshire
    Posts
    5,668
    Thanks
    85
    Thanked
    13 times in 11 posts

    Microsoft Access Help

    I've got a table with some dates in it, and I've written a query so that a dialog box comes up when run (it asks for TODAY's date).

    Now I've managed to show all the listings with the previous date of the day entered today (Using: <[Enter Date (In format: DD/MM/YYYY)]


    Which works fine for showing all previous dates... but what do I need to do for only showing dates before the last 30 days?


    Need it for school coursework, teacher doesn't seem to know how to do this!



    Thanks
    Last edited by XA04; 16-01-2006 at 10:59 AM.

  2. #2
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,660
    Thanks
    53
    Thanked
    381 times in 312 posts
    does ACCESS SQL support the DATEDIFF command ?
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  3. #3
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,660
    Thanks
    53
    Thanked
    381 times in 312 posts
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  4. #4
    Senior Member
    Join Date
    Aug 2004
    Location
    W Yorkshire
    Posts
    5,668
    Thanks
    85
    Thanked
    13 times in 11 posts
    Thanks for the reply.

    Can't test it on the computer I am using at the moment... but shall try it when I get home.

    It looks sort of what I'm looking for, but those dates seem to be fixed... and if I wanted to change it for the last 30 days I'd have to change the query everytime manually...


    Sorry if that's not very clear, I'll try to explain again..

    I have managed to make a query so that it shows ALL the dates before the date I enter into the dialog box. But what I want to do is so that BEFORE the last 30 days(not during the last 30 days.. anything before) all the dates are shown.

    It's basicly for an overdue payment system I'm making (school work).

    So what would I need to modify from what I've already got?:
    <[Enter Date (In format: DD/MM/YYYY)]

    '<' is for less than.. and the square brackets is for the message in the dialog box, which shows an empty box to enter data, so basicly if you entered todays date into the box the command would be:
    <16/01/2006
    and that works fine - and shows all the dates before today, but I want it before the last 30 days.

    If anyone can help that would be great.

    Thanks!

  5. #5
    Administrator Moby-Dick's Avatar
    Join Date
    Jul 2003
    Location
    There's no place like ::1 (IPv6 version)
    Posts
    10,660
    Thanks
    53
    Thanked
    381 times in 312 posts
    so you want to show files for a range of (getdate()-30) - again I dunno if access supports getdate either.
    my Virtualisation Blog http://jfvi.co.uk Virtualisation Podcast http://vsoup.net

  6. #6
    Treasure Hunter extraordinaire herulach's Avatar
    Join Date
    Apr 2005
    Location
    Bolton
    Posts
    5,618
    Thanks
    18
    Thanked
    172 times in 159 posts
    • herulach's system
      • Motherboard:
      • MSI Z97 MPower
      • CPU:
      • i7 4790K
      • Memory:
      • 8GB Vengeance LP
      • Storage:
      • 1TB WD Blue + 250GB 840 EVo
      • Graphics card(s):
      • 2* Palit GTX 970 Jetstream
      • PSU:
      • EVGA Supernova G2 850W
      • Case:
      • CM HAF Stacker 935, 2*360 Rad WC Loop w/EK blocks.
      • Operating System:
      • Windows 8.1
      • Monitor(s):
      • Crossover 290HD & LG L1980Q
      • Internet:
      • 120mb Virgin Media
    Access has pretty good date commands, I think something along the lines of Now()-<Function thats used to cast to a number of days>(30) shoudl work,. theres an example in the help IIRC

  7. #7
    Senior Member
    Join Date
    Aug 2004
    Location
    W Yorkshire
    Posts
    5,668
    Thanks
    85
    Thanked
    13 times in 11 posts
    Hmm, I think you guys are on the right track to what I mean now. But I'm still not sure how to implement what your saying.

    I'm at home now, here is a screenshot if it helps..



    Thanks

  8. #8
    Treasure Hunter extraordinaire herulach's Avatar
    Join Date
    Apr 2005
    Location
    Bolton
    Posts
    5,618
    Thanks
    18
    Thanked
    172 times in 159 posts
    • herulach's system
      • Motherboard:
      • MSI Z97 MPower
      • CPU:
      • i7 4790K
      • Memory:
      • 8GB Vengeance LP
      • Storage:
      • 1TB WD Blue + 250GB 840 EVo
      • Graphics card(s):
      • 2* Palit GTX 970 Jetstream
      • PSU:
      • EVGA Supernova G2 850W
      • Case:
      • CM HAF Stacker 935, 2*360 Rad WC Loop w/EK blocks.
      • Operating System:
      • Windows 8.1
      • Monitor(s):
      • Crossover 290HD & LG L1980Q
      • Internet:
      • 120mb Virgin Media
    Its actually DateAdd('d',-30,Now()), that gets you the date 30 days ago, and im sure you can manage the rest.

  9. #9
    unapologetic apologist fuddam's Avatar
    Join Date
    Nov 2005
    Location
    UK
    Posts
    1,948
    Thanks
    359
    Thanked
    275 times in 146 posts
    even easier:

    <Now()-30



    works in days regardless, unless you tell it otherwise

  10. #10
    Senior Member
    Join Date
    Aug 2004
    Location
    W Yorkshire
    Posts
    5,668
    Thanks
    85
    Thanked
    13 times in 11 posts
    Quote Originally Posted by herulach
    Its actually DateAdd('d',-30,Now()), that gets you the date 30 days ago, and im sure you can manage the rest.
    Can't seem to get that to work?

    Quote Originally Posted by fuddam
    even easier:

    <Now()-30



    works in days regardless, unless you tell it otherwise
    Yeah that works thanks.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Microsoft Support....
    By Devilbod in forum General Discussion
    Replies: 14
    Last Post: 09-04-2007, 02:42 AM
  2. Microsoft Access 2003 - Dynamic Time?
    By Jonny M in forum Software
    Replies: 3
    Last Post: 01-05-2004, 11:44 PM
  3. Microsoft Scousers!
    By XTR in forum General Discussion
    Replies: 10
    Last Post: 19-01-2004, 10:37 AM
  4. New RPC hotfix from Microsoft
    By Paul Adams in forum Software
    Replies: 12
    Last Post: 14-09-2003, 08:44 AM
  5. Microsoft Access Question...
    By TomWilko in forum Software
    Replies: 2
    Last Post: 24-08-2003, 12:53 PM

Posting Permissions

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