Date Data type and Parameters.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi all,
I have a table with test results in it and each test result has a date.

The date is in the following format in the table: "dd-mmm-yyyy" .

I have a parameter query that takes all the records with a particular date.

Here is the catch; for the parameter box, if I type in 01-jan-2006 it brings
up all my needed records OR if I type 01-01-06(Same date, different format to
the table) it will bring up the same results.

I understand that they are both valid date and hence thats why I am getting
the results that I am, but is there any way that anyone knows to limit a
paramater query to only allow the date format of dd-mmm-yyyy to be entered?
Or do I just have to create a form to do this? Any help would be greatly
apreciated.

Thanks in advance.
 
Parameters are not powerful enough to do what you ask. Use a form instead.

For an example, see Method 2 in this article:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html

BTW, I hope you realise that internally Access actually stores dates as a
number, where the whole-number represents a date value, and the fraction is
a time value (0.5 = noon; 0.25 = 6am, etc.) The format is applied at display
time, based on your user settings (Regional Settings in the Windows Control
Panel.)

For more suggestions on how to make sure Access understands your d/m/y
dates, see:
International Date Formats in Access
at:
http://allenbrowne.com/ser-36.html
 
A format is just a display item. It has nothing to do with the underlying
value in the table.

You can't get a parameter query by itself to limit the user's input. What
you can do is create a form with a textbox for the user's entry. You could
then use an Input Mask for the textbox to limit the user's entry. The
parameter for the parameter query would then be set to point to this
textbox.

Example:
[Forms]![frmMyForm]![txtMyTextbox]

The query won't automatically open the form. If the form isn't open, it will
assume the entry above to be a prompt for a user's input. So, you'll have to
do something to open the form yourself.

I assume the reason you want the user to enter the date in the format you
specified is to prevent confusion between mm/dd/yyyy and dd/mm/yyyy formats.
 
Good to know. Thank you very much for the info, I will implement a form
instead.

Thanks.


Wayne Morgan said:
A format is just a display item. It has nothing to do with the underlying
value in the table.

You can't get a parameter query by itself to limit the user's input. What
you can do is create a form with a textbox for the user's entry. You could
then use an Input Mask for the textbox to limit the user's entry. The
parameter for the parameter query would then be set to point to this
textbox.

Example:
[Forms]![frmMyForm]![txtMyTextbox]

The query won't automatically open the form. If the form isn't open, it will
assume the entry above to be a prompt for a user's input. So, you'll have to
do something to open the form yourself.

I assume the reason you want the user to enter the date in the format you
specified is to prevent confusion between mm/dd/yyyy and dd/mm/yyyy formats.

--
Wayne Morgan
MS Access MVP


James D. said:
Hi all,
I have a table with test results in it and each test result has a date.

The date is in the following format in the table: "dd-mmm-yyyy" .

I have a parameter query that takes all the records with a particular
date.

Here is the catch; for the parameter box, if I type in 01-jan-2006 it
brings
up all my needed records OR if I type 01-01-06(Same date, different format
to
the table) it will bring up the same results.

I understand that they are both valid date and hence thats why I am
getting
the results that I am, but is there any way that anyone knows to limit a
paramater query to only allow the date format of dd-mmm-yyyy to be
entered?
Or do I just have to create a form to do this? Any help would be greatly
apreciated.

Thanks in advance.
Click to expand...
Click to expand...
 
Good to know. Thank you very much for the info, I will implement a form
instead.

Thanks.
 
Back
Top