formatting dates

[Guest]

Please help me, I posted this question once already on another thread and the
answer was over my head, I thought I would try here this time, I think this
seems easy, if not, please tell me and I will give up.

I have made a table that I want to enter a date, but only the month and
year. I tried changing the date to just a number, but then it dropped the 2
digit month and it won't sort like a date would, so I need help. ( I need to
sort by month and year)

I think this is a mask in the formatting screen of my table. Or do I need
to do this in a form, or is it not possible at all?

In your responses, please tell me if I should be in a form, or at the table,
and if you are telling me insrtructions for a mask or for validation, or
whatever, I am totally teaching this stuff to myself and am still learning a
lot.

 

[Guest]

I don't have enough information to give you a totally complete answer, but I
will get started, and you can fill me in where there are gaps.

First, you say you want to sort by month and year. In reality, you should
sort by year and month. This will sort your data so that all the months for a
year will be together. If you sort by month and year, you would get all the
Januarys followed by all the Februarys, etc.

So, make the field in your table a text field 6 characters long. Then to
format the date correctly, use this:

=Format(TheDate,"yyyymm")

This assumes TheDate is a date/time data type and that you want a 4 digit
year and 2 digit month. That is the way it should be. Now, as to where to
put this, it depends on what you are doing. If you are typing it directly
into the table, there is no need for the format, just type it in like 200510
(October, 2005). If you are using a form to input data, for this type of
operation it gets a little tricky, because you want your data to go into a
field in the table in a different format than it is on the form. So here is
what you need to do.
Make the textbox you enter the date in an unbound control. (for example we
will call it txt.InputDate)
Create another textbox and bind it to the date field. (We will call it
txtFormatDate)
Set the following properties for txtFormatDate to these values:
Visible - No
Enabled - Yes
Locked - Yes
Tab Stop - No

You will not see this control and you will not be able to tab to it. That
is what you want. Then, to get the formatted value form txtInputDate to
txtFormatDate, Put the following code in the After Update event of
txtInputDate:

If Not IsNull(Me.txtInputDate) Then
Me.txtFormatDate = Format(Me.txtInputDate,"yyyymm")
End If

That should do it. If you have questions, post back and we'll see if we can
get it working.

 

[Guest]

You are right when you stated I need to sort by year, that is what I meant,
just didn't say it right. And then after that I just hear the voices from
the Charlie Brown cartoons "wha, wha, wha, wha, wha".

Let's start with my table. Are you saying that it should be a text field?
This confuses me so I am just clarifying it. I named the field Date and I
made it text. Then in the format box I typed in =Format(TheDate,"yyyymm"),
but it switched itself to "=For"m"at(T"h\ed"ate,yyyymm)". So right there I
am sure I messed up already. Where should I have put that formula? (I tried
it in the mask also, but it did the same thing.

I will be using a form with this table, so if you don't mind getting me
through this first step, and then I'll move on to the form.

Just some background on what I am doing. The person who had my job before
preffered to use Panorama, (a Mac program.) I have learned it enough to
start transferring the data to Access which I prefer to use,but don't know it
either, since I am using Windows. This particlular project is for our
school. My fields include the month and year along with about 15 programs.
My boss has asked me to get a report to him with 20 years worth of September
enrollment numbers. I have successfully transferred all the info into Access
with the exception of this darn date. He wants it by Friday so if I have to
I will manually put it all into excel, but I want this up and running for any
future use.

It just donned on me I could seperate the month and year into 2 fields if
all else fails, but it just wouldn't be right.

Thanks again for the help.

 

[Guest]

See responses below:

You are right when you stated I need to sort by year, that is what I meant,
just didn't say it right. And then after that I just hear the voices from
the Charlie Brown cartoons "wha, wha, wha, wha, wha".

Let's start with my table. Are you saying that it should be a text field?

Yes, it is easier to manipulate that way.

This confuses me so I am just clarifying it. I named the field Date and I

Don't name a field Date. It is a reserved word in Access.

made it text. Then in the format box I typed in =Format(TheDate,"yyyymm"),

Not in the Format property box, Look at the code below with the If statement
and put that in the After Update event of txtInputDate

but it switched itself to "=For"m"at(T"h\ed"ate,yyyymm)". So right there I
am sure I messed up already. Where should I have put that formula? (I tried
it in the mask also, but it did the same thing.

I will be using a form with this table, so if you don't mind getting me
through this first step, and then I'll move on to the form.

Go back and reread my post carefully. I think you are on the right track.
Post back if you have more questions. We only have 2 days to get this done