Results 1 to 11 of 11
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Query Year (MSOffice 97)

    Hi

    when I run the query shown below if I select January as the month it shows only quotes to January 03, but if I Select February It shows records for 03 and 02.

    Ideally I would like just to show the current year.

    Can anyone help please
    If you are a fool at forty, you will always be a fool

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

    Re: Query Year (MSOffice 97)

    You'd have to put in a criterion for the year. So where do we get the year from?
    What is the relation between the Month and Date fields? Do they belong together?

  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Query Year (MSOffice 97)

    HiHans
    It takes the year from the form quots, I entered the following criteria in date and seems to work >01/01/03.
    Not sure if its the best solution, unfornatley I inherited the database from an former Italian employee and some of the table, form, and query names are not understandable.

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Query Year (MSOffice 97)

    I'm missing something here (what does "It takes the year from the form quots" mean?)

    An alternative is to add a calculated field Yr:Year([Date]) and set the criterion on this field: Year(Date()). This way, you will always select the records whose Date field value is in the current year, not just in 2003.

  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Query Year (MSOffice 97)

    Hi Hans

    I have attached a screenshot of where I beleive it comes from but there is also a table with the same name quots.

    I must point that I am a novice to Access. that is probably why I can't explain myself very well. to prove the point, I don't fully understand your calculated field option
    I know what a calculated field is. but do not fully understand the parameters you refer to.

    Sorry to try you patience

    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Query Year (MSOffice 97)

    Braddy,

    The form has a text box labeled Date, and a combo box (dropdown list) labeled Month. In your screenshot, the combo box displays the name of the month of the Date text box.
    Does the month name change automatically if the user changes the Date text box?
    Can the user select a month different from the month of the Date text box?

    I am asking this because you may have a problem if the text box and combo box act independently of each other.

  7. #7
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Query Year (MSOffice 97)

    Hi Hans

    Thank you for your patience, Both the date text box and the and the combo month box are independant of each other.

    Date text box is the day it is created and the combo box relates to an accumilation of quotes for that month.

    As said I inherited this database and it is poorly designed.

    Thanks again

    Please don't spend to much time on this problem
    If you are a fool at forty, you will always be a fool

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

    Re: Query Year (MSOffice 97)

    Hello Braddy,

    In the post that began this thread, you have a screenshot of a query with a criterion on the Month field. You also want to apply a restriction on the year. But now, it turns out that the Month field and Date field are not related to each other. Putting a restriction on the year of the Date field would mean that you have date conditions on two mutually independent fields. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    I would think that you either need to impose the month and year conditions on the Date field, or that you need a Year field that is related to the Month field.

  9. #9
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Query Year (MSOffice 97)

    Hi Hans

    Thanks for your reply as I said in another reply I used >01/01/03 and it worked, I get all the records that show the month I enter and date ie 21/02/03.

    I guess I will have to settle for this.

    Once again many thanks for your patience and understanding


    Braddy
    If you are a fool at forty, you will always be a fool

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

    Re: Query Year (MSOffice 97)

    We'll leave it at that then. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>.
    Put a reminder in your diary to change it to >01/01/04 next year...

  11. #11
    Silver Lounger
    Join Date
    Dec 2000
    Location
    Northampton, Northamptonshire, England
    Posts
    1,951
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: Query Year (MSOffice 97)

    Hi Hans

    Sure will

    Thanks
    If you are a fool at forty, you will always be a fool

Posting Permissions

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