Help With Report

[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

[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")

[NeoPa]

Please don't use Now() for this.
Date() is the correct function here ;).

[PEB]

Please don't use Now() for this.
Date() is the correct function here ;).

Ade why don't like Now()? What about it?

[NeoPa]

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 :

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.

[PEB]

When comparing dates with Now() (which includes a time element), they are never equal.
Consider the following SQL snippet :

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 ;)

[j0rdanf1]

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"

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.

[j0rdanf1]

Tried the one for the query and received this error

"Data Type Mismatch in criteria expression"

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

[NeoPa]

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.