Date Format for the VBE

[chergh]

Hey folks,

Bit of an embarassing this one. How do I change the way the VBE displays dates? currently it's mm/dd/yy and I'm wanting dd/mm/yy.

 

[mazza]

FORMAT ([fieldname],"DD/MM/YY")

 

[chergh]

Thats not what I am meaning.

I am writing code and am including some dates in the code itself. When I type in the date it changes the way it is displayed e.g.

Call assign_status(tbl_name, "default_e57xx_db ", #20/04/2006#, "3.9")

The VBE then changes this to display as:

Call assign_status(tbl_name, "default_e57xx_db ", #4/20/2006#, "3.9")

I want the date to remain the same as I enter it. All the regional setting in the control panel are correctly set as well.

 

[Dennisk]

Replace #20/04/2006#, with FORMAT (#20/04/2006#,"DD/MM/YY")

 

[mazza]

A useful function might be the following


Function getSQLDate(datDate As Date, Optional strFormat As String) As String 'breaks down date to insert into a SQL string Dim strDateDD As String, strDateMM As String, strDateYYYY As String
Dim strDateDD As String
Dim strDateMM As String
Dim strDateYYYY As String

strDateDD = Format(datDate, "dd")
strDateMM = Format(datDate, "mm")
strDateYYYY = Format(datDate, "yyyy")

Select Case strFormat
Case "mdy"
getSQLDate = strDateMM & "/" & strDateDD & "/" & strDateYYYY
Case "dmy"
getSQLDate = strDateDD & "/" & strDateMM & "/" & strDateYYYY
Case Else
getSQLDate = strDateMM & "/" & strDateDD & "/" & strDateYYYY
End Select

End Function



use the getSQLdate for example as follows
dim usedate as date

Usedate = #20/04/2006#

Call assign_status(tbl_name, "default_e57xx_db ", getSQLDate(Usedate, "mdy") , "3.9")

 

[chergh]

That would be overkill really but thanks for the suggestion. i was just hoping there was an option in a menu somewhere, guess I'm stuck with using FORMAT.

Thx for the help.

 

[boblarson]

Normally this would be a system regional setting based on your Windows system. But, you might try turning off Autocorrect.

 

[chergh]

Where's the option to turn off auto correct about?

 

[boblarson]

In the main Window (not the code IDE) select Tools > Options > General and you should see it.

 

[chergh]

Doesn't seem to make any difference

 

[RoyVidar]

Would it be impolite to ask why you think you need a specific format in VBE?

If you need to hardcode dates, I would recommend the DateSerial function, which, as long as you pass the correct values, will work regardless of locale.

I think the VBE date and number format is US regardless of locale (same as Jet).

 

[chergh]

The only reason I need a specific date format is for my own benefit in that I use dd/mm/yy format everywhere else so it's more convienient if I could use it in the VBE as well.

 

[RoyVidar]

Don't you pass dates to dynamic SQL strings?

If you do, then you should be aware that dd/mm/yyyy gives funny results, so I doubt you're using dd/mm/yyyy everywhere else

 

[chergh]

looking through my past projects it would appear I haven't done that. I have plenty of dynamic sql strings but I've never had the need to pass full dates, only partial dates like mmm-yyyy.

I'm sure I will at some point so you have probably save me a world of pain for when I do.

 

[RoyVidar]

<grin>

When you get there, here's a link with some explaining http://allenbrowne.com/ser-36.html