Enter Date Format

[Confused87]

In my table I have a field set to Date/Time format.

In my form I have a text box for the user to input data. I need to either

a) allow any format of date to be entered, ie 2nd Jan 09 or 2/1/9 or January
2nd 2009 et cetera

b) a dialogue box appearing to tell the user to enter the date in the
correct format: dd/mm/yyyy

Any ideas?

 

[Arvin Meyer MVP]

b is the easiest. Use the BeforeUpdate event to check the format and post a
message box if it is the incorrect format.

 

[John W. Vinson]

In my table I have a field set to Date/Time format.

In my form I have a text box for the user to input data. I need to either

a) allow any format of date to be entered, ie 2nd Jan 09 or 2/1/9 or January
2nd 2009 et cetera

b) a dialogue box appearing to tell the user to enter the date in the
correct format: dd/mm/yyyy

Any ideas?

a) is the default. Access is very forgiving about date entry; there are some
limitations - all of the examples you post work "out of the box" (assuming
that your computer's regional settings are set to d/m/y dates).

Literal dates used as search criteria must be in m/d/y format or an
unambiguous format such as yyyy-mm-dd however. You can use the Format()
function to recast a user entered date into an allowed format, i.e. use a
criterion on a date field of

=Format([Forms]![frmCrit]![txtFindDate], "mm/dd/yyyy")

The user can enter 4/3 into the textbox txtFindDate (with their computer's
Start... Control Panel... Regional and Language... Date/Time set to
dd/mm/yyyy) and the search will translate it to 03/04/2009.

They can also enter a date (using any of these styles) into a table field
(using a form, or even using a table datasheet); if the date field is
displayed in a textbox with a dd/mm/yyyy format, that's what will be shown.

What specific problem are you having?

 

[Ken Sheridan]

I'm afraid that in a query that would have the opposite effect here and
exclude the row from the result set as formatting the value entered in the
text box as 4/3/2009 (4 March 2009) as 'mm/dd/yyyy' would cause it to be
interpreted as 3 April 2009 on UK date format systems.

Declaring the parameter (to ensure its not misinterpreted as an arithmetical
expression) and leaving it unformatted is all that's needed in a query:

PARAMETERS [Forms]![frmCrit]![txtFindDate] DATETIME;
SELECT *
FROM MyTable
WHERE MyDate = [Forms]![frmCrit]![txtFindDate];

In contexts such as a DLookup function call, where the value input is
concatenated into a string expression it should be formatted unambiguously
and the # date delimiter characters included in the expression, e.g.

Const conMESSAGE = "No matching records found."
Dim varD As Variant
Dim strCriteria As String

strCriteria = "MyDate = #" & _
Format([Forms]![frmCrit]![txtFindDate],"yyyy-mm-dd") & "#"

vargID = DLookup("MyID", "MyTable", strCriteria)

If Not IsNull(varID) Then
strCriteria = "MyID = " & varID
DoCmd.OpenReport "MyReport", _
WhereCondition:=strCriteria
Else
MsgBox conMESSAGE, vbInformation, "Warning"
End If

Similarly when building an SQL which includes date literals:

strSQL = "SELECT * " & _
" FROM MyTable " & _
"WHERE MyDate = #" & _
FORMAT([Forms]![frmCrit]![txtFindDate],"yyyy-mm-dd") & "#"

Ken Sheridan
Stafford, England

 

[John Spencer MVP]

John,
I think that using ordinal day numbers will cause a failure.
January 3rd, 2008 will probably error.
January 3, 2008 will not error


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

In my table I have a field set to Date/Time format.

In my form I have a text box for the user to input data. I need to either

a) allow any format of date to be entered, ie 2nd Jan 09 or 2/1/9 or January
2nd 2009 et cetera

b) a dialogue box appearing to tell the user to enter the date in the
correct format: dd/mm/yyyy

Any ideas?

a) is the default. Access is very forgiving about date entry; there are some
limitations - all of the examples you post work "out of the box" (assuming
that your computer's regional settings are set to d/m/y dates).

Literal dates used as search criteria must be in m/d/y format or an
unambiguous format such as yyyy-mm-dd however. You can use the Format()
function to recast a user entered date into an allowed format, i.e. use a
criterion on a date field of

=Format([Forms]![frmCrit]![txtFindDate], "mm/dd/yyyy")

The user can enter 4/3 into the textbox txtFindDate (with their computer's
Start... Control Panel... Regional and Language... Date/Time set to
dd/mm/yyyy) and the search will translate it to 03/04/2009.

They can also enter a date (using any of these styles) into a table field
(using a form, or even using a table datasheet); if the date field is
displayed in a textbox with a dd/mm/yyyy format, that's what will be shown.

What specific problem are you having?

 

[Confused87]

I get the following error message when I type anything that isn’t in with the
dd/mm/yy format:

“The value you entered isn’t valid for this field.

For example, you may have entered text in a numeric field or a number that
is larger than FieldSize setting permits.â€

I don’t have a problem if they have to enter it in dd/mm/yy format, as long
is there a message which states that. What do I need to do to create the
BeforeUpdate thingy? I am very new to validation rules.

 

[John W. Vinson]

I get the following error message when I type anything that isn’t in with the
dd/mm/yy format:

“The value you entered isn’t valid for this field.

For example, you may have entered text in a numeric field or a number that
is larger than FieldSize setting permits.”

I don’t have a problem if they have to enter it in dd/mm/yy format, as long
is there a message which states that. What do I need to do to create the
BeforeUpdate thingy? I am very new to validation rules.

The *FORMAT* property of the field just controls how the stored data is
displayed. I think you may have the wrong *DATATYPE* for the field.

Open the table in design view. What is the datatype (second column in the
table design grid) for this field? What (if anything) is in the Format
property in the lower left? What is in the Input Mask property?

If you're only having problems on the Form, not directly in the table
datasheet, what are the Format and Input Mask properties of the textbox you're
using?

 

[Tony Toews [MVP]]

The user can enter 4/3 into the textbox txtFindDate

You don't even need the /. Try entering 4 3 with the space.

Tony

 

[Confused87]

The Format property and the Input Mask property on both the form and the
table are blank. The Data Type on the table is set to Date/Time.

 

[John W. Vinson]

The Format property and the Input Mask property on both the form and the
table are blank. The Data Type on the table is set to Date/Time.

What is an example of data that causes the invalid entry message? Access is
pretty tolerant of different date formats, but there *are* limits...