[AccessD] More Date issues

Hi all,

I am having more date issues in access. I have searched the archives and 
tried the formatting shown there. I have redone the code several times and 
implemented msgbox popups but to no avail. I now have less hair and am 
considering a return to the chain smoking days of my youth... or putting a 
hit out on Microsoft for the whole America is the world date thing. (I am 
married to an American so I get to whine about it a lot). 

this is the scenario.

It is a simple little database written a few years back and now passed on 
to me because it has problems. (I get all the good jobs)

It has a form for data entry, including a date capture, which works fine, 
as follows:

Private Sub FinalDateCommand_Click()
'this allows user to enter final date in all displayed records by command 
button

On Error GoTo Err_FinalDateCommand_Click
Dim mydb As Database, myset As Recordset
Dim I As Integer
Dim n As Integer
Dim Finished As Date
Dim TSID As Integer
Dim strSQL As String

Set mydb = CurrentDb
Set myset = mydb.OpenRecordset("WorkSheet")

TSID = Forms![worksheet]![TSID]
Finished = Format(Forms![worksheet]![FinalDate], "mm\/dd\/yyyy")

    ' Create SQL string.
    strSQL = "UPDATE worksheet SET finished = #" & Finished & "# WHERE 
tsid = " & TSID
    ' Run action query.
    mydb.Execute strSQL

'refresh the records
    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
 
Exit_FinalDateCommand_Click:
    Exit Sub

Err_FinalDateCommand_Click:
    MsgBox Err.Description
    Resume Exit_FinalDateCommand_Click
 
End Sub

This displays the date in the required format (ie: dd/mm/yy) as per the 
formatting of the box. 
from here however it is stored in a sub form which is displaying the date 
totally reversed (ie: yy/mm/dd).

Private Sub Form_BeforeUpdate(Cancel As Integer)

'store the finished date that you have just entered
finisheddate = Me![Finished]
End Sub

The formatting on the fields in the form is set to shortdate and my PC has 
regional settings for Australia. 

I am really stuck (again) ... I hate Date issues in Access!


Connie Kamrowski

Analyst/Programmer
Information Technology
NSW Agriculture
Orange

Ph: 02 6391 3250
Fax:02 6391 3290


This message is intended for the addressee named and may contain 
confidential information. If you are not the intended recipient or 
received it in error, please delete the message and notify sender. Views 
expressed are those of the individual sender and are not necessarily the 
views of their organisation.
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20031015/dbefbd2b/attachment.html>