By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,154 Members | 2,313 Online
Bytes IT Community
+ 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
Share this Question
Share on Google+
8 Replies


PEB
Expert 100+
P: 1,418
PEB
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

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

PEB
Expert 100+
P: 1,418
PEB
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

NeoPa
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
  1. 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

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

NeoPa
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

Post your reply

Sign in to post your reply or Sign up for a free account.