desperately need help on date validation 1

[Gerilee]

Please help me on this. I am sooooo frustrated. I have a form bound to a Date/Time field in my table with a format of Short Date with no input mask. I want the date to be stored and displayed as mm/dd/yyyy. The date field is also part of the primary key on the table along with another text field. I have tried Validation Rules on the table, functions in VBA, and writing code in VBA. I still can't get it. The problem is if the user enters for example 20/2/06, it is transposed to 2/6/2020. I don't want that to happen because what if the user wants 2 for day and just types the wrong month in -- then I don't want it to be transposed. I tried to write VBA code that would validate the date entered as a string allowing me to look at the input in parts (1st 2 fields, 3rd field, etc.). I put that logic in the After Update event and then the Before Update event. The problem with that is my code is evaluating the date AFTER it is transposed. I tried to put the logic in the Change Event and that didn't work either. Is there some standard date validation routine that I should be using to accommodate for what Access doesn't handle? Or is there something in Access that I just don't know about. Please help -- I have been trying to get this to work for awhile. I keep going back to it as I learn more but I still can't get it. I'm really frustrated.

 

[stix4t2]

Can you put your code into the field change event to analyze it while it is being entered??

 

[Remou]

You may find RoyVidar's post in this:
Date format problem
thread705-1081480

Useful.

 

[Gerilee]

stix4t2 - I did put the code in the Change event of the field but it did the evaluation after Access transposed it. Is that what you meant?

Remou -- thanks for the quick response. I will study this info and get back with questions I may have. Thanks!

 

[stix4t2]

Yes, but I was thinking you could validate the month as they were typing it in. So as the first / or no numeric is at the right of the text, you could validate the entry as to say incorrect month, or date.

 

[Gerilee]

stix4t2,

How would I do that? That's what I was thinking when I put the validation on the control's Change event but the date was transposed by Access before my validation started. Am I missing something?

Thanks.

 

[stix4t2]

Use the code you wrote to test the parts in the change event so you can identify problems as the date is being entered.

 

[PHV]

Just a note: in the Change event procedure play with the Text property of the control, not the Value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886