Help With Report

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • j0rdanf1
    New Member
    • Oct 2006
    • 35
    #1

    Help With Report

    I think after this question i may have to invest in a book,

    I have a report where i want to show the date of all computers that have expired if its equal to todays date or before.

    So I have created a query to show the results i want, now i know all i need to do is input a piece of code into the criteria of the expiry date. The question is what code do i need.

    A rough but wrong guess of mine was

    IF [MACHINE]![Warrenty Finish]"<="Now()"

    Any help would be great

    Thanks
    Tags: None
    • PEB
      Recognized Expert Top Contributor
      • Aug 2006
      • 1418
      #2
      Originally posted by j0rdanf1
      I think after this question i may have to invest in a book,

      I have a report where i want to show the date of all computers that have expired if its equal to todays date or before.

      So I have created a query to show the results i want, now i know all i need to do is input a piece of code into the criteria of the expiry date. The question is what code do i need.

      A rough but wrong guess of mine was

      IF [MACHINE]![Warrenty Finish]"<="Now()"

      Any help would be great

      Thanks
      If this is a query:

      Try this one:
      IIF([MACHINE].[Warrenty Finish]<=Now(),"Finish ed","Not Finished")

      If in a report:
      IIF(Reports![MACHINE]![Warrenty Finish]<=Now(),"Finish ed","Not Finished")

        Comment

        • NeoPa
          Recognized Expert Moderator MVP
          • Oct 2006
          • 32634
          #3
          Please don't use Now() for this.
          Date() is the correct function here ;).

            Comment

            • PEB
              Recognized Expert Top Contributor
              • Aug 2006
              • 1418
              #4
              Originally posted by NeoPa
              Please don't use Now() for this.
              Date() is the correct function here ;).
              Ade why don't like Now()? What about it?

                Comment

                • NeoPa
                  Recognized Expert Moderator MVP
                  • Oct 2006
                  • 32634
                  #5
                  Originally posted by PEB
                  Ade why don't like Now()? What about it?
                  When comparing dates with Now() (which includes a time element), they are never equal.
                  Consider the following SQL snippet :
                  Code:
                  WHERE Now() Between MyTable.DateFrom AND MyTable.DateTo
                  If you had DateFrom as yesterday and DateTo as today would you expect this record to be included?
                  In fact it would not be.
                  Yesterday = 39,047; Today = 39,048 & Now() = 39,048.8599 which falls outside of the range.
                  Date() of course = 39,048 so would work exactly as expected.

                  If working with Date/Times, which is also needed a lot, then Now() is the correct thing to use.

                    Comment

                    • PEB
                      Recognized Expert Top Contributor
                      • Aug 2006
                      • 1418
                      #6
                      Originally posted by NeoPa
                      When comparing dates with Now() (which includes a time element), they are never equal.
                      Consider the following SQL snippet :
                      Code:
                      WHERE Now() Between MyTable.DateFrom AND MyTable.DateTo
                      If you had DateFrom as yesterday and DateTo as today would you expect this record to be included?
                      In fact it would not be.
                      Yesterday = 39,047; Today = 39,048 & Now() = 39,048.8599 which falls outside of the range.
                      Date() of course = 39,048 so would work exactly as expected.

                      If working with Date/Times, which is also needed a lot, then Now() is the correct thing to use.
                      Sure u've right! Sometimes i'm using Int(Now()) to eliminate this element in the queries.. but when i introduce a document i use Now() for date/time stamp and the date and time of creation of the document...

                      And than i repass the document date using int() if i don't forget of course ;)

                        Comment

                        • j0rdanf1
                          New Member
                          • Oct 2006
                          • 35
                          #7
                          Originally posted by PEB
                          Sure u've right! Sometimes i'm using Int(Now()) to eliminate this element in the queries.. but when i introduce a document i use Now() for date/time stamp and the date and time of creation of the document...

                          And than i repass the document date using int() if i don't forget of course ;)
                          Tried the one for the query and received this error

                          "Data Type Mismatch in criteria expression"

                          Code:
                          IIF([MACHINE].[Warrenty Finish]<=Now(),"Finished","Not Finished")
                          the above code matches the table called MACHINE and matches the cell Warrenty Finish so it confuses me a little.

                          This is the format i use in the Warrenty finish cell 19/09/2010

                          Cheers.

                            Comment

                            • j0rdanf1
                              New Member
                              • Oct 2006
                              • 35
                              #8
                              Originally posted by j0rdanf1
                              Tried the one for the query and received this error

                              "Data Type Mismatch in criteria expression"

                              Code:
                              IIF([MACHINE].[Warrenty Finish]<=Now(),"Finished","Not Finished")
                              the above code matches the table called MACHINE and matches the cell Warrenty Finish so it confuses me a little.

                              This is the format i use in the Warrenty finish cell 19/09/2010

                              Cheers.
                              Anybody? tried to google the answer but am not really getting anywhere

                                Comment

                                • NeoPa
                                  Recognized Expert Moderator MVP
                                  • Oct 2006
                                  • 32634
                                  #9
                                  If you read all the answers posted then you would know not to use Now().
                                  However, it sounds like your problem is with the date formatting.
                                  Do you store the date as a string or a date/time?
                                  If it is a Date/time then the format is irrelevant. SQL only works with dates in m/d/y format.
                                  Where is your posted code? In VBA or in SQL?
                                  If in SQL then you've got problems.

                                    Comment

                                    Previous template Next
                                    Working...