Date Format Question 1

[hjgoldstein]

Hi.

I have a table tblMain with two date fields:

MainDateRecd - format dd-mm-yyyy
MainReviewDate - format mmm-yyyy

There are two forms which are based on this table.

In the MainReviewDate field on each of the forms, I need to validate as follows:

>=[MainDateRecd] as the proposal can't be reviewed before it has been received <grin>.

My question is, when the Date Received is, say, 09/09/2009 and the Review Date is Sep-09, the validation fails, as Access is looking at Sep-09 as 01/09/09.

Is there a way around this without reams of code?

I was thinking about:

DateSerial(month)[MainReviewDate]>=DateSerial(month)[MainDateRecd]
AND
DateSerial(year)[MainReviewDate]>=DateSerial(year)[MainDateRecd]

but can't get it to work

Any help much appreciated.

Howard.

 

[dhookom]

Criteria should always be entered as m/d/y. Date values are always stored as numbers. For instance the current date an time is 40065.2573611111 where the date part is to the left of the decimal and time part is to the right.

Your MainReviewDate can never be "Sep-09". If it is a date field, it will always have a day of the month associated. Sometimes Access will supply the 1st day of the month if you omit it.

Duane
Hook'D on Access
MS Access MVP

 

[hjgoldstein]

Thanks, Duane. I thought that the fact of dates being stored as numbers would be the problem.

I think I will have to see if I can convince the users to adopt a procedural work-around.

 

[JoeAtWork]

I thought that the fact of dates being stored as numbers would be the problem.

No, that's not a problem at all.

Why don't you simply change MainReviewDate display format to show the day as well? Then the user can see the exact date that will be compared to MainDateRecd. It appears that displaying the date as mmm-yy does not fit your user requirements, so why use it?

 

[hjgoldstein]

Joe,
The Review date is actually a month, not a specific day within the month.

The procedure I was referring to was that I will suggest to the users that a review cannot be required in the same month as that in which the proposal is received.

This will get over the MMM-YYYY default to 1st of the month.

Thanks for your input.

Howard.

 

[HarleyQuinn]

This might be over-simplifying things but why not simply eliminate the day from the comparison?

Format([tblMain]![MainReviewDate],"yyyymm")>=Format([tblMain]![MainDateRecd],"yyyymm")

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[hjgoldstein]

HarleyQuinn,

I like simple <grin>.

Can you tell me where this goes? Is it in the validation rule property?

Thanks for coming back.

Howard

 

[HarleyQuinn]

I'll be honest, I don't use access forms at all anymore (so there might be a better way of doing this) but depending what you want to happen when validating a textbox putting

Format([MainReviewDate],"yyyymm")>=Format([MainDateRecd],"yyyymm")

in the Validation Rule for the MainReviewDate textbox works fine for me (i.e. a message pops up saying that the value is invalid etc. if you enter an invalid review date).

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[hjgoldstein]

Thanks, HarleyQuinn. Have a Star!

 

[HarleyQuinn]

Glad I could help, thanks for the star

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post