Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2010
    Posts
    6

    Unanswered: Expression Builder: If DateValue

    Hello,

    I am trying to build an expression to compare 2 date/time fields and see if they took place on the same day.

    Below is what i have tried but it's returning an error.

    SameDate: IIf(DateValue([Closed Date (LocalTime)])=DateValue([Created Date (LocalTime)]),"Yes","No")

    Thank you in advance for your help,
    Rip22

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Might there be Null values in either of the fields (the ones that contain the inadvisable spaces and symbols)?
    Paul

  3. #3
    Join Date
    Mar 2010
    Posts
    6
    Thank you for your reply.
    The 2 fields I am comparing do not contain any null values,
    These fields are actually in Text Format Example: 40234.6881944444
    They were created by taking the orginal created date GMT and adjusting to local time.

    As I stated before I want to compare the 2 fields and return a Yes if they both took place on the same day and No if they do not.

    What do you recommend?

    Thanks for your help

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    I'm not sure the DateValue can handle the Text field. Try wrapping each in CDate():

    DateValue(CDate([Closed Date (LocalTime)]))
    Paul

  5. #5
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You could try:
    Code:
    Clng(Nz(Date1), 0) = Clng(Nz(Date2), 0)
    Have a nice day!

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Rip22 View Post
    Thank you for your reply.
    The 2 fields I am comparing do not contain any null values,
    These fields are actually in Text Format Example: 40234.6881944444
    They were created by taking the orginal created date GMT and adjusting to local time.

    As I stated before I want to compare the 2 fields and return a Yes if they both took place on the same day and No if they do not.

    What do you recommend?

    Thanks for your help
    so have you created the 40234.6881944444 or have you used an date function to get that value
    why store a date value as text, its asking for trouble later on.
    as you are comparing date not time you need to remove the decimal part....
    so depedning on how you created the 40234.6881944444 bit you could use
    iif(cint(avalue) = cint(bvalue),"Yes","No").. that assumes the decimal bit is the time element as per normal JET date/times.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2010
    Posts
    6
    Yes, I have created the date/time value as a text string. Only because the data/time is provided to me in GMT and I need to convert to local time. I do that buy just taking the created date less a few hours based on timezone. I would really like to have it stored as a date/time format. However I am not sure how to do that.

    This is part of a 2 step process;
    In this case I am just comparing the date. If the answer is Yes, the next step will be to calculate the turn around time based on a 24hr clock. Closed Date - Created Date = HrsWorked

    If the answer is No, I will use a Modula that i have set up to exclude non working hours, weekends and holidays.

    If you have a suggestion on the following please let me know
    1. convert the date/time value from text to date/time
    2. compare created date and closed date to see if they take place on the same day.

    Tks,
    Rip22

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Have you looked at any of the proposed methods? My CDate() will convert to date/time value. The CLng() will let you do the compare (As would the Int() function). CInt() would not work, given the values involved.
    Paul

  9. #9
    Join Date
    Mar 2010
    Posts
    6
    SameDate: IIf(CDate([Closed Date (LocalTime)])=CDate([Created Date (LocalTime)]),"Yes","No")


    Aboved is what I used and it worked.

    Thank you for your help!

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    These are not dates - they are strings. Calling them dates does change the fact they are strings.
    Does the below work?
    Code:
    IIf([Closed Date (LocalTime)]=[Created Date (LocalTime)],"Yes","No")
    It would be preferable to use this if it does since using functions inefficient if it is unnecessary.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Based on your earlier comments, you would also need the DateValue. As you have it, I would think the time value would be included and thus have to match. I thought you said you just wanted to match on date.
    Paul

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK the OP request to match the dates not the time elements
    that can be done by either converting the text value to a date and comparing those or comparing integer represetnations of the text value.

    granted the conversion should be Clng, not Cint but either route is practical.

    mind you it still seems daft to store a date as a numeric let alone text value.
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Mar 2010
    Posts
    6
    You all are extactly right, I have to convert the text string to date/time and then use the the below expresion to find the same date.

    SameDate: IIf(DateValue([Closed Date (LocalTime)])=DateValue([Created Date (LocalTime)]),"Yes","No")

    My new question is how do I convert a text string to date/time.
    I will post a new thread to ask this question as it maybe valueable to others.

    Tks for your help.

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    CDATE converts a valid representation of a date into a date time value
    exploiting the way JET stroees dates you could use the date add function to add the numeric value youv'e stored to an empty (new) date varaiable
    heck you could probably just assign the numeric value to a date time value.

    you of course by storing a date/time as a numeric value but as a text string have managed to make life really really complicated for yourself.

    why don't you stop, rething your design and do it properly
    when you capture your original date and convert it to ZT/GMT/UT add/knock off the requisite number of hours using the dateadd function and store the value as a date time... you'll be amazed at how much easier it makes your life in the long run
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Rip22 View Post
    I have to convert the text string to date/time
    Sorry - do you need to convert to date or to date and time? It seems like you are agreeing with Paul (he says you only want the date component) but then say you want date\ time.

Posting Permissions

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