By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,456 Members | 1,390 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,456 IT Pros & Developers. It's quick & easy.

Short date problems in a Form

P: 20
Hi

I have an access form in which dates are entered as short dates with an input mask requiring the entry to be in the form of mm/dd/yyyy. Access automatically changes this for single digit dates/months to m/d/yyyy. Is there any way to force the form to keep the date in the original format entered: ie: "01" as opposed to "1". This data is not saved to a table.

I have looked but cannot find this anywhere.

Thank You
Ivan
Dec 24 '06 #1
Share this Question
Share on Google+
19 Replies


100+
P: 1,646
Hi

I have an access form in which dates are entered as short dates with an input mask requiring the entry to be in the form of mm/dd/yyyy. Access automatically changes this for single digit dates/months to m/d/yyyy. Is there any way to force the form to keep the date in the original format entered: ie: "01" as opposed to "1". This data is not saved to a table.

I have looked but cannot find this anywhere.

Thank You
Ivan
Hi Ivan,
Look no further
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.     Text0.Format = "mm/dd/yyyy"
  3. End Sub
Dec 24 '06 #2

P: 20
Thanks I didn't realize you could enter mm/dd/yyyy dirctly into the format section of the form.
Ivan
Dec 24 '06 #3

100+
P: 1,646
Thanks I didn't realize you could enter mm/dd/yyyy dirctly into the format section of the form.
Ivan
Not the format section. Open the code module for the form and paste the code. Change Text0 to the name of your text box to fit.
Now you are a real programmer :)
Dec 24 '06 #4

Expert 5K+
P: 8,435
Not the format section. Open the code module for the form and paste the code. Change Text0 to the name of your text box to fit.
Now you are a real programmer :)
Don't be silly, there's much more to being a programmer than that.

You also have to drink large amounts of either coffee or Buzz Cola. :)

Merry Christmas!
Dec 27 '06 #5

missinglinq
Expert 2.5K+
P: 3,532
And get so engrossed in code that you forget at least one crucial social event per week!
Dec 27 '06 #6

Expert 5K+
P: 8,435
And get so engrossed in code that you forget at least one crucial social event per week!
Um... "social event"? I'm sure I've heard that expression somewere... :)
Dec 27 '06 #7

100+
P: 1,646
Um... "social event"? I'm sure I've heard that expression somewere... :)
Yup, it's next to the click event... er isn't it?
Dec 27 '06 #8

NeoPa
Expert Mod 15k+
P: 31,186
Thanks I didn't realize you could enter mm/dd/yyyy dirctly into the format section of the form.
Ivan
The format property of your TextBox will give you the facility to control how it's displayed.
If it's to be saved (not in this case clearly), then you can use Will's code in the AfterUpdate event.
Dec 30 '06 #9

P: 20
Thanks for all your answers.

Actually the format property of the text box accepted mm/dd/yyyy and let me accomplish what I needed.

For the same project I had another question which I posted under the title "ADOX and linked tables". I will repost that here. Does anyone have any ideas?

And YES, it is 5:00 in the morning and I am working on code, that almost qualifies me as a real programer.

Thanks Ivan

Hi

I have data tables in the form of

200612...
200701...
200702...
etc

The user specifies the start date and end date in an input form ie: 12/25/2006 - 1/10/2007.

I want to use access to link to the requested tables but maintain the internal structure already set up in access (relationships, queries, reports, etc). The tables being linked to will change frequently.

My still incomplete code follows below.

The "tbl.Properties("Jet OLEDB:Remote File Name") = strGetDataFrom" stays undefined.

