Date() Not working??

[Guest]

I would call this an emergence! There are people throughout the state using
this database and now it's down. Strange thing is that no changes were made
in days.
Anyway: I have narrowed the problem down to a statement in a query.
When I remove any reference to "Date()" it works..
Example:
THIS WORKS:
DaysRemaining_45: IIf([issueclosedate] Is Not Null,"CLOSED")

THIS DOES NOT:
DaysRemaining_45: IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_45]<Date(),"OVER DUE"))

Completiondate_45 is just a date value like 01/01/07

Any suggestions????

 

[Guest]

Do you get an error problem because of the date, or you just not getting the
right resault?

If you are getting an error because of the date(), it usually indicate on a
missing reference.
Open code anywhere (or press Ctrl+G), from the menu bar select Tools >
Reference > check if the word missing apear next to any of the objects
selected.
I the word missing apear, press to unselect, and then run complie to check
if you need that reference.

Also, usually the IIf need an ELSE
IIf(Condition , Then , Else)

You are missing the Else

 

[Guest]

Has there been any other changes applied to the affected users' computers?
This appears to be a refernce problem. Make sure everyone still has a
reference to VBA. That is where the Date function lives.

 

[Allen Browne]

Dan, there could be a problem with one of the library references the
database uses. Any bad library will prevent them all from working. For
details, see:
Solving problems with Library References
at:
http://allenbrowne.com/ser-38.html

We assume that [completiondate_45] is a Date/Time type field in a table, not
a Text type field. If it is a calculated field from a query, it may need
typecasting to ensure Access understands it. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

 

[Guest]

Thank you both for the advise.
I am working with my IT dept to determine if it could be a reference issue.
Also, they suggest it may be invalid data that somehow made it in.

Thanks

Has there been any other changes applied to the affected users' computers?
This appears to be a refernce problem. Make sure everyone still has a
reference to VBA. That is where the Date function lives.
--
Dave Hargis, Microsoft Access MVP

I would call this an emergence! There are people throughout the state using
this database and now it's down. Strange thing is that no changes were made
in days.
Anyway: I have narrowed the problem down to a statement in a query.
When I remove any reference to "Date()" it works..
Example:
THIS WORKS:
DaysRemaining_45: IIf([issueclosedate] Is Not Null,"CLOSED")

THIS DOES NOT:
DaysRemaining_45: IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_45]<Date(),"OVER DUE"))

Completiondate_45 is just a date value like 01/01/07

Any suggestions????

 

[Guest]

[completiondate_45] a field created from another date/type filed +45.

The issue seems to be with Date()
I looked at eh link you attached but I'm not sure how that would plug into
this?

When I remove all reference to Date() it works.
Below: I add in DateDiff reference using Date() and it does not work…


ORIGINAL - Does Not Work
DaysRemaining_45: IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_45]<Date(),"OVER DUE",IIf([csreceiptdate]
Is Null And [receiptdate] Is Null,"NO DATE",IIf([csreceiptdate] Is
Null,DateDiff("d",Date(),[receiptdate]+45),DateDiff("d",Date(),[csreceiptdate]+45)))))

WORKS - Removed all reference to Date()
DaysRemaining_45: IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([csreceiptdate] Is Null And [receiptdate] Is Null,"NO
DATE"))

DON"T WORK - When adding any reference to Date()
DaysRemaining_45: IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([csreceiptdate] Is Null And [receiptdate] Is Null,"NO
DATE",DateDiff("d",Date(),[csreceiptdate]+45))

Dan, there could be a problem with one of the library references the
database uses. Any bad library will prevent them all from working. For
details, see:
Solving problems with Library References
at:
http://allenbrowne.com/ser-38.html

We assume that [completiondate_45] is a Date/Time type field in a table, not
a Text type field. If it is a calculated field from a query, it may need
typecasting to ensure Access understands it. Details in:
Calculated fields misinterpreted
at:
http://allenbrowne.com/ser-45.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I would call this an emergence! There are people throughout the state
using
this database and now it's down. Strange thing is that no changes were
made
in days.
Anyway: I have narrowed the problem down to a statement in a query.
When I remove any reference to "Date()" it works..
Example:
THIS WORKS:
DaysRemaining_45: IIf([issueclosedate] Is Not Null,"CLOSED")

THIS DOES NOT:
DaysRemaining_45: IIf([issueclosedate] Is Not
Null,"CLOSED",IIf([completiondate_45]<Date(),"OVER DUE"))

Completiondate_45 is just a date value like 01/01/07

Any suggestions????

 

[John W. Vinson]

The issue seems to be with Date()
I looked at eh link you attached but I'm not sure how that would plug into
this?

Here's my canned response to this problem. The References problem is *on your
computer*, actually in your instance of the Database; you shouldn't need IT
support to fix it.

This appears to be the very common References bug. Open any
module in design view, or open the VBA editor by typing
Ctrl-G. Select Tools... References from the menu. One of the
..DLL files required by Access will probably be marked
MISSING. Uncheck it, recheck it, close and open Access.

If none are MISSING, check any reference; close and open
Access; then uncheck it again. This will force Access to
relink the libraries.

John W. Vinson [MVP]