Re: Make Table Query-Issue with Date

From: MGFoster (me_at_privacy.com)
Date: 10/26/04 

Date: Tue, 26 Oct 2004 18:33:47 GMT

-----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-----
SMac wrote:
> 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.

Relevant Pages

  • Re: Date Format problem in parameter query
    ... it sounds like Access is confused about the data type of the field. ... Are you applying this on a calculated field? ... In query design view, choose Parameters on the Query menu. ... > its Format property...nothing fancy ...
    (microsoft.public.access.queries)
  • RE: Inserting a MS Access Query as a field using Office 2002
    ... format than a default short format. ... One is to change the connection method back to DDE. ... be to set up your query to do the transformation to text on the fly. ... > the data type to text, however can anyone tell me what I can do to make it ...
    (microsoft.public.word.mailmerge.fields)
  • Re: more date problems...
    ... It should be a Date data type column. ... I don't know if VBScript has the Format() function, ... This will format the date in a format that SQL understands. ... How can I modify my query so that it reformats the date. ...
    (comp.databases.ms-sqlserver)
  • Re: Comparing different date types in a query
    ... >You mean the format, or the data. ... >If one data type is string, you MAY be able to get ... >> How do you use two different date types in a query? ...
    (microsoft.public.access.queries)
  • Make Table Query-Issue with Date
    ... dump all the info into one table, in addition I added a field that takes the ... When I run the query and it creates the table the Data Type is set to Text ... selected and then opens charts based on the Make Table but since the Data ... What is going wrong or is their code I need to write to update the Data Type ...
    (microsoft.public.access.queries)
Quantcast