Results 1 to 12 of 12
  1. #1
    Join Date
    Mar 2004
    Posts
    287

    Unanswered: Checking Data Field by Year

    I have a table field named DayDate which is dd/mm/yyyy

    I run a query to search by Name and Date but I want to filter this query in VBA to search the date field by year as well (or another field selected by the year on the form)

    Can anyone point me in the correct way of thinking please?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    write the SQL on the fly, or set a filter

    if you want a specific calendar year

    WHERE YEAR(mydatecolumn) = 2011

    if you want rows for March 2008
    WHERE YEAR(mydatecolumn) = 2008 AND MONTH(mydatecolumn) = 3

    if you are using a filter then effectively the filter parameters are the same as the WHERE clause

    Me.Filter = "YEAR(mydatecolumn) = 2008 AND MONTH(mydatecolumn) = 3"
    Me.FilterOn = True
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2004
    Posts
    287
    thanks that helps. but i can't seem to get the syntax correct - any suggestions;

    [Day1] = DLookup("[DayType]", "Qry_MainSelectStaff", (Year(DayDate) = "2008" And Month(DayDate) = "3"))

    I have attempted other varieties of the code but to no avail.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so what do you think is wrong with your code?
    what else have you tried
    examine your code and try to understand where or how it may be failing.
    as a hint its to do with literal text and how you present that to the dlookup
    Description of DLookup() usage, examples, and troubleshooting in Access 2000
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by NeilMansell View Post
    thanks that helps. but i can't seem to get the syntax correct - any suggestions;

    [Day1] = DLookup("[DayType]", "Qry_MainSelectStaff", (Year(DayDate) = "2008" And Month(DayDate) = "3"))

    I have attempted other varieties of the code but to no avail.

    Try:

    Code:
    [Day1] = DLookup("[DayType]", "Qry_MainSelectStaff", "Year([DayDate]) = 2008 And Month([DayDate]) = 3")
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  6. #6
    Join Date
    Mar 2004
    Posts
    287
    thanks that worked I thought I had tried that and I did look at the dlookup command earlier. thanks for the help everyone. damn "

  7. #7
    Join Date
    Mar 2004
    Posts
    287
    another/same problem with this -
    I now need to link these month or year dates to a combo box on the form.

    I have tried linking but get errors - whats the general rule for doing this?
    Or any code suggestions would help. I am learning here - honest!

  8. #8
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Like . . .

    [Day1] = DLookup("[DayType]", "Qry_MainSelectStaff", "Year([DayDate]) = " & me![YearSelectionCombobox] & " And Month([DayDate]) = " & me![MonthSelectionCombobox])*

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    new question, new thread please
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2001
    Location
    Chicago, Illinois, USA
    Posts
    601
    Quote Originally Posted by healdem View Post
    new question, new thread please
    That's silly.

    His "new" question is virtually identical to the old question, with one minor modification.
    Ken

    Maverick Software Design

    (847) 864-3600 x2

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by PracticalProgram View Post
    Try:

    Code:
    [Day1] = DLookup("[DayType]", "Qry_MainSelectStaff", "Year([DayDate]) = 2008 And Month([DayDate]) = 3")
    you would have found this solution a lot earlier if you tried to assign the criteria to a variable
    one of the reasons for suggesting this is that you can check the code compiles (ie has the right punctuation) and then examine what you are sending to the dlookup and make certain that it makes syntactical sense.. ie is valid SQL.

    the problem you had was that you didn't quote a literal value.

    incidentally, going back to your first post
    I have a table field named DayDate which is dd/mm/yyyy
    No you didn't
    you had a column (we assume) of datetime datatype, which when diosplayed on a form or report is formatted as dd/mm/yyyy, rather than asserting its dd/mm/yyyy.

    the difference is that you do not need to knwo how Access / JET stores datetime values and if you use the date time functions you dont' need to care overly much about how Access / JET store data, you can always extract what you want when you want in the manner you want
    ms access date time functions - Google Search
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Mar 2004
    Posts
    287
    To add;
    DayDate is the field in a table which is set to date format (dd/mm/yyyy).
    I also have a DayID linked to the each record, so 01/01/2011 may also have an ID of 12.

    What I am trying to do is display the data on an unbound form that links box1 bound to showing ID for date 01/01/2011, etc etc

    To make matters worse I want to limit the years shown by having a year selector at the top (combo). This displays 2010,2011,2012 so I want to be able to insert this year selected into the criteria for the box1, etc

    So it displays DayID on box1 which is linked to 01/01/year selected
    box2 links to 02/01/year selected, etc

    Does that make sense?

    As I am still ahving problems when attenmpting to link the year to the combo box!?
    The code works fine without the combo link, but as soon as I link to the combo it doesn't work! I have looked into the formats of the fields in question, even tried to make the year selected (2011) be converted into a date format "yyyy" but to no avail.

    This is now stopping me pushing ahead with this database so any help to speed up my progress would be most welcome.

Posting Permissions

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