Date format changed to text after query. Why?

[klchoy]

I have a query which sums up certain fields by months (values and quantity fields). The query was created via access wizard. I need the output of the query to draw a graph via Excel by using "Get External Data" function (I got tonnes of problem with access drawing graphs). The query somehow changed the format of the "date by month" field to text format.

The data in the table is link to another excel file where the date field has been formatted as date.

What seems to be the problem?

 

[Mile-O]

Do you use the Format() function anywhere in your query? Or the Date$ function?

If so, then these both return strings.

 

[klchoy]

If you don't mind me asking, what are strings?
There are actually some format$ functions used. How does format$(), format(), date$ used?
The query was created using wizard and I'm not familar with these.

 

[Mile-O]

Format(), Format$ etc. changes something to a textual representation.

A String is a variable that holds text.

Think of the data types you can create for fields: text, number, date/time.

The Format() functions basically take a number or date/time value and convert it to a text value. Thus your date's are no longer dates; but text.

 

[klchoy]

Now I get it. =)
Further to that, instead of using format(), format$ functions which turn the data into text representation, what function could be used in order to retain the date format?

Is there a site that I could learn about these functions and how to use them?

 

[WayneRyan]

klchoy,

Don't know of a site offhand.

For queries, you can always create a new field:

TheYear: Format(SomeDateField, "yyyy")

This field does not even have to be visible, you can use
it for sorting & grouping purposes only.

Note: If you tried to apply a comparison to a date, or
use a function like DateAdd to it that would be invalid. It
is no longer a "date".

You can see Access Help or Search here for:
DateAdd, DateDiff, Format, DatePart and others.

Wayne

 

[Mile-O]

If you want to use the date (if it is currently text) for comparisons then enclose it within the CDate() function.

For displaying a date, use the Format() function.

 

[klchoy]

Thanks. Will give it a try.