Converting date entry from mm/dd to mm/yy

[Gnowor]

Right now I've got a text box on my form that is formated mm/yy. In the
table in the date field it is formatted mm/yy (even though I know it's really
stored mm/dd/yy). When users go to enter info, they think they just need to
enter mm/yy, but access reads that as mm/dd, and inputs the current year and
displays, mm/current year.

Example:
User input: 06/03 - meaning June, 2003
Access sees: June 3, 2006
Access displays: 06/06

Is there anyway I can write some code to reformat the users entry to
06/01/03, just so Access will see it right. The day of the month doesn't
matter, so I could be a constant such as 1. Thanks for your help.

-eD

 

[strive4peace]

try using mm/yyyy or mm-yyyy for the format


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Steve Schapel]

Ed,

As you have apparently recognised, a date field requires all 3 components.

It would be possible to use an Input Mask, so that the Day portion of
the date is automatically entered.

As far as I know, the only other way would be to use an unbound textbox
for your data entry, and code on the the After Update event of such
textbox to write the date to the table. Something like this should work...
Me.YourDateField = CDate(Left(Me.YourTextbox, 3) & "01/" &
Right(Me.YourTextbox, 2))

Other than that, you may consider changing the design of your database,
so you do not use a Date/Time field at all, replacing it with two
separate fields to record the month and year.

 

[strive4peace]

Hi Ed,

Normally, I would say Steve is correct about a date needing
all 3 components, but ... using "mm/yyyy" for the format
does work properly -- it doesn't have anything to do with
the format code, however, it is entering the 4 digit year

you may want to make sure the Day entered is 1, however on
the control AfterUpdate event

'~~~~~~~~~~~~~~~~~~
if isnull(me.datecontrol) then exit sub
if day(me.datecontrol)<>1 then
me.datefield = DateSerial( _
year(me.datecontrol),
month(me.datecontrol),1)
end if
'~~~~~~~~~~~~~~~~~~


another thought is just to collect a month number in an
unbound control and, on its afterupdate event, construct the
datefield using the current year (or have a seperate unbound
control for year and use that)

you would then use the form OnCurrent event to fill out the
right numbers in your unbound control(s)

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Steve Schapel]

That's interesting, Crystal. Thanks. I knew this would work in my own
country, where we do our dates in a logical order ;-) but I didn't think
it would work in the US, and seemed to remember a previous discussion
where it was concluded that it wouldn't. As far as I am aware, you
wouldn't need to change the format at all, as long as the 4 digit year
is what is typed when entering. (Note to Ed: the format only affects
the display, not the value... your statement "I know it's really stored
mm/dd/yy" is actually not correct. You can enter a date in a form in
any date-recognisable way, regardless of the format setting of the
control.) In any case, I didn't pursue this with Ed, given that he had
specifically mentioned that he wanted to enter a 2 digit year. If I was
him, I would still use separate fields for month and year.

 

[strive4peace]

you're welcome, Steve

I like doing dates logically too ... but in America, it just
confuses people! When I put a datestamp on my backup
filenames, I use yymmdd so they will sort chronologically --
and I always have to explain it

Note to Ed --
tagging on to what Steve said about how dates are stored...
(since you brought it up, Steve )

Access stores date/times in a numeric format where the
integer portion of the number represents the date and the
decimal portion of the number represents time:

1/1/100 --> -657434
1/2/100 --> -657433
12/30/1899 --> 0
1/1/1950 --> 18264
1/1/2005 --> 38353
1/1/9999 --> 2958101

the time is a fraction of the day

12 noon is 0.5
6pm is 0.75

1/1/2005, 12 noon --> 38353.5

if you have a control with just a date and you want to make
sure it converts to a whole number (or it is stored in text
format), use

DateValue([control_or_fieldname])
or
cLng([control_or_fieldname])

likewise, if you have a time, you can force it to the
fractional part by

TimeValue([control_or_fieldname])

since dates are whole numbers and times are the fractions,
you can also do arithmetic operations on them

that is why you can subtract one date from another and get
the number of days between the two.

Because dates can also have a time component, it is handy to
use DateDiff and DateAdd, which let you specify the time
increment (year, month, day, hour, etc) to calculate new
dates or get a difference between dates.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Gnowor]

