Simple date edit

[mwc0914]

I have a form where I enter a date. I am using an input mask of 99/99/0000;0;_

The data type on the table is short date. It allows me to enter any numbers in this format, but I want to perform a date edit to ensure the value entered is a valid date. Is there a way to do this via form properties?

 

[Brianwarnock]

If the form field is defined as a date it will error message you if the values are invalid.

brian

 

[mwc0914]

Being new to Access, how do you define the field as a date field?

What I have now is in the properties, the format is short date, under the data tab, the control source is the table column name (which is defined as text on the table)

 

[Brianwarnock]

What I have now is in the properties, the format is short date, under the data tab, the control source is the table column name (which is defined as text on the table)

Are you saying that the control source is a text field, but the form is saying its a date!? Never having done that I haven't a clue what ACCESS will make of it.

Brian

 

[Brianwarnock]

Ok just tried it, Access right justifies the input as if its a date, but lets you enter rubbish. Why is the date field in the table a text field?

Brian

 

[mwc0914]

I agree...I think the table field should be defined as date. If it were, woudl the date edit wotk on the form?

 

[Brianwarnock]

I agree...I think the table field should be defined as date. If it were, woudl the date edit wotk on the form?

yep

Brian

 

[missinglinq]

The field in the table should be defined in Design View as a date/time datatype. Having said that, Access is very tolerant, much of the time, of a text field, as long as it's in an recognized date format, i.e. 1/15/2008 or 1-15-2008.

DateAdd("ww",1,strDate)

will work when strDate is defined as Text datatype, as will

DateDiff("d",strDate,Date).

But while

YourDate -1

will give you yesterday's date, if YourDate is defined as a date/time datatype, it will bomb (runtime error 13 Type Mismatch) if YourDate is defined as text.

I suspect, but don't know for a fact, that SQL would not be as tolerant as Access is in the matter.

Linq

 

[Brianwarnock]

linq none of that is the problem, if defined as text the date field will accept 32/13/2006 , in otherwords it will not validate the entry which is what is required.

brian

 

[missinglinq]

I understand that, Brian! Hence my opening statement "The field in the table should be defined in Design View as a date/time datatype." I was merely adding to the general discussion of how Access adresses the issue of dates in text datatype!

 

[Brianwarnock]

I had noticed your first line reinforcing what I had said earlier, I was just a bit worried that if mwc was a newbie he/she might start to get confused with too much info. Perhaps I should have more faith.

Brian