Date format force Properties in VBA forms

[Mlanger575]

Hey everyone

I am setting up a database which has a number of date fields. Traditionally in the past a number of people have housed the wrong date, for example, I want dd/mm/yyyy however I attain results such as dd/mm/yy, d/mm/yyyy, dd.mm.yyyy or d.mm.yyyy. This is an issue as I have sub formulas running off these dates and need them consistent.

How can I force the data base to accept only dates in the dd/mm/yyyy formats than can form part of routine to check and return error text to users whom are entering data in the wrong format.

Thanks
Michael

[Kenneth Hobson]

Access is a database. Excel is a spreadsheet. Are you talking about a pseudo database in Excel?

If Excel, your formulas should be using the data Value, not the date Format. As such, format the cells to the format that you want. If using Excel, you can delete invalid dates using the Worksheet's Change event. If you want to try that, say so. If they enter dd/mm/yy like 1/2/03, then you really have no way to correct that. It is the same as if they picked the wrong date from a calendar dialog. A Worksheet's Selection event could be used to open a date dialog. If you want that, say what worksheet and range the dates are in.

[Mlanger575]

Hi Kenneth

Yes, I'm using Excel to compile multiple responses in a database style format. I'm using a macro to collate the information. Once of the Macro fields it a text box to collect the date in a dd/mm/yyyy format, however I really don't want to trust the users that this is they will enter this correctly. Is there a way in VBA to change the properties of the cell so only the date format dd/mm/yyyy is permissible, thereby allowing the correct information to come through to drive other metrics?

Thanks
Michael

[Kenneth Hobson]

As I said, you can determine if a string (textbox values are strings) is a date or impossible date. You can not determine if it is the "correct" date where 01/02/01 could be mm/dd/yy or dd/mm/yy. For cells, you could use a date format but it is still a number.

You can do similar checks for a cell by adding a Worksheet Change event. You are better off using a date control or a pseudo date userform to make sure that they at least pick a valid date to begin with.

If you are using a userform, we could add a date control activex control. The most reliable method if it is a one off deal is to use a pseudo date dedicated userform.