Input format in a query

[Leif Thorsen]

I have a Date-field in a table named "Nxt contact" in the form "Short date" =
2009-04-15 for example.

When I make a query with that table and let the question ask for a
particular date in "Nxt contact" I will not beeing asked to write the wanted
value in the form "Short date". How can I write the question so I always know
in what form I shall write asked value in ???

Thank You in advance

Leif

 

[KARL DEWEY]

If it is a true DateTime field then the format does not matter as Access will
change it if you enter a recognizable date format.

 

[John Spencer MVP]

If you are using a parameter in the query to get the value, you have NO
control over the data format of the parameter.

If you want to control the format of the entry you will need to use a form to
get the data and reference the form's control in your query. You can use vba
on the form to check the entry in the control prior to running the query.

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

 

[fredg]

If it is a true DateTime field then the format does not matter as Access will
change it if you enter a recognizable date format.

Except ...... If the date is an ambiguous value (i.e. 4/7/2009)
Access will assume you meant April 7th, and not July 4th.
So, to be sure Access understands the date value, always use the U.S.
date format of Month/Day/Year or the ISO date format of Year/Month/Day
when entering a Date parameter, regardless of your regional date
format settings.

 

[Ken Sheridan]

Leif:

How Access interprets a date entered at the parameter prompt when the query
is opened depends on the regional date settings in Windows Control panel on
the system:

1. If the date is entered in an internationally unambiguous format such as
the ISO standard of 2009-04-15 or a format such as 15 April 2009 then it will
be correctly interpreted whatever the regional settings.

2. If the parameter is entered in whatever format is set as the regional
short date format on the system it will be correctly interpreted whatever the
date.

3. If the regional short date setting is the US format 04/15/2009 or UK
format 15/04/2009 and the parameter is entered in that format it will be
correctly interpreted for dates after the 12th of each month whether the
parameter is entered as 04/15/2009 or 04/15/2009, but if the date is on or
before the 12th of the month then if entered in the wrong format it will be
'incorrectly' interpreted. On a system set to US short date format for
instance 07/04/2009 will be interpreted as 4 July 2009 although intended to
be 7 April 2009. The same will apply if the date is entered in US format on
a system set to UK short date format, so 07/04/2009 would be interpreted as 7
April 2009, not 4 July 2009.

If the query will be opened on a system of known date format, then you can
prompt the user to enter the date in that format, e.g. [Enter date in format
dd/mm/yyyy:] if the UK short date format is the system's regional short date
setting. If it is to be opened on systems of various possible regional date
formats, and you are not confidant of the users using the correct format for
the system when entering the parameter, then specifying an internationally
unambiguous format in the prompt would be advisable.

Another possible problem is that a parameter entered in short date format
such as 15/04/2009 could be mistakenly interpreted as an arithmetical
expression rather than a date, and give the wrong results. For this reason
date/time parameters should always be declared in a query. You can do this
in design view or by switching to SQL view and adding the parameter
declaration to the start of the query, e.g.

PARAMETERS [Enter date in format dd/mm/yyyy:] DATETIME;
SELECT *
FROM [MyTable]
WHERE [Nxt Contact] = [Enter date in format dd/mm/yyyy:];

Finally, in versions of Access since 1995 date literals, whether entered in
VBA or SQL (Jet), delimited by the # character must be in US format or an
internationally unambiguous format. I always use the ISO format
#2009-04-15#. Prior to 1995 date literals in VBA, but not in SQL, recognized
the system's regional date setting.

Ken Sheridan
Stafford, England

 

[Leif Thorsen]

When I see all of your answeres I can see that I wasn't enough clerar when I
wrote my question here.
My problem is that I want to write 20090101 and then my question shall
translate this into 2009-01-01 which is the right way of writing for the
question to respond correctly.

In Windows control panel I have the format "Short date" as Swedish standard
expression 2009-01-01

Leif:

How Access interprets a date entered at the parameter prompt when the query
is opened depends on the regional date settings in Windows Control panel on
the system:

1. If the date is entered in an internationally unambiguous format such as
the ISO standard of 2009-04-15 or a format such as 15 April 2009 then it will
be correctly interpreted whatever the regional settings.

2. If the parameter is entered in whatever format is set as the regional
short date format on the system it will be correctly interpreted whatever the
date.

