date format problem, can not get dd/mm/yy in vb code,

[noorani]

I have create a function, in my module. and i'm Calling this function in a report by using a macro, in the menu which i have designed for my report.

now what this code is doing. when this code runs it change one of the field value to true in the table name as "order".

and then it inserts the current date and time in seprate fields, but my problem is that. ,

my code is not inserting the date in the proper format, i want the format like dd/mm/yy , but i'm getting mm/dd/yy ,

the table fields which i'm accessing by this code are as follows, Date field is set to medium date format,
time field is set to short time format,

here is the code


Function zayavactivadmin()
Dim dbs As Database
Dim O As Recordset
Dim title As String
Dim Mydate
Dim MyTime
Mydate = #1/1/1980#
Mydate = date$
MyTime = #4:35:17 PM# ' Assign a time.
MyTime = Time
title = "Backoffice"
Set dbs = CurrentDb
Set O = dbs.OpenRecordset("select * from Orders where OrderID=" & Forms!OrdersAdmin!OrderID, dbOpenDynaset)
If O!zayavflag = True Then
MsgBox ("You already have send this order&quot, ,title
Exit Function
Else
O.edit
O!zayavflag = True
O!zayavDate = Mydate
O!zayavtime = MyTime
O.Update
O.Close
dbs.Close
End If
End Function


Please tell me where i'm wrong. thanks in advance.

 

[scriverb]

Date field values are all stored the same way. The difference in how they are displayed has to do with the format property of the Date/Time field and the Format property of a control where this field is being displayed.

You can modify the field Format property to dd/mm/yy to begin with. This way as you make new controls on forms and reports the format will automatically follow to that item. You can display the date without this modification if you make reference to it in a Report or Form control or VBA code by this syntax:
Format([DateControlName], "dd/mm/yy&quotor by modifying the Format property of that control to the "dd/mm/yy" setting.

You could also modify your PC's Regional And Language options for the Date field. This can be done through the control panel. This is only a fix for your personal PC so when you distribute your Database you would have to make that adjustment to all PC's using it.

I hope this helps you understand the formating process. Bob Scriver

 

[tonyireland]

I find this code quite useful to test for the Windows Date Format and change it if neccessary

Public Const LOCALE_SSHORTDATE As Long = &H1F
Public Const LOCALE_USER_DEFAULT As Long = &H400
' Used for setting the date format to dd/MM/yyyy, if required
Public Declare Function GetLocaleInfo Lib "Kernel32" _
    Alias "GetLocaleInfoA" (ByVal lLocale As Long, _
        ByVal lLocaleType As Long, ByVal sLCData As String, _
        ByVal lBufferLength As Long) As Long
Public Declare Function SetLocaleInfo Lib "Kernel32" _
    Alias "SetLocaleInfoA" (ByVal Locale As Long, _
        ByVal LCType As Long, ByVal lpLCData As String) As Long
Sub Test_Date_Format()

    Dim shortDateFormat As String
    Dim lBuffSize As String
    Dim sBuffer As String
    Dim lRetGet As Long
    Dim lRetSet As Long
    
    lBuffSize = 256
    sBuffer = String$(lBuffSize, vbNullChar)
    lRetGet = GetLocaleInfo(LOCALE_USER_DEFAULT, LOCALE_SSHORTDATE, sBuffer, lBuffSize)
  
    If lRetGet > 0 Then
        shortDateFormat = Left$(sBuffer, lRetGet - 1)
        'this is the existing format of machine
    End If
    'to change the format if it doesn't match our format
    'MM should be used in capitals for monyhs,small m are for minutes
    If LCase(shortDateFormat) <> &quot;dd/mm/yyyy&quot; Then
        lRetSet = SetLocaleInfo(LOCALE_USER_DEFAULT, _
                LOCALE_SSHORTDATE, &quot;dd/MM/yyyy&quot;)
        If lRetSet <= 0 Then
             MsgBox &quot;Could not change date format &quot;, vbCritical, STRSYSTEM
        Else
            MsgBox &quot;Your date format was invalid for this application&quot; & vbCrLf & _
            &quot;it was &quot; & shortDateFormat & &quot; it has been set to dd/MM/yyyy&quot; & vbCrLf _
            & &quot;you will need to restart the application to apply it&quot;, vbInformation, _
            STRSYSTEM
            Quit
        End If
    End If
End Sub

Run this at startup