444,154 Members | 2,313 Online
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,154 IT Pros & Developers. It's quick & easy.

# Help With Report

 P: 35 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 Nov 27 '06 #1
8 Replies

 Expert 100+ P: 1,418 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(),"Finished","Not Finished") If in a report: IIF(Reports![MACHINE]![Warrenty Finish]<=Now(),"Finished","Not Finished") Nov 27 '06 #2

 Expert Mod 15k+ P: 31,616 Please don't use Now() for this. Date() is the correct function here ;). Nov 27 '06 #3

 Expert 100+ P: 1,418 Please don't use Now() for this. Date() is the correct function here ;). Ade why don't like Now()? What about it? Nov 27 '06 #4

 Expert Mod 15k+ P: 31,616 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 : Expand|Select|Wrap|Line Numbers 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. Nov 27 '06 #5

 Expert 100+ P: 1,418 When comparing dates with Now() (which includes a time element), they are never equal. Consider the following SQL snippet : Expand|Select|Wrap|Line Numbers 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 ;) Nov 27 '06 #6

 P: 35 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" Expand|Select|Wrap|Line Numbers 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. Nov 28 '06 #7

 P: 35 Tried the one for the query and received this error "Data Type Mismatch in criteria expression" Expand|Select|Wrap|Line Numbers 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 Nov 28 '06 #8

 Expert Mod 15k+ P: 31,616 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. Nov 28 '06 #9