Results 1 to 5 of 5
  1. #1
    Rip22 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    3

    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
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    From yourthread I assume that you have Four Date/Time Fields. Now you want to compare them using Iff.
    In this example I have Four Fields all Date/Time
    gDate
    LocalTime

    gDate1
    Localtime1

    I want to Check gDate and Local time together is equal or not to gDate1 and Localtime1
    Solution:

    IIf([gDate]+[LocalTime]=[gDate1]+[LocalTime1],"Yes","No")

    note: this solution wll only work if both Date and time are stored in Date and time Field.

    Now if you have stored date and time in text fields then the above example will not work.
    Solution:

    IIf(CDate([hDate] & " " & [htime])=CDate([hDate1] & " " & [htime1]),"Yes","No")

    if this solves you problem please mark the thread solved

  3. #3
    Rip22 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    3
    Thank you for your reply.
    Actually I am just comparing 2 fields.

    Created Date(Local Time) & Closed Date(Local Time)

    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
    maximus's Avatar
    maximus is offline Expert
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Aug 2009
    Location
    India
    Posts
    931
    Microsoft saves Date and Time values as Doubles. What you have is a double that needs to be re-converted into date time. So assuming I have two Text Fields date_ee and date_pp both contains date in Double format(e.g. 40234.6881944444)

    my solution:

    IIf(CDate([date_pp])=CDate([date_ee]),"Yes","No")
    Please mark this thread solved if this solves you problem.

  5. #5
    Rip22 is offline Novice
    Windows XP Access 2007
    Join Date
    Mar 2010
    Posts
    3
    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!
    It's very satisfying learning something new.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Epression Builder
    By ehrendreich in forum Programming
    Replies: 1
    Last Post: 12-26-2009, 02:33 PM
  2. Code Builder
    By nkenney in forum Forms
    Replies: 3
    Last Post: 11-04-2009, 10:58 AM
  3. What's wrong with this expression
    By tallroger in forum Access
    Replies: 1
    Last Post: 05-05-2009, 04:00 PM
  4. Help With an expression
    By kylem4711 in forum Queries
    Replies: 2
    Last Post: 04-23-2009, 01:57 PM
  5. Expression Builder
    By mistaken_myst in forum Access
    Replies: 2
    Last Post: 05-07-2008, 01:30 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums - Senior Forums