dates from row source query incorrect

  • Thread starter Thread starter megthegirlfriend
  • Start date Start date
M

megthegirlfriend

I have a form with a combobox. The box is linked to a table. The
Rowsource on this combobox is simple:

SELECT DISTINCT Details.Date
FROM Details;

When I run that SQL I get this:
11/13/05
11/10/95
11/13/05

Why am I getting two of the same dates? Why is DISTINCT not working?
Any help is appreciated, thanks.
 
In addition, Details.Date is a Text field. Its Input Mask in the table
is 99/99/0000.
 
Could the date/time field contain a time component as well as a date?
Typically this happens when you used =Now() instead of =Date() to assign the
value.
 
Thanks for your quick reply.
The Default Value assigned to that field is Date().
Doesn't help much!
 
You might be facing any combination of these problems:

1. You are using a Text field instead of a date.

2. You have an input mask that requires 4 digits for the "year" part, but
your example output shows only 2 digits from the data.

3. The month and day part of your input mask are purely optional, so your
field would accept this as a valid entry:
//2005

4. The field is named Date, which is a reserved word in VBA and likely to be
misunderstood for the system date.

5. The Format property of the field in the table or the Format property of
the field in the query, or the Format property of the text box on your form
could cause Access to display something that does not match the actual data
in the field.

6. If any of those Formats are involved, the results could also be affected
by your Regional Options in the Windows Control Panel.

In short, even if you did solve the immediate problem, there will be plenty
of other issues awaiting you down the track.

The best approach would be to replace the text field with a DateTime field.
 
Back
Top