X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Date Format Issues, Access 2000    
post Jun 19 2017, 06:12 AM

Posts: 55
Joined: 17-March 04

I'm using the following expressions in a query to calculate is a date is 30 days prior to todays date and mark it as expired.

expire_date: Format(IIf([used_hours]=0,DateAdd("d",[date],30),"NA"),"Short Date") expired: IIf([expire_date]<Format(Now(),"Short Date"),"Y","N")

When expire date is 6/10/2017 it works fine and the "N" is displayed. When the expire_date is 6/7/2017 it doesn't work and the "Y" is displayed. I think it has something to do with the single digit in the day field but don't know how to fix it.

Go to the top of the page
post Jun 19 2017, 06:31 AM

UtterAccess VIP
Posts: 6,899
Joined: 24-May 10
From: Downeast Maine

Date is stored as a number where the integer portion is the number of days since Dec. 30, 1899 and the decimal portion is the fraction of a day. When you apply Format the result is a text value. To compare two date values, just compare them without formatting. You are comparing a number (date/time value) to a text value.

Note that when a date/time value that does not specify the time of day (for instance, you add a date value to expire_date without specifying the time of day), Access interprets the value as midnight. Now includes the time of day. If expire_date does not, expire_date < Now() even if expire_date is today's date.
Go to the top of the page
post Jun 19 2017, 09:35 AM

UtterAccess VIP
Posts: 7,692
Joined: 25-October 10
From: Gulf South USA

Hi: Bruce explained how the dates are stored and how you might use that information. But here is another approach to getting what you want that might work better for you...

IIf([used_hours]=0,IIf([MyDate]<DateAdd("d",-30,Date()),"Y","N"),"NA") AS expired

You had written the DateAdd function incorrectly in your post, and you had converted the dates to text (Format does that) - which messes up your comparison operation. So you needed to rethink this anyway. And it can be done in a single expression, as you see.

It also looks like you have a field named [Date] - this is a very bad idea since Date is a reserved word and refers to the current date. I changed that name in my demo to [MyDate], but you should name it something appropriate - such as [ProjectDate] or [EventDate] or something relevant.

See the demo attached, using the two dates you posted, and adding more records with other combinations of used-hours and dates (past and future). See if this is what you a trying to do...

You may have to consider what Bruce told you about the dates if time is a factor. But, if the demo looks like what you want, test it against your db and see if it gives you the results you want.

Attached File(s)
Attached File  DateFormatIssues_A2000.zip ( 18.48K )Number of downloads: 5

"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    20th October 2017 - 04:44 PM