CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2013
    Posts
    3

    Smile Date Format automatically changing in VB coding window. How to stop it?

    Hello all,

    I am writing some vb codes via Microsoft access 2010 to perform some sql queries to Access tables.

    Something strange is happening.

    Below is my code

    Dim tDate

    tDate = #28 June 2013#

    once I hit enter, tDate will change to tDate =#06/28/2013#

    Where can I make settings not to change the format?

    Thanks a lot!
    M

  2. #2
    Join Date
    Dec 2001
    Posts
    6,332

    Re: Date Format automatically changing in VB coding window. How to stop it?

    Suppose you make it a string rather than a date? If tDate must be a date data type, then why not initialize it with a date before setting it with date formatted strings? In other words, use " instead of #, such as "28 June 2013". I've not coded in Access, but if variables can be defined as other types besides variants, then you won't need to initialize your variable with a date.
    Please remember to rate the posts and threads that you find useful.
    How can something be both new and improved at the same time?

  3. #3
    Join Date
    Sep 2013
    Posts
    3

    Re: Date Format automatically changing in VB coding window. How to stop it?

    Hi WizBang,

    Yes, if it is with "" it takes it as it is but I can't format the date into other formats that I wanted.. Even if i declare tDate as date..

    Eg: tDate ="28/06/2013"
    then tDate = format(tDate, "mmddyyyy")

    the result of tDate is the same.

    Thank you for your reply ...

  4. #4
    Join Date
    Jul 2001
    Location
    Sunny South Africa
    Posts
    11,283

    Re: Date Format automatically changing in VB coding window. How to stop it?

    This is quite common with MS Access ( all versions ) and has always sucked, probably always will. Access obtains the date formatting from your computer's regional settings. Change that, then your problem should also have been sorted out. But, remember as well that any new info that gets stored in access might get mixed up somehow.

    What you can also do in Access is to format the date column exactly as you want it, just go to the particular field's properties and set the format there.

    I hope it helps

  5. #5
    Join Date
    Jan 2006
    Location
    Fox Lake, IL
    Posts
    15,007

    Re: Date Format automatically changing in VB coding window. How to stop it?

    Here's an old sample that I did NOT write:

    Code:
    Option Explicit
    
    Private Sub Form_Load()
    Dim dteDate     As Date
    Dim adoRec      As ADODB.Recordset
        dteDate = CDate("7 March 2005 10:04:00AM")
        Set adoRec = GetRecordset(dteDate)
        With adoRec
            Do While Not .EOF
                'your code goes here
                 MsgBox .Fields(0)
                .MoveNext
            Loop
            .Close
        End With
        Set adoRec = Nothing
    End Sub
    
    Private Function GetRecordset(ByVal pdteSearchDate As Date) As ADODB.Recordset
    Dim strSQL      As String
    Dim adoRec      As ADODB.Recordset
        strSQL = "SELECT * "
        strSQL = strSQL & "FROM AS_Createsched "
        strSQL = strSQL & "WHERE AS_Createsched.CSIN_dates = #" & Format$(pdteSearchDate, "d mmm yyyy") & "# "
        strSQL = strSQL & "AND HOUR(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "hh") & " "
        strSQL = strSQL & "AND MINUTE(AS_Createsched.CSIN_times) = " & Format$(pdteSearchDate, "nn") & " "
        Set adoRec = New ADODB.Recordset
        adoRec.Open strSQL, GetConnString, adOpenForwardOnly, adLockReadOnly
        Set GetRecordset = adoRec
        Set adoRec = Nothing
    End Function
    
    Private Function GetConnString() As String
        GetConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\db4.mdb;Persist Security Info=False"
    End Function
    David

    CodeGuru Article: Bound Controls are Evil-VB6
    2013 Samples: MS CODE Samples

    CodeGuru Reviewer
    2006 Dell CSP
    2006, 2007 & 2008 MVP Visual Basic
    If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!

  6. #6
    Join Date
    Dec 2001
    Posts
    6,332

    Re: Date Format automatically changing in VB coding window. How to stop it?

    Can you not use a string data type rather than a date type? The Format function works just as well if the variable is a string. Won't the SQL queries work if the date is a string? If not, then I suppose one possible workaround might be to change the database field to a string, though that may present its own set of issues.
    Please remember to rate the posts and threads that you find useful.
    How can something be both new and improved at the same time?

  7. #7
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Date Format automatically changing in VB coding window. How to stop it?

    tDate = #28 June 2013#

    once I hit enter, tDate will change to tDate =#06/28/2013#
    Dates are not stored as words so if you want a word to be there then you either store it as a string or use format to make it appear in string form. Don't worry about the way the IDE shows it you can still display it as you wish to the user.
    Always use [code][/code] tags when posting code.

  8. #8
    Join Date
    Sep 2013
    Posts
    3

    Re: Date Format automatically changing in VB coding window. How to stop it?

    Thank you all and sorry for being late to reply.

    I can't use string as I need it to reformat the date. I tried using tDate data type Variant and I can reformat the date.

    I am not trying to use as I had posted in the first thread (tDate = #28 June 2013#) anymore.

    What I did is:

    I declare tDate as variant, change the tDate format to (yyyy/mm/dd) and then pass straight into the query string. That works.

    Access is taking the date format from local setting. However, the way it keeps changing when I use between # # can't be fixed.

    Thanks all for helping me out here...

  9. #9
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: Date Format automatically changing in VB coding window. How to stop it?

    When you define a variant and use the format function you are using a string.

    When you use the ## signs you are using a date which is never stored using the words as you tried in your first post but again that does not matter because a date can be displayed many different ways and it does not matter how the date looks in your code so long as it is valid.
    Always use [code][/code] tags when posting code.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured