-
September 6th, 2013, 05:57 AM
#1
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
-
September 6th, 2013, 10:06 AM
#2
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?
-
September 9th, 2013, 07:29 AM
#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 ...
-
September 9th, 2013, 08:06 AM
#4
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
-
September 9th, 2013, 12:03 PM
#5
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
-
September 9th, 2013, 12:15 PM
#6
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?
-
September 10th, 2013, 12:46 AM
#7
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.
-
September 11th, 2013, 03:55 AM
#8
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...
-
September 11th, 2013, 07:45 AM
#9
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|