Short date problems in a Form

[racquetballguy]

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

[willakawill]

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

Private Sub Form_Load()
    Text0.Format = "mm/dd/yyyy"
End Sub

[racquetballguy]

Thanks I didn't realize you could enter mm/dd/yyyy dirctly into the format section of the form.
Ivan

[willakawill]

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 :)

[Killer42]

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!

[missinglinq]

And get so engrossed in code that you forget at least one crucial social event per week!

[Killer42]

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... :)

[willakawill]

Um... "social event"? I'm sure I've heard that expression somewere... :)

Yup, it's next to the click event... er isn't it?

[NeoPa]

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.

[racquetballguy]

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?

Private Sub Report_Open(Cancel As Integer)

Dim AppointmentYear As String
Dim AppointmentMonth As String
Dim strGetDataFrom As String

Dim SeeIfMoreThanOneMonth As Integer

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

With cat
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb;"
End With

SeeIfMoreThanOneMonth = DateDiff("m", Forms!frmReminderDates![Start Date], Forms!frmReminderDates![End Date])

Select Case SeeIfMoreThanOneMonth

Case 0
AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![End Date]), 2)
AppointmentMonth = DatePart("m", Forms!frmReminderDates![End Date])

If AppointmentMonth < 10 Then
strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
Else
strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
End If

For Each tbl In cat.Tables

If tbl.Name = "tblRecall" Then
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
tbl.Properties("Jet OLEDB:Remote File Name") = strGetDataFrom
End If

Next

Case 1
AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![Start Date]), 2)
AppointmentMonth = DatePart("m", Forms!frmReminderDates![Start Date])

LastDayInMonth = DateAdd("m", 1, DateSerial(Val(AppointmentYear), Val(AppointmentMonth), 1)) - 1


If AppointmentMonth < 10 Then
strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
Else
strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
End If

Case Else
Debug.Print "Please re-eneter dates"

End Select


End Sub

Thanks
Ivan

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

[NeoPa]

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.

[racquetballguy]

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.

   For Each tbl In cat.Tables

       If tbl.Name = "tblRecall" Then
            tbl.Properties("Jet OLEDB:Create Link") = True
            tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
            tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
        End If

    Next

The more complete code:

Private Sub Report_Open(Cancel As Integer)

Dim AppointmentYear As String
Dim AppointmentMonth As String
Dim strGetDataFrom As String

Dim SeeIfMoreThanOneMonth As Integer

Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table

Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table

With cat
    .ActiveConnection = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb;"
End With

SeeIfMoreThanOneMonth = DateDiff("m", Forms!frmReminderDates![Start Date], Forms!frmReminderDates![End Date])

Select Case SeeIfMoreThanOneMonth

Case 0
    AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![End Date]), 2)
    AppointmentMonth = DatePart("m", Forms!frmReminderDates![End Date])

    If AppointmentMonth < 10 Then
        strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
    Else
        strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
    End If
    
    For Each tbl In cat.Tables

       If tbl.Name = "tblRecall" Then
            tbl.Properties("Jet OLEDB:Create Link") = True
            tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
            tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
        End If

    Next

Case 1
    AppointmentYear = Right(DatePart("yyyy", Forms!frmReminderDates![Start Date]), 2)
    AppointmentMonth = DatePart("m", Forms!frmReminderDates![Start Date])
    
    LastDayInMonth = DateAdd("m", 1, DateSerial(Val(AppointmentYear), Val(AppointmentMonth), 1)) - 1


    If AppointmentMonth < 10 Then
        strGetDataFrom = "01sk0" & AppointmentMonth & AppointmentYear & ".dat"
    Else
        strGetDataFrom = "01sk" & AppointmentMonth & AppointmentYear & ".dat"
    End If
     
Case Else
    Debug.Print "Please re-eneter dates"
    
End Select


End Sub

Thanks
Ivan

[NeoPa]

For Each tbl In cat.Tables

    If tbl.Name = "tblRecall" Then
        tbl.Properties("Jet OLEDB:Create Link") = True
        tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
        tbl.Properties("Jet OLEDB:Remote Table Name").Value = strGetDataFrom
    End If

Next [b]tbl[/b]

(Notice Next tbl)
would seem to be equivalent to :

Set tbl = cat.TableDefs("tblRecall")
tbl.Properties("Jet OLEDB:Create Link") = True
tbl.Properties("Jet OLEDB:Link Datasource") = "C:\Documents and Settings\Ivan\Desktop\Pernal MD PA\Access Programs\Reminder Notices SK Files.mdb"
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.)

[Killer42]

...(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.

[NeoPa]

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!)