I actually only saw the first response to this whole thread before I left
work on Friday. Didn't know I was going to start something this big. LOL.
I had just set it up for 4 digit year, and I think I'm going to stick with
that, just for ease of use for the user. The form is set up with two
sub-datasheets, and given feedback from the people I'm designing the database
for, the sub-datasheet is easier to use than the subform, so by just storing
the info in mm/yyyy (and having an input mask so they can't mess it up) it's
working great.

I have to say one of the reasons I love these forums is that even though the
first solution worked, I really like seeing the different approaches to the
problem. It really, REALLY, helps my n00b brain wrap itself around some of
these concepts in Access. Thank you Steve and Crystal. You're both amazing.

-eD

you're welcome, Steve

I like doing dates logically too ... but in America, it just
confuses people! When I put a datestamp on my backup
filenames, I use yymmdd so they will sort chronologically --
and I always have to explain it

Note to Ed --
tagging on to what Steve said about how dates are stored...
(since you brought it up, Steve )

Access stores date/times in a numeric format where the
integer portion of the number represents the date and the
decimal portion of the number represents time:

1/1/100 --> -657434
1/2/100 --> -657433
12/30/1899 --> 0
1/1/1950 --> 18264
1/1/2005 --> 38353
1/1/9999 --> 2958101

the time is a fraction of the day

12 noon is 0.5
6pm is 0.75

1/1/2005, 12 noon --> 38353.5

if you have a control with just a date and you want to make
sure it converts to a whole number (or it is stored in text
format), use

DateValue([control_or_fieldname])
or
cLng([control_or_fieldname])

likewise, if you have a time, you can force it to the
fractional part by

TimeValue([control_or_fieldname])

since dates are whole numbers and times are the fractions,
you can also do arithmetic operations on them

that is why you can subtract one date from another and get
the number of days between the two.

Because dates can also have a time component, it is handy to
use DateDiff and DateAdd, which let you specify the time
increment (year, month, day, hour, etc) to calculate new
dates or get a difference between dates.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

That's interesting, Crystal. Thanks. I knew this would work in my own
country, where we do our dates in a logical order ;-) but I didn't think
it would work in the US, and seemed to remember a previous discussion
where it was concluded that it wouldn't. As far as I am aware, you
wouldn't need to change the format at all, as long as the 4 digit year
is what is typed when entering. (Note to Ed: the format only affects
the display, not the value... your statement "I know it's really stored
mm/dd/yy" is actually not correct. You can enter a date in a form in
any date-recognisable way, regardless of the format setting of the
control.) In any case, I didn't pursue this with Ed, given that he had
specifically mentioned that he wanted to enter a 2 digit year. If I was
him, I would still use separate fields for month and year.

 

[strive4peace]

you're welcome, Ed happy to help


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

I actually only saw the first response to this whole thread before I left
work on Friday. Didn't know I was going to start something this big. LOL.
I had just set it up for 4 digit year, and I think I'm going to stick with
that, just for ease of use for the user. The form is set up with two
sub-datasheets, and given feedback from the people I'm designing the database
for, the sub-datasheet is easier to use than the subform, so by just storing
the info in mm/yyyy (and having an input mask so they can't mess it up) it's
working great.

I have to say one of the reasons I love these forums is that even though the
first solution worked, I really like seeing the different approaches to the
problem. It really, REALLY, helps my n00b brain wrap itself around some of
these concepts in Access. Thank you Steve and Crystal. You're both amazing.

-eD

:

you're welcome, Steve

I like doing dates logically too ... but in America, it just
confuses people! When I put a datestamp on my backup
filenames, I use yymmdd so they will sort chronologically --
and I always have to explain it

Note to Ed --
tagging on to what Steve said about how dates are stored...
(since you brought it up, Steve )

Access stores date/times in a numeric format where the
integer portion of the number represents the date and the
decimal portion of the number represents time:

1/1/100 --> -657434
1/2/100 --> -657433
12/30/1899 --> 0
1/1/1950 --> 18264
1/1/2005 --> 38353
1/1/9999 --> 2958101

the time is a fraction of the day

12 noon is 0.5
6pm is 0.75

1/1/2005, 12 noon --> 38353.5

if you have a control with just a date and you want to make
sure it converts to a whole number (or it is stored in text
format), use

DateValue([control_or_fieldname])
or
cLng([control_or_fieldname])

likewise, if you have a time, you can force it to the
fractional part by

TimeValue([control_or_fieldname])

since dates are whole numbers and times are the fractions,
you can also do arithmetic operations on them

that is why you can subtract one date from another and get
the number of days between the two.

Because dates can also have a time component, it is handy to
use DateDiff and DateAdd, which let you specify the time
increment (year, month, day, hour, etc) to calculate new
dates or get a difference between dates.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

That's interesting, Crystal. Thanks. I knew this would work in my own
country, where we do our dates in a logical order ;-) but I didn't think
it would work in the US, and seemed to remember a previous discussion
where it was concluded that it wouldn't. As far as I am aware, you
wouldn't need to change the format at all, as long as the 4 digit year
is what is typed when entering. (Note to Ed: the format only affects
the display, not the value... your statement "I know it's really stored
mm/dd/yy" is actually not correct. You can enter a date in a form in
any date-recognisable way, regardless of the format setting of the
control.) In any case, I didn't pursue this with Ed, given that he had
specifically mentioned that he wanted to enter a 2 digit year. If I was
him, I would still use separate fields for month and year.

 

[Steve Schapel]

Ed,

... It really, REALLY, helps my n00b brain wrap itself around some of
these concepts in Access.

Well, if you really want to wrap around concepts , here's one that is
really central to this discussion, but hasn't been mentioned yet... the
Data Type of the field. You have a Date/Time field. Is that the
correct decision? I'm not sure, because we haven't got enough
information about what you are using this for, and how you are using it.
It's not enough to say that you can see this sort of information on a
calendar, and therefore it's Date. In a database, these decisions are
based on the required functionality. So here's how I would read it...
you want the month and the year, it doesn't matter about the day, means
it is very likely that you do not want date functionality. So yes, it
is possible to shove it into a Date/Time field, using techniques as
discussed in this thread. But the question still looms - why? Unless
there is something I don't know yet about your database to make me
change my mind, at the moment it looks to me that you don't want a date,
you want the month and year. As such, I wouldn't use a Date/Time data
type field. I would either use 2 Number fields, one for month and one
for year, or else I would use a text field to store like 06/06. My
decision between these two options would depend on how I wanted to use
the information, but I really don't think it's a date.

 

[Gnowor]

I agree with you there. Problem is, the reason for the creation of this
database is that the spreadsheet that was started several years ago (before
my time) has become very unwieldly to the point where people were essentially
running queries by hand, and it took them 10-15 minutes a pop. I have a
bunch of data that I need to import from excel, as well as giving people the
ability to add to that information. The existing cells in excel are in the
mm/yyyy format. Although I'm sure I could write some sort of update query to
split the cells, or store the mm/yyyy format as general text in one field and
write a sub to make sure it's a valid date, I'm under a bit of time crunch
and need to get this database on it's feet by the end of the week.

Best solution would most likely be the subform with an unbound date control
that used AfterUpdate to split it into mm/yyyy fields to be stored.
Unfortunately this is one of many problems I'm running into.

Oh, did I mention that my current position is that of department assistant?
I'm the guy that's supposed to answer phone calls, schedule appointments, and
sort department mail. And yet when I suggest that their spreadsheet isn't
very functional and that it should be a database, they say "take care of it,
ed". And that's how my first foray into database design began. Hey,
whatever gets my foot in the door, right? If I learn what I'm doing one of
these days, I could probably triple my salary.

Thanks again for all your help.

 

[strive4peace]

Hi Ed,

