Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Posts
    101

    Unanswered: IF time function

    I have a button where it brings runs a macro which applies a filter based on the criteria on the query, 10-12pm 12-2pm etc....what i would like would be to have one button which brings back the relevant results for the filter based on the time right now?
    any help appreciated

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    are these times stored as text, or as date\time?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Sep 2008
    Posts
    101
    stored as text i.e "10-12pm" but the date is stored as a valid date

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok.
    Ideally this should have been stored as time (a start Date\Time and an end Date\Time). You should store data in its correct data type. Time is not text.

    Also, this is not atomic - first normal form states that the content of columns should be one single thing - you are storing a start and end time in a single column.

    So it could have been simple however now you need to:
    1) Parse the text and extract the start and end times
    2) You then need to convert these (and your current time reading) to compatible datatypes. I would suggest Date\Time
    3) Compare the start and end times with the current time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Sep 2008
    Posts
    101
    how would you compare the current time with the start/end time once i change the data?

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Use the BETWEEN operator - the current time would be between the start and end time.

    I've just checked and lucky for you text can be implicitly converted to time in Access.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Sep 2008
    Posts
    101
    excellent, would that be an update query of some sort? regarding the BETWEEN operator, how would the query look?
    as i would need it to do something like checked the current time and see what its between

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    It would be a select, because you want to return rows, not alter them - correct?

    Why don't you have a stab at the BETWEEN operator? Look it up in Google and see how you go. It will stick better if you figure it than if I do it for you.

    I think your hardest task will be parsing the text....
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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