Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Dates between a Period (Access 2000 / Windows XP SP2)

    Hi,

    I'm trying to do a very simple query to select dates and seem to be having a mind block! The query has two fields Date and Activity. I know I can select between two dates at run time with something like :- Between [Enter first date] And [Enter Last date]. Then if I fill in the first box with say 1/2/04 and the second box with say 1/6/06 It will list all the records that have an activity between these two dates.

    The dates can lie anywhere between 1960 and today and what I want to do is have a box into which I type say June (or 06 or 6)) , then for it to list every activity that has taken place in June. It might be nice to have another query that does the same as this one, but also allows you to select a year range.

    I realise that I could create three date fields for day, month and year, but there must be any easier way!

    I would be very grateful for any Ideas

    Best wishes
    Michael Peak
    Morecambe England

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    You don't have to split the date field into parts. Instead, you can add month and year as calculated columns to a query:

    TheMonth: Month([DateField])

    and

    TheYear: Year([DateField])

    where DateField is the name of the date field. (I wouldn't call it Date since Date is both a function and a data type, so it might cause confusion). You can now specify criteria for TheMonth and/or TheYear.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    Thanks Hans,

    Works perfectly. What I get so very frustrated about is the fact that the solution is so simple, but at least I know a lot more about functions as I looked up in Access help.

    Thanks again Hans and sorry to bother you on such a simple point

    Michael

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    No problem. The Lounge is there for both simple and complex problems. And what seems simple in retrospect, may be quite complicated if you don't know yet how to approach it.

  5. #5
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    Hans,
    Thanks for your understanding. So I will bother you with a slight complication. What you suggest works very well and gives the right number and I found the function you used (MONTH
    Returns the month, an integer from 1 (January) to 12 (December), that corresponds to a serial number.)

    However as it is now simply an integer it does not know that 1 is January and so on. Therefore you can't used format MMm to convert it back to a month. What would be nice would be the ability to have a box that says [Enter Month] and you put in Jul and get July, rather than have to put in 7.

    I've run through the other date functions and nothing springs out

    Regards Michael

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    I would do the following:

    1) Create a table tblMonths with two fields: MonthNo (Integer) and MonthName (Text)

    <table border=1><td align=center>MonthNo</td><td align=center>MonthName</td><td align=right>1</td><td>January</td><td align=right>2</td><td>February</td><td align=right>3</td><td>March</td><td align=right>...</td><td>...</td></table>2) Create an unbound form, and place a combo box on it with tblMonths as Row Source.
    Set the Column Count property to 2 and the Column Widths property to 0";1".
    Name the combo box cboMonths and save the form as frmParameters.

    3) Set the criteria for TheMonth to

    [Forms]![frmParameters]![cboMonths]

    The user can enter or select the month name, but the hidden first column will be passed to the query.
    You could do something similar for the years (but you'd need only one column there)

  7. #7
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    Hi Hans
    Well I have got it to work, but not quite as I expected! I didn't realise that I would have to put the value into the form first then click on the query. It's not too much of a problem I just thought that as I double clicked on the query either the form would open or I could type the month into the query as normal

    I have taken the liberty of attaching it as a zip just to see if I have done it the way you suggested

    Regards

    Michael

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    The idea is indeed to open the form first. In fact, I never let end users work directly with tables and queries, only with forms and reports. In the attached version, the frmParameters form is opened automatically. I have added a command button that opens a form based on the query, so it will only display records for the selected month.

  9. #9
    2 Star Lounger
    Join Date
    Jan 2001
    Location
    Morecambe England, Lancashire, England
    Posts
    105
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dates between a Period (Access 2000 / Windows XP SP2)

    Dear Hans,

    Perfect!

    I know it's easy to say after the event, but in my previous reply I nearly put I suppose that I could run it from a button on the form". And then thought No keep it short I may have done something wrong.

    Many thanks for your help. As usual, you have given me loads of Ideas to use else where

    Michael

Posting Permissions

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