I definitely agree with Steve (good point ) -- since you
are never going to want your "date" to be any day but 1,
(your values won't compare if it is), it is best not to
include a meaningless (and possibly troublesome) day when
you store the value

If you need a real date for date range reporting (for
instance), you can construct it for comparison.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[Gnowor]

So I tried posting this separately but wasn't getting any responses, so I'm
hoping one of you helpful people is still watching this post.

---------------------------------

So I have a table that has information (phone numbers, emails, etc.) for a
bunch of sales agents. I have a form based on this table. I also have a
table that has the dates of sales for each agent, that appears as a subform
on the main info form.

My problem is that some of these agents are members of a team, and when I
add a date of a sale to one member of the team, I want it to add that date to
all members of the team.

The subform right now has an unbound control that pulls up the dates of
sales, and if you type in new info into that unbound control, it adds a
record for that agent, with the sales date you entered.

I was thinking of using afterupdate to add the records to all members of the
team. I have the if statement, but then i draw a blank as to the code that
would go through, find each member of that team and add the record for them.
the if statement would go like:

if Forms!InfoForm!team <> ""

Do I need to build a query that takes Forms!InfoForm!team as a parameter to
find all members of that team? Once I have that, how to I add the record for
each agent that the query returns?

Advice greatly appreciated! Thanks!

-eD

 

[Steve Schapel]

Ed,

I think I would need to know how you know (or how the computer knows)
which agents are in which teams. Do you have this recorded in a field
in an Agents table, or some such? And by what mechanism is a sales date
added as a record for the agent? And why are you using an unbound
control? Sorry about all the questions, but I haven't got a good
picture of how it all hangs together at the moment.

 

[Gnowor]

Don't apologize. I knew there were going to be a bunch of questions. In
order:

In the Agents table, there is a field called team, and this field is the
same for each agent that is part of each team. (i.e. John Brown and John
Smith are on team John, entry in the team field on the Agents table is "John")

There's another table called Sales, that has one field that is an agent's
name (linked to the Agents table), and another field that is the sales date.
The subform simply displays one sales date for the currently displayed agent,
and you can flip through the dates using the navigation buttons.

The reason for the unbound control is that I was thinking the best way to
implement the added of records to the sales table was via the unbound
control. There's a invisible bound text field on the subform that populates
the unbound control. The logic behind the unbound control was going to be
something like

Private Sub DateUnbound_AfterUpdate()

Dim db As Database
Set db = CurrentDb
Dim AgentNameTemp As String
AgentNameTemp = Forms![Agent Editor]![AgentName]
Dim DateTemp As String
DateTemp = Forms![Agent Editor]![Sales subform]!DateUnbound


If IsNull(Forms![Agent Editor]![Team]) Then
db.Execute "INSERT INTO Sales ([AgentName],[Date]) VALUES (""" &
AgentNameTemp & """,""" & DateTemp & """)", dbFailOnError
Else
' Here's the part where I need help. I want this to select Agents
with the same Team value, and add a record for each to my Sales table, with
the same Date value, from DateUnbound.
End If
End Sub

I was thinking that if it was a bound control and the record was added to
John Brown, then when you find all agents with the same "Team" value, it will
add a record to John Smith and a duplicate record to John Brown. (Duplicate
records containing all the same info are permitted, due to the existing data
that's going to be input to the database)

Let me know if you've got any other questions.

 

[Steve Schapel]

Ed,

Here's how I would go about it. Forget the unbound controls thing... I
hardly know you, but you're still too good a friend for me to allow you
to persist with that idea . Change the name of Date to SalesDate
(date is a "reserved word" in Access (i.e. has a special meaning), and
as such should not be used as the name of a field or control). Enter
the date straight into the SalesDate control on the subform.

Private Sub Date_AfterUpdate()
Me.Dirty = False
DBEngine(0)(0).Execute "INSERT INTO Sales ( AgentName, SalesDate )" & _
" SELECT AgentName, " & CLng(Me.SalesDate) & _
" FROM Agents" & _
" WHERE Team = '" & Me.Parent!Team & "'" & _
" AND AgentName <> '" & Me.AgentName & "'", dbFailOnError
End Sub

A couple of further comments...

- You are using the AgentName field as a primary key for agents. This
practice is generally avoided, based mainly on the fact that uniqueness
cannot be guaranteed... even if it is unlikely, it is always possible to
have two agents with the same name. In the absence of any "real life"
unique identifier for agents, I would add an Autonumber AgentID field to
the Agents table, and also use this instead of the name in the Sales
table. The above code would then look like this...
Private Sub Date_AfterUpdate()
Me.Dirty = False
DBEngine(0)(0).Execute "INSERT INTO Sales ( AgentID, SalesDate )" & _
" SELECT AgentID, " & CLng(Me.SalesDate) & _
" FROM Agents" & _
" WHERE Team = '" & Me.Parent!Team & "'" & _
" AND AgentID <> " & Me.AgentID, dbFailOnError
End Sub

- Using the After Update event of SalesDate, there is the potential
for a problem if the date entered is subsequently edited for some
reason, in which case the code will run again, and duplicated records
will result. Not only that, but the dates in the existing records for
other team mambers will now be incorrect. You need to handle this in
some way.

--
Steve Schapel, Microsoft Access MVP

Don't apologize. I knew there were going to be a bunch of questions. In
order:

In the Agents table, there is a field called team, and this field is the
same for each agent that is part of each team. (i.e. John Brown and John
Smith are on team John, entry in the team field on the Agents table is "John")

There's another table called Sales, that has one field that is an agent's
name (linked to the Agents table), and another field that is the sales date.
The subform simply displays one sales date for the currently displayed agent,
and you can flip through the dates using the navigation buttons.

The reason for the unbound control is that I was thinking the best way to
implement the added of records to the sales table was via the unbound
control. There's a invisible bound text field on the subform that populates
the unbound control. The logic behind the unbound control was going to be
something like

Private Sub DateUnbound_AfterUpdate()

Dim db As Database
Set db = CurrentDb
Dim AgentNameTemp As String
AgentNameTemp = Forms![Agent Editor]![AgentName]
Dim DateTemp As String
DateTemp = Forms![Agent Editor]![Sales subform]!DateUnbound


If IsNull(Forms![Agent Editor]![Team]) Then
db.Execute "INSERT INTO Sales ([AgentName],[Date]) VALUES (""" &
AgentNameTemp & """,""" & DateTemp & """)", dbFailOnError
Else
' Here's the part where I need help. I want this to select Agents
with the same Team value, and add a record for each to my Sales table, with
the same Date value, from DateUnbound.
End If
End Sub

I was thinking that if it was a bound control and the record was added to
John Brown, then when you find all agents with the same "Team" value, it will
add a record to John Smith and a duplicate record to John Brown. (Duplicate
records containing all the same info are permitted, due to the existing data
that's going to be input to the database)

 

[Gnowor]

OMG, genious. Pure genious. Works perfectly as described.

First, for the thoughts on the AgentName. In case you hadn't figured out
yet, this is the first database that I've built and wasn't grasping certain
concepts when I jumped in. I realized a little too late that the AgentID
would've been a better idea, so I added an option so that if you want to add
a duplicate name, you click a button, select the name that's going to be
duplicated, it changes that name so it's now followed by the Office Name in
() and if it already has the Office name, it adds a number, then inserts a
new record with the same name, and the new office you select. Realize that's
a massively long way around a simple problem, but due to the other forms and
reports I already had built, it's a functional solution. (Yes, I know......
I've gotta stop beating myself with the stupid stick.)

Secondly, I see your point regarding the duplicate entries if the record is
edited. That was another reason why I started with the idea of the unbound
control (as it would not allow editing to existing records, since it doesn't
repopulate the field that populates it (that sounded confusing, moving on.)

Here's a thought. Lock the Date field. I already have custom record
navigation command buttons, as well as a delete button. What if I add an
"add record" command button, that pops up a new form with unbound controls (I
can't seem to get away from that), takes input from the user, and then runs
query you provided to me, now without the "where" clause. I'm thinking the
unbound controls would not be a burden in this instance, because I not trying
to have them do two things. What do you think?

Everything I just proposed is well within my grasp (now that you've pointed
me in the right direction, that part about Date being a reserved word is what
was messing me up all along). One part I'm going to stumble over is the
select the just added record. If you could impart the knowledge upon me on
how to do that real quick, that'd be great, if not, I've stumbled through
worse in this whole process.

In case I didn't mention it before, you're the man, Steve. And if you've
ever in Concord, CA, I'll definitely get you at least one beer, and if this
goes over as well as I think it will in the department, possibly a keg......
or two. Thanks, Steve.

 

[Steve Schapel]

Ed,

Regarding "I've gotta stop beating myself with the stupid stick", I
think it's more the "strangling thyself with the over-complication wire"
that's more troubling .

It's just that I wasn't sure about the details of the business process.
But, would this be true... if the date is being entered on a new
record, it should be added for the other team members, and if it is
being edited in an existing record, it means there was initially a typo
and the alteration should be propogated to all? Is that true? If so,
maybe this will work...

Private Sub SalesDate_AfterUpdate()
Dim IsItNew As Boolean
Dim strSQL As String
IsItNew = Me.NewRecord
Me.Dirty = False
If IsItNew Then
strSQL = "INSERT INTO Sales ( AgentName, SalesDate )" & _
" SELECT AgentName, " & CLng(Me.SalesDate) & _
" FROM Agents" & _
" WHERE Team = '" & Me.Parent!Team & "'" & _
" AND AgentName <> '" & Me.AgentName & "'", dbFailOnError
Else
strSQL = "UPDATE Sales SET SalesDate =" & CLng(Me.SalesDate) & _
" WHERE Team = '" & Me.Parent!Team & "'" & _
" AND SalesDate = " & CLng(Me.SalesDate.OldValue), dbFailOnError
End If
DBEngine(0)(0).Execute strSQL, dbFailOnError
End Sub

Well, not 100% sure about this, I'm still shooting in the dark a bit.
This won't work if more than one Sales record can be entered for an
Agent on any given date. But then, I suppose you also have some other
fields that can be used to identify?

By the way, if you ever feel the words "unbound control" creeping
insiduously up into your thoughts, you should stand on your head, take
10 deep breaths, and then shout "I love Access because of bound
controls". Ok?