What do I need to add/change to change the Remote File being accessed?

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. Dim AppointmentYear As String
  4. Dim AppointmentMonth As String
  5. Dim strGetDataFrom As String
  6.  
  7. Dim SeeIfMoreThanOneMonth As Integer
  8.  
  9. Dim cat As ADOX.Catalog
  10. Dim tbl As ADOX.Table
  11.  
  12. Set cat = New ADOX.Catalog
  13. Set tbl = New ADOX.Table
  14.  
  15. With cat
  16. .ActiveConnection = _
  17. "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  18. "Data Source=C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb;"
  19. End With
  20.  
  21. SeeIfMoreThanOneMonth = DateDiff("m", Forms!frmReminderDates![Start Date], Forms!frmReminderDates![End Date])
  22.  
  23. Select Case SeeIfMoreThanOneMonth
  24.  
  25. Case 0
  26. AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![End Date]), 2)
  27. AppointmentMonth = DatePart("m", Forms!frmReminderDates![End Date])
  28.  
  29. If AppointmentMonth < 10 Then
  30. strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
  31. Else
  32. strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
  33. End If
  34.  
  35. For Each tbl In cat.Tables
  36.  
  37. If tbl.Name = "tblRecall" Then
  38. tbl.Properties("Jet OLEDB:Create Link") = True
  39. tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
  40. tbl.Properties("Jet OLEDB:Remote File Name") = strGetDataFrom
  41. End If
  42.  
  43. Next
  44.  
  45. Case 1
  46. AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![Start Date]), 2)
  47. AppointmentMonth = DatePart("m", Forms!frmReminderDates![Start Date])
  48.  
  49. LastDayInMonth = DateAdd("m", 1, DateSerial(Val(AppointmentYear), Val(AppointmentMonth), 1)) - 1
  50.  
  51.  
  52. If AppointmentMonth < 10 Then
  53. strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
  54. Else
  55. strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
  56. End If
  57.  
  58. Case Else
  59. Debug.Print "Please re-eneter dates"
  60.  
  61. End Select
  62.  
  63.  
  64. End Sub
Thanks
Ivan

--------------------------------------------------------------------------------
Dec 30 '06 #10

NeoPa
Expert Mod 15k+
P: 31,186
Ivan,

I've put code tags around your code for readability and compliance with forum regulations/guidelines.
On to the question :
It's really not a good idea in an RDBMS to split similar data up into separate tables.
The recommended way to handle this is to put an identifier in your record layout which can be used to differentiate between months.
That way you can select the items in the WHERE or HAVING clauses as designed.
Dec 30 '06 #11

P: 20
Unfortunately, this isn't my data design. It comes from a very old program that we hope to get 3 more years use out of but would like to increase the functionality of what we have. Altering the data structure at this time would definately be more trouble than it is worth.

I assume I either need something else in the following part of my code or I have an ommision, I do not know which. Sorry about not using the code tags earlier.

Expand|Select|Wrap|Line Numbers
  1.    For Each tbl In cat.Tables
  2.  
  3.        If tbl.Name = "tblRecall" Then
  4.             tbl.Properties("Jet OLEDB:Create Link") = True
  5.             tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
  6.             tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
  7.         End If
  8.  
  9.     Next

The more complete code:


Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Open(Cancel As Integer)
  2.  
  3. Dim AppointmentYear As String
  4. Dim AppointmentMonth As String
  5. Dim strGetDataFrom As String
  6.  
  7. Dim SeeIfMoreThanOneMonth As Integer
  8.  
  9. Dim cat As ADOX.Catalog
  10. Dim tbl As ADOX.Table
  11.  
  12. Set cat = New ADOX.Catalog
  13. Set tbl = New ADOX.Table
  14.  
  15. With cat
  16.     .ActiveConnection = _
  17.     "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  18.     "Data Source=C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb;"
  19. End With
  20.  
  21. SeeIfMoreThanOneMonth = DateDiff("m", Forms!frmReminderDates![Start Date], Forms!frmReminderDates![End Date])
  22.  
  23. Select Case SeeIfMoreThanOneMonth
  24.  
  25. Case 0
  26.     AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![End Date]), 2)
  27.     AppointmentMonth = DatePart("m", Forms!frmReminderDates![End Date])
  28.  
  29.     If AppointmentMonth < 10 Then
  30.         strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
  31.     Else
  32.         strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
  33.     End If
  34.  
  35.     For Each tbl In cat.Tables
  36.  
  37.        If tbl.Name = "tblRecall" Then
  38.             tbl.Properties("Jet OLEDB:Create Link") = True
  39.             tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
  40.             tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
  41.         End If
  42.  
  43.     Next
  44.  
  45. Case 1
  46.     AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![Start Date]), 2)
  47.     AppointmentMonth = DatePart("m", Forms!frmReminderDates![Start Date])
  48.  
  49.     LastDayInMonth = DateAdd("m", 1, DateSerial(Val(AppointmentYear), Val(AppointmentMonth), 1)) - 1
  50.  
  51.  
  52.     If AppointmentMonth < 10 Then
  53.         strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
  54.     Else
  55.         strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
  56.     End If
  57.  
  58. Case Else
  59.     Debug.Print "Please re-eneter dates"
  60.  
  61. End Select
  62.  
  63.  
  64. End Sub