3. If the regional short date setting is the US format 04/15/2009 or UK
format 15/04/2009 and the parameter is entered in that format it will be
correctly interpreted for dates after the 12th of each month whether the
parameter is entered as 04/15/2009 or 04/15/2009, but if the date is on or
before the 12th of the month then if entered in the wrong format it will be
'incorrectly' interpreted. On a system set to US short date format for
instance 07/04/2009 will be interpreted as 4 July 2009 although intended to
be 7 April 2009. The same will apply if the date is entered in US format on
a system set to UK short date format, so 07/04/2009 would be interpreted as 7
April 2009, not 4 July 2009.

If the query will be opened on a system of known date format, then you can
prompt the user to enter the date in that format, e.g. [Enter date in format
dd/mm/yyyy:] if the UK short date format is the system's regional short date
setting. If it is to be opened on systems of various possible regional date
formats, and you are not confidant of the users using the correct format for
the system when entering the parameter, then specifying an internationally
unambiguous format in the prompt would be advisable.

Another possible problem is that a parameter entered in short date format
such as 15/04/2009 could be mistakenly interpreted as an arithmetical
expression rather than a date, and give the wrong results. For this reason
date/time parameters should always be declared in a query. You can do this
in design view or by switching to SQL view and adding the parameter
declaration to the start of the query, e.g.

PARAMETERS [Enter date in format dd/mm/yyyy:] DATETIME;
SELECT *
FROM [MyTable]
WHERE [Nxt Contact] = [Enter date in format dd/mm/yyyy:];

Finally, in versions of Access since 1995 date literals, whether entered in
VBA or SQL (Jet), delimited by the # character must be in US format or an
internationally unambiguous format. I always use the ISO format
#2009-04-15#. Prior to 1995 date literals in VBA, but not in SQL, recognized
the system's regional date setting.

Ken Sheridan
Stafford, England

I have a Date-field in a table named "Nxt contact" in the form "Short date" =
2009-04-15 for example.

When I make a query with that table and let the question ask for a
particular date in "Nxt contact" I will not beeing asked to write the wanted
value in the form "Short date". How can I write the question so I always know
in what form I shall write asked value in ???

Thank You in advance

Leif

 

[Ken Sheridan]

Leif:

In the criteria row of the date column in query design view enter something
like this:

Format([Enter date:],"0000-00-00")

That will in fact work whatever the regional date setting is as the format
corresponds to the ISO date format standard, so is internationally
unambiguous. You can of course put whatever you like in place of Enter date:

Ken Sheridan
Stafford, England

When I see all of your answeres I can see that I wasn't enough clerar when I
wrote my question here.
My problem is that I want to write 20090101 and then my question shall
translate this into 2009-01-01 which is the right way of writing for the
question to respond correctly.

In Windows control panel I have the format "Short date" as Swedish standard
expression 2009-01-01

Leif:

How Access interprets a date entered at the parameter prompt when the query
is opened depends on the regional date settings in Windows Control panel on
the system:

1. If the date is entered in an internationally unambiguous format such as
the ISO standard of 2009-04-15 or a format such as 15 April 2009 then it will
be correctly interpreted whatever the regional settings.

2. If the parameter is entered in whatever format is set as the regional
short date format on the system it will be correctly interpreted whatever the
date.

3. If the regional short date setting is the US format 04/15/2009 or UK
format 15/04/2009 and the parameter is entered in that format it will be
correctly interpreted for dates after the 12th of each month whether the
parameter is entered as 04/15/2009 or 04/15/2009, but if the date is on or
before the 12th of the month then if entered in the wrong format it will be
'incorrectly' interpreted. On a system set to US short date format for
instance 07/04/2009 will be interpreted as 4 July 2009 although intended to
be 7 April 2009. The same will apply if the date is entered in US format on
a system set to UK short date format, so 07/04/2009 would be interpreted as 7
April 2009, not 4 July 2009.

If the query will be opened on a system of known date format, then you can
prompt the user to enter the date in that format, e.g. [Enter date in format
dd/mm/yyyy:] if the UK short date format is the system's regional short date
setting. If it is to be opened on systems of various possible regional date
formats, and you are not confidant of the users using the correct format for
the system when entering the parameter, then specifying an internationally
unambiguous format in the prompt would be advisable.

Another possible problem is that a parameter entered in short date format
such as 15/04/2009 could be mistakenly interpreted as an arithmetical
expression rather than a date, and give the wrong results. For this reason
date/time parameters should always be declared in a query. You can do this
in design view or by switching to SQL view and adding the parameter
declaration to the start of the query, e.g.

