date formatting question

[keeling1]

I've asked a little about this before, and I've gotten some useful help. Here's what I'm doing. My overall goal is to create a line graph that plots certain data by month. It will plot Open Tags vs. Closed tags depending on the date parameters a user enters. (What Open Tags, etc. means doesn't matter.) But here's what I'm trying to do right now.

The user has already entered two dates into a form. (Sometimes only one date will be entered.) These dates have been stored in a main data table, in a format like 1/1/09. What I want to do is have these dates converted to months and years and then stored. For example, 1/1/09 would become 1/09 or Jan 09. I also need Access to recognize 1/09 as a date, or at least be able to recognize that 12/08 is before 1/09.

I suspect that eliminating the day part of the date will be easy. (I've found a number of different ways to format dates, but I haven't been able to find this one. So if you know how to do this, please tell me!) But I'm not sure whether I should use a table or a query to format the date. It seems obvious that I should use a query, but I have a lot of other things for a query to do after getting the date formatted.

Specifically, after the date gets formatted, I need the query to look at the dates entered by the user and, for each month in the parameters entered, the query needs to calculate how many tags were open during that month and how many were closed. (For instance, if the user enters the date parameters 1/09 and 12/09, the graph will plot open vs. closed tags for each of these 12 months.) The data is cumulative, hence the need for Access to recognize that 12/08 is before 1/09.

So I have two questions: (1) How to format a date like 1/1/09 into one like 1/09 so that Access still recognizes this as a date. And (2) Should I do this with a table or with a query?

Thanks for the help.

 

[The_Doc_Man]

What I want to do is have these dates converted to months and years and then stored. For example, 1/1/09 would become 1/09 or Jan 09.

No you don't want that. You want the date stored literally and then use QUERIES to do conversion when you need to convert.

You would ALWAYS format dates as dates. Then they sort correctly. If the underlying field is sortable, you can have a field in a query that provides you separate month and year values via DatePart function (which you can look up in Help files.)

Also, be aware that because Date is technically a cast (a.k.a. typecast), you will need to reformat it for display purposes. But ALWAYS leave it as a date field since you wish to sort and group in appropriate order.

MS Graph will work correctly for things like X/Y scatter plots if one axis is a date field and the other is a numeric observation tied to that date. You would plot the date field as a number at first, just to get the X/Y plot right. But then, you can click on the date axis and reformat how you want that number displayed, and in that case you would have month/year options available. You might have to play with it a bit.

 

[keeling1]

Thanks for your help, Doc Man. I've gotten some way into this, but I've hit a roadblock.

I've got a query, as you suggested, which separates the month and year for each date entered. Then, I have a second query which is meant to (1) determine whether the date is before a date entered by the user, and (2) for only those entries that are before the date entered, sum the total number of "Yes" entries and the total number of "No" entries from a different field. I thought about using an IF function, such as:

 IF (([DateYear] < ([Forms]![MyForm]![YearOfDate])) OR
     (([DateYear] = ([Forms]![MyForm]![YearOfDate])) AND 
     ([DateMonth] <= ([Forms]![MyForm]![MonthOfDate])))

The "Then" part of the statement, would need to say something like:

 WHERE [Closed] = No, then Sum([Closed])
     WHERE [Closed] = Yes, then Sum([Closed])

As you can see, the apodosis of this If statement contains another field, namely [Closed]. My intuition says that I need to do this a different way, but I can't figure out what other way to do it.

Thanks again.

 

[wazz]

apodosis

 

[keeling1]

Quite right, wazz. Thanks for the correction.