Thanks
Ivan
Dec 30 '06 #12

NeoPa
Expert Mod 15k+
P: 31,186
Expand|Select|Wrap|Line Numbers
  1. For Each tbl In cat.Tables
  2.  
  3.     If tbl.Name = "tblRecall" Then
  4.         tbl.Properties("Jet OLEDB:Create Link") = True
  5.         tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
  6.         tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
  7.     End If
  8.  
  9. Next tbl
(Notice Next tbl)
would seem to be equivalent to :
Expand|Select|Wrap|Line Numbers
  1. Set tbl = cat.TableDefs("tblRecall")
  2. tbl.Properties("Jet OLEDB:Create Link") = True
  3. tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
  4. tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
I'm not sure about catalogues but that's certainly how I'd do it within an Access database.
(Actually I'd use With cat.TableDefs("tblRecall"), but that's the most similar to your code.)
Dec 30 '06 #13

Expert 5K+
P: 8,435
...(Notice Next tbl)
Is that important? I have a strict rule of always leaving out the variable name in a Next statement, but I think that's rooted in a very old version of (probably Visual) Basic where the code executed slower with the variable name specified. May not apply any more.
Dec 30 '06 #14

NeoPa
Expert Mod 15k+
P: 31,186
To be honest - I thought it was a mandatory part of the syntax.
It appears that it is not. I believe it's good practice though - consider the situation where you have nested For loops. Which is easier to maintain? (Rhetorical question - A lone 'Next' is not the answer!)
Dec 31 '06 #15

Expert 5K+
P: 8,435
To be honest - I thought it was a mandatory part of the syntax.
It appears that it is not. I believe it's good practice though - consider the situation where you have nested For loops. Which is easier to maintain? (Rhetorical question - A lone 'Next' is not the answer!)
I've always found that correct indenting makes a much bigger difference to readability than including the variable name. And having the name on there is a nuisance when copying chunks of code. Just a matter of personal preference, really.
Dec 31 '06 #16

100+
P: 1,646
I've always found that correct indenting makes a much bigger difference to readability than including the variable name. And having the name on there is a nuisance when copying chunks of code. Just a matter of personal preference, really.
There is no possibility of me ever leaving out the variable name at the end of a next loop even if I comment it out. Just too tiresome to debug without it. This is especially true for me with c++ where there is only closing curly braces for loops and if statements. Crazy code to read.
Dec 31 '06 #17

P: 20
Thanks for your responses

I agreee that "tabledefs" and "refreshlink" using DAO would have been my first try, but like many others we are migrating away from DAO and using ADO. This is my first try with ADOX.

Unfortunately, my lack of experience with ADOX is showing as I still don't know what is wrong with my code.

Any other ideas would be greatly appreciated.

Thanks
Ivan
Dec 31 '06 #18

NeoPa
Expert Mod 15k+
P: 31,186
I've always found that correct indenting makes a much bigger difference to readability than including the variable name. And having the name on there is a nuisance when copying chunks of code. Just a matter of personal preference, really.
Personal preference then Killer :).
I expect you're quite fastidious with your indentation, but I've seen too many weirdly indented code for me ever to rely on it. Like you I use it and it's very helpful to me but away from my own code - it's often more of a problem than a help.
Dec 31 '06 #19

NeoPa
Expert Mod 15k+
P: 31,186
I would help if I could - unfortunately I know very little about ADO.
Most of my data processing work is in SQL and when I do need to work with RecordSets I tend to use DAO. DAO is fine and, contrary to M$'s attempts, is still thriving in most of AccessLand :).
Dec 31 '06 #20

Post your reply

Sign in to post your reply or Sign up for a free account.