Make Table Query-Issue with Date

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

Guest

I created a Make Table query that takes 2 tables that have a relationship and
dump all the info into one table, in addition I added a field that takes the
"Occurrence Date" and formats it to a general date Chart
Date:Format([Occurrence Date],"mm/yyyy") with the format set to Short Date.
Reason I created this field is I have daily entries that I want rolled up in
subsequent queries by the month.

When I run the query and it creates the table the Data Type is set to Text
and not to Date. I run a macro that runs this query based on criteria
selected and then opens charts based on the Make Table but since the Data
Type is Text is puts the dates in number order and not date order.

What is going wrong or is their code I need to write to update the Data Type
of the field to Date, I need it as date.

Thanks!
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

From the Access help article on "Format Function":

"Returns a Variant (String) containing an expression formatted according
to instructions contained in a format expression."

That's why the column is created as a Text data type (string=text).

Why not just put the date in as-is and when you run your monthly queries
just use the Month() function in the SELECT clause and the GROUP BY
clause. E.g.:

SELECT Month(date_column) As theMonth, SUM(numeric_column) As Totals,
.... etc.
FROM ...
WHERE ...
GROUP BY Month(date_column), ...

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQX6YXIechKqOuFEgEQJaeQCg5QL7G/BSQ3/WVzjCGCvH+oe/sOMAoN6v
ARozb0KMVKMpFm5PQR0IVA1C
=W7mQ
-----END PGP SIGNATURE-----
 
Back
Top