PARAMETERS [Enter date in format dd/mm/yyyy:] DATETIME;
SELECT *
FROM [MyTable]
WHERE [Nxt Contact] = [Enter date in format dd/mm/yyyy:];

Finally, in versions of Access since 1995 date literals, whether entered in
VBA or SQL (Jet), delimited by the # character must be in US format or an
internationally unambiguous format. I always use the ISO format
#2009-04-15#. Prior to 1995 date literals in VBA, but not in SQL, recognized
the system's regional date setting.

Ken Sheridan
Stafford, England

I have a Date-field in a table named "Nxt contact" in the form "Short date" =
2009-04-15 for example.

When I make a query with that table and let the question ask for a
particular date in "Nxt contact" I will not beeing asked to write the wanted
value in the form "Short date". How can I write the question so I always know
in what form I shall write asked value in ???

Thank You in advance

Leif

 

[Leif Thorsen]

Thank You for Your answer.

Whit a small amount of manipulation I have succeded to solve my problem
- as usual by using this forum.

Have a nice day all of You in the space

Leif:

In the criteria row of the date column in query design view enter something
like this:

Format([Enter date:],"0000-00-00")

That will in fact work whatever the regional date setting is as the format
corresponds to the ISO date format standard, so is internationally
unambiguous. You can of course put whatever you like in place of Enter date:

Ken Sheridan
Stafford, England

When I see all of your answeres I can see that I wasn't enough clerar when I
wrote my question here.
My problem is that I want to write 20090101 and then my question shall
translate this into 2009-01-01 which is the right way of writing for the
question to respond correctly.

In Windows control panel I have the format "Short date" as Swedish standard
expression 2009-01-01

Leif:

How Access interprets a date entered at the parameter prompt when the query
is opened depends on the regional date settings in Windows Control panel on
the system:

1. If the date is entered in an internationally unambiguous format such as
the ISO standard of 2009-04-15 or a format such as 15 April 2009 then it will
be correctly interpreted whatever the regional settings.

2. If the parameter is entered in whatever format is set as the regional
short date format on the system it will be correctly interpreted whatever the
date.

3. If the regional short date setting is the US format 04/15/2009 or UK
format 15/04/2009 and the parameter is entered in that format it will be
correctly interpreted for dates after the 12th of each month whether the
parameter is entered as 04/15/2009 or 04/15/2009, but if the date is on or
before the 12th of the month then if entered in the wrong format it will be
'incorrectly' interpreted. On a system set to US short date format for
instance 07/04/2009 will be interpreted as 4 July 2009 although intended to
be 7 April 2009. The same will apply if the date is entered in US format on
a system set to UK short date format, so 07/04/2009 would be interpreted as 7
April 2009, not 4 July 2009.

If the query will be opened on a system of known date format, then you can
prompt the user to enter the date in that format, e.g. [Enter date in format
dd/mm/yyyy:] if the UK short date format is the system's regional short date
setting. If it is to be opened on systems of various possible regional date
formats, and you are not confidant of the users using the correct format for
the system when entering the parameter, then specifying an internationally
unambiguous format in the prompt would be advisable.

Another possible problem is that a parameter entered in short date format
such as 15/04/2009 could be mistakenly interpreted as an arithmetical
expression rather than a date, and give the wrong results. For this reason
date/time parameters should always be declared in a query. You can do this
in design view or by switching to SQL view and adding the parameter
declaration to the start of the query, e.g.

PARAMETERS [Enter date in format dd/mm/yyyy:] DATETIME;
SELECT *
FROM [MyTable]
WHERE [Nxt Contact] = [Enter date in format dd/mm/yyyy:];

Finally, in versions of Access since 1995 date literals, whether entered in
VBA or SQL (Jet), delimited by the # character must be in US format or an
internationally unambiguous format. I always use the ISO format
#2009-04-15#. Prior to 1995 date literals in VBA, but not in SQL, recognized
the system's regional date setting.

Ken Sheridan
Stafford, England

:

I have a Date-field in a table named "Nxt contact" in the form "Short date" =
2009-04-15 for example.

When I make a query with that table and let the question ask for a
particular date in "Nxt contact" I will not beeing asked to write the wanted
value in the form "Short date". How can I write the question so I always know
in what form I shall write asked value in ???

Thank You in advance

Leif