Form validation rule MS-Access 2007

1
votes

I have a simple form that gets used to enter information into a table. I want to use a validation rule on the form so that information gets entered correctly. I have a datetime object that must be filled out in a non-traditional form so I just want to check the length and make sure it is equal to 16. I have the following in the form which does not work

=Len([DISCHARGE DATETIME])=16

But when I put the same rule in the table and not in the form it works just fine, any ideas?

1
validationms-accessms-access-2007
Why? If you have a date time, just make sure it is not null, or insist on an input mask. - Fionnuala
The input mask in Access is not of an acceptable form for when the data has to be uploaded to another source, I'll just use the rule on the table, it seems to work just fine there. The date time has to be of the form YYYY-MM-DD HH:MM I do not see that input mask and I do not know how to make one. - MCP_infiltrator
It is not difficult to create an input mask, but if you think that a date / date time is in any format except number, you have a problem. A date is stored as an integer and a time as a decimal. - Fionnuala
Periodically I have to take the data from the db and export it as a .csv and all datetimes must be in the form of YYYY-MM-DD HH:MM when I upload the .csv file not in its serial number form - MCP_infiltrator
Is the field / column text or date? If it is date, it is up to you to create a query that formats the date correctly, and then export that. - Fionnuala

1 Answers

2
votes

Dates should be stored in date data types. In a lot of DBs the date data type is numeric. In MS Access it is a decimal, the integer portion is a date and the decimal a time. It is not difficult to create a query that uses the Format function to modify output to suit an application.

 SELECT Format(ThisDate,"yyyy-mm-dd hh:nn:ss") FROM ThisTable