Define date field in a make table query

[Guest]

Is there a way to define a field as a date, formatted as mm/yyyy from the
make table query? By default, the field will be text, but I will be loading
dates into the field in mm/yyyy format.

Thanks in advance for your help
Kenny A.

 

[Guest]

You can't define field types in a make table query. The query decides what
each type should be.

 

[Guest]

Klatuu,
I had a feeling, but wanted to check just the same.

Thanks
Kenny A.

 

[Guest]

You can add a DateTime field by includeing this in the design view of the
query --
Expr1: #01/01/2007#

 

[John W. Vinson]

Is there a way to define a field as a date, formatted as mm/yyyy from the
make table query? By default, the field will be text, but I will be loading
dates into the field in mm/yyyy format.

As Dave says, you can't. But in addition, mm/yyyy is NOT a date. An Access
Date/Time value is a number, a count of days and fractions of a day since
midnight, December 30, 1899; as such it corresponds to a precise point in
time, not a range of times. The best you can do in this regard is store a
precise date sometime within that month (midnight at the beginning of the
first of the month, say) and use the Format property of form or report
controls to display it in mm/yyyy format. The whole date is still stored,
though; and you may run into ambiguity between day numbers and month numbers
in data entry or in queries.

John W. Vinson [MVP]

 

[Guest]

Thanks for your advice. Originally I was going to be replacing this table
each month, that's why I was trying to rebuild the dated field. Now, we
determined that the table will not be replaced each month, but we will keep
adding to it, so I have added the field to the table in design view. Thanks
for your help anyway.

Kenny A.