Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Posts
    9

    Unanswered: putting dates in an iif statement

    in the expression builder for a query, what is the proper way to put Between statements (for dates) into the true and false parts of an IIF statement?

    currently it is not giving me the correct result, but if i put either of those true or false Between statements by itself as the criteria, it'll return the correct result. it's only when i embed them into an IIF statement do they not work. any suggestions? thanks.

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: putting dates in an iif statement

    Originally posted by cjwang80
    in the expression builder for a query, what is the proper way to put Between statements (for dates) into the true and false parts of an IIF statement?

    currently it is not giving me the correct result, but if i put either of those true or false Between statements by itself as the criteria, it'll return the correct result. it's only when i embed them into an IIF statement do they not work. any suggestions? thanks.
    Let's see what you are doing ... Post the relevant SQL ok?

  3. #3
    Join Date
    Dec 2003
    Posts
    9

    Re: putting dates in an iif statement

    Originally posted by M Owen
    Let's see what you are doing ... Post the relevant SQL ok?
    IIF([Forms]![Parent Info]![AllDates] =False, Between #1/1/1900# And Date (),Between [Forms]![Parent Info]![CommLogStartDate] And [Forms]![Parent Info]![CommLogEndDate])

    [All Dates] - toggle button (show all dates or let user enter start and end)
    [Parent Info] - Main Form
    [CommLogStartDate] - text box for start date entry
    [CommLogEndDate] - text box for end date entry

    Thanks for the help.

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    try using format to change the format of the dates to US (mm/dd/yyyy) your probably entering European dates (dd/mm/yyyy) which ends up getting you mixed information.
    01-03-2004 (01-march) will get interperted as 03-jan. By using mm/dd/yyyy or dd/mmm/yyy you ensure that SQL enterpets the dates as intended.

    Regards

  5. #5
    Join Date
    Jun 2003
    Location
    Belgium
    Posts
    133

    Re: putting dates in an iif statement

    Originally posted by cjwang80
    IIF([Forms]![Parent Info]![AllDates] =False, Between #1/1/1900# And Date (),Between [Forms]![Parent Info]![CommLogStartDate] And [Forms]![Parent Info]![CommLogEndDate])

    [All Dates] - toggle button (show all dates or let user enter start and end)
    [Parent Info] - Main Form
    [CommLogStartDate] - text box for start date entry
    [CommLogEndDate] - text box for end date entry

    Thanks for the help.
    to cjwang :
    It looks like you are misinterpreting the format of the IIF function:
    the syntax should be IIF(Expr, truepart, falsepart)

    You seem to use the IIF function with 3 expr to evaluate since Between is also returning True or False.

    Try reading the Help functions on the IIF and Between functions...

  6. #6
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Cool

    Here is a nice function to do the convert for you:



    Function CSQLDate(varDate As Variant) As String
    'This funktion changes any given date format to a string that used in the SQL-Statment,

      If IsDate(varDate) Then
         CSQLDate = "#" & Month(varDate) & "/" & Day(varDate) & "/" & Year(varDate) & "#"
       Else
         CSQLDate = ""
       End If
    End Function



  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Hammbakka,

    How about just using: Format(date(),"dd-mmm-yyyy") or something simular, shorter sweeter faster....

  8. #8
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    Originally posted by namliam
    Hammbakka,

    How about just using: Format(date(),"dd-mmm-yyyy") or something simular, shorter sweeter faster....

    That is also OK, but I used to use Functions to perform my work, this is a habit. Also this function will be a complete of the ACCE$$ series CDate , CSng, CInt, ...

Posting Permissions

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