 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / June 2006Converting date entry from mm/dd to mm/yy
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 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 * > 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 [quoted text clipped - 12 lines] > > -eD 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.  SignatureSteve Schapel, Microsoft Access MVP > 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 [quoted text clipped - 12 lines] > > -eD 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 * > Ed, > [quoted text clipped - 12 lines] > so you do not use a Date/Time field at all, replacing it with two > separate fields to record the month and year. 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. SignatureSteve Schapel, Microsoft Access MVP > Hi Ed, > [quoted text clipped - 21 lines] > you would then use the form OnCurrent event to fill out the right > numbers in your unbound control(s) 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 [quoted text clipped - 8 lines] > 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. 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 :) > [quoted text clipped - 73 lines] > > 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. 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. [quoted text clipped - 89 lines] >>>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. 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. SignatureSteve Schapel, Microsoft Access MVP 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. > Ed, > [quoted text clipped - 20 lines] > 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. 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 * > Ed, > [quoted text clipped - 20 lines] > 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. 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 > Hi Ed, > [quoted text clipped - 43 lines] > > 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. 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. SignatureSteve Schapel, Microsoft Access MVP > 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. 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. > Ed, > [quoted text clipped - 4 lines] > control? Sorry about all the questions, but I haven't got a good > picture of how it all hangs together at the moment. 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. SignatureSteve Schapel, Microsoft Access MVP > Don't apologize. I knew there were going to be a bunch of questions. In > order: [quoted text clipped - 39 lines] > records containing all the same info are permitted, due to the existing data > that's going to be input to the database) 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. 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? SignatureSteve Schapel, Microsoft Access MVP > OMG, genious. Pure genious. Works perfectly as described. > [quoted text clipped - 34 lines] > goes over as well as I think it will in the department, possibly a keg...... > or two. Thanks, Steve. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.