By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,587 Members | 1,076 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,587 IT Pros & Developers. It's quick & easy.

Changing DATE Format HELP

P: 38
I have a technical Date problem that's really difficult for me, I have a "custom made" Date format MM.DD.YY this is actually extracted from SAP and theirs no other format option offered such as ShortDate, LongDate etc. so now I making a sql query that must convert SAP date to a date Format that is recognizable to MS Access and I choose the ShortDate MM/DD/YY using ADO. I read about the Format() command using ADO online and experiment on it and hoping I get something right.

Expand|Select|Wrap|Line Numbers
  1. Public Sub ConvertDate()
Expand|Select|Wrap|Line Numbers
  1. Dim cnn1 As ADODB.Connection
  2. Dim rst1 As ADODB.Recordset
  3. Dim sql As String
  4. Dim mm As Variant
  5. Dim dd As Variant
  6. Dim yy As Variant
  8. Set cnn1 = New ADODB.Connection
  10. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  11. "Data Source=" & CurrentProject.Path & _
  12. "\nope.mdb"
  16. Set rst1 = New ADODB.Recordset
  17. rst1.Open "Sheet1", cnn1, adOpenDynamic, adLockPessimistic, adCmdTable
  18. sql = "SELECT * FROM Sheet1" & Format(rst1.Fields("MyDate").Value, "mm/dd/yy")
  20. cnn1.Execute sql
  21. Debug.Print rst1.Fields("Mydate").Value
  22. End Sub

This one's causes a big error that's beyond my understanding and I was wondering if anyone could help me with it and a little explanation would be nice. It is very important that I solve this, so…pls…pls help ME!

Thanks in advance!
Jun 3 '08 #1
Share this Question
Share on Google+
15 Replies

Expert Mod 2.5K+
P: 2,545
Hi Cephalon. To avoid getting into such a tangle you do need to be more systematic about what you are trying to do. Firstly, you need something that Access will recognise as a date. To do so, take the current date string from SAP and replace the '.' separators with the recognised '/' separators:
Expand|Select|Wrap|Line Numbers
  1. newdate = replace(SAPstring, ".", "/")
then convert this to a date - an actual date, not a string that is formatted to look like a date:
Expand|Select|Wrap|Line Numbers
  1. newdate = CDate(replace(SAPstring, ".", "/"))
and finally add this as a calculated field to a correct SQL statement (not an incorrect one as you show in your code)
Expand|Select|Wrap|Line Numbers
  1. sql = "SELECT *, CDate(replace(rst1.Fields("My Date"), ".", "/")) AS newdate FROM Sheet1" 

With newdate as a date and not a string you can use it in subsequent calculations, group values using the date functions to extract months or whatever, and format it in whatever way is best according to requirements.

Format is a very powerful and flexible function, but you were really starting with a string that looked something like a date, not a date as such. Worry about formatting when you have an actual date to work on.


ps It would really be appreciated if you would use code tags to delineate your code - it makes it so much easier to read. I have added them for you in this case.
Jun 3 '08 #2

Expert Mod 2.5K+
P: 2,545
By the way, although I corrected the syntax of your SQL statement, as it stands it is not going to do anything for you - nothing useful at all. What do you expect to happen when you execute the SQL statement? You are creating a view of the data which is not subsequently being used. It does not persist outside of the routine in which you have placed it.

It seems to me that you do not need to use code at all. Just use an Access query on Sheet1 with the calculated field added to provide the new date.

Jun 3 '08 #3

P: 38
Hi Stewart!

first of, thank you for the help. I copied your code as instructed and made some changes.

Expand|Select|Wrap|Line Numbers
  1. Public Sub ConvertDate()
  2. Dim cnn1 As ADODB.Connection 
  3. Dim rst1 As ADODB.Recordset
  4. Dim sql As String
  5. Dim newdate As Date
  6. Dim SAPstring As String
  8. 'take the current date string from SAP and replace the '.' separator
  9. 'with the recognise '/' separators:
  10. 'newdate = replace(SAPstring, ".", "/")
  11. Set cnn1 = New ADODB.Connection
  13. cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  14.             "Data Source=" & CurrentProject.Path & _
  15.             "\nope.mdb"
  17. Set rst1 = New ADODB.Recordset
  18. rst1.Open "Sheet1", cnn1, adOpenDynamic, adLockPessimistic, adCmdTable
  20. 'then convertthis date - an actual date, not a string that is formatted to look like a date:
  21. newdate = CDate(Replace(SAPstring, ".", "/"))
  23. 'and finally add this as calculated field to correct SQL statement
  24. sql = "SELECT*, CDate(replace(rst1.Fields("MyDate"), ".", "/")) AS newdate FROM Sheet1"
  27. cnn1.Execute sql
  28. Debug.Print rst1.Fields(1).Value
  30. End Sub
But when I tried running the code I get the compile error: Syntax error
on the sql part:
sql = "SELECT*, CDate(replace(rst1.Fields("MyDate"), ".", "/")) AS newdate FROM Sheet1"
did I missed somthing? pls. help

I apologize for my mistake on not putting code tags, I hope I made it right this time. About your question
What do you expect to happen when you execute the SQL statement?

this changing part is the first step, in the future when I get this right I'am hoping to populate the MyDate field with morethan 500 data and get their MAXDATE.
but for now I'm solving problem one at a time.

Thanks in advanced :)
Jun 4 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi Cephalon. There should have been a space between the SELECT and the asterisk in your code, but you missed it out:

Expand|Select|Wrap|Line Numbers
  1. SQL = "SELECT *, CDate(replace(rst1.Fields("MyDate"), ".", "/")) AS newdate FROM Sheet1"
I would also comment that I personally would not use a code solution as a first choice for what you want to achieve. Queries built in the Access editor or in SQL can do what you want in terms of finding max dates and so on without any form of coding. Code solutions are best when there are multiple sequential actions involved.

I use recordset-based code solutions when there is no practicable way to achieve something in SQL. Code solutions are very flexible, of course, but in the longer term other people have to maintain them - and SQL/Access queries are much more visible and maintainable than trying to find how something was solved in code.

It is interesting when reading posts on this site to find how many people have 'inherited' code written by others which they do not understand and are unwilling to rewrite or modify as a result. I can't say the same for SQL solutions, though.

Jun 4 '08 #5

Expert Mod 2.5K+
P: 2,545
In addition to my previous post please comment out line 21 of your code in your last reply (post 4) - the one that begins newdate = .... You have included the line in your code but it was actually intended as an example to show you each of the steps involved in reaching a workable solution.

The only line you really need to include is the one for the SQL string, not the intermediate steps that got us there.
Jun 4 '08 #6

P: 38
Thanks Stewart!

I tried what you said about putting space between SELECT and the * and I encountered a new compile error: Expected: end of statement and when I debug the code it keeps pointing on the ("MyDate") field.
Expand|Select|Wrap|Line Numbers
  1. Dim cnn1 As ADODB.Connection
  2. Dim rst As ADODB.Recordset
  3. Dim SQl As String
  4. Dim newdate As Date
  5. Dim SAPstring As String
  8. Set cnn1 = New ADODB.Connection
  10.     With cnn1
  11.         .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  12.             "Data Source=" & CurrentProject.Path & "\nope2.mdb"
  14.     End With
  15. 'MsgBox "Provider:" & cnn1.Provider
  17. Set rst = New ADODB.Recordset
  18.     With rst
  19.         .Open "Table1", cnn1, adOpenDynamic, adLockOptimistic, adCmdTableDirect
  20.     End With
  21. 'take the cuurent date string from SAP and replace the '.' separator
  22. 'with the recognise '/' separators.
  23. 'newdate = replace(SAPstring,".","/")
  25. 'then convert this date - an actual date, not a string is formatted to look like a date.
  26. 'newdate = CDate(Replace(SAP, ".", "/"))
  28. 'and finally add this as calculated field to correct sql statement
  29. SQL = "SELECT *, CDate(replace(rst1.Fields("MyDate"), ".", "/")) AS newdate FROM Table1"
  33. cnn1.Execute SQl
  34. Debug.Print "Result: " & rst.Fields("MyDate")
I feel so dumb now I tried looking for substitution like changing ("Mydate") to (1) but I got bigger errors, knowing its a big bad idea, I would like to ask if you have any idea why my compiler is sending me the error.

thank you for your patience.
Jun 5 '08 #7

Expert Mod 2.5K+
P: 2,545
Hi Cephalon. In your original post, post 1, you defined an ADO recordset called rst1 and referred to a field within its fields collection called "MyDate" - rst1.fields("MyDate"). However, in the current version of your code you are defining a slightly differently named recordset variable, rst,not rst1. This inconsistency will cause a compile error if (as you should) you have Option Explicit on, which will make sure that all variables are defined before use.

Even if you are not using Option Explicit - and you really should! - the attempt to qualify the undefined variable rst1 with the .fields property reference will cause a compile error as that property will not be recognised for a variant type which is not defined as a recordset.

(I am assuming that the name of the field is indeed "MyDate" as in your original post, as I have no way of knowing the real name of your field if it isn't.)

You may see from experiencing these errors why I have advised you that an Access query-based SQL solution is in so many ways a simpler and better approach in the circumstances. However, fix the current error and see where this gets you.

I would be interested to know what it is you are coding after this first step is completed - as I think it should be possible to come up with an SQL alternative that would be equivalent without using the code approach at all.

Jun 5 '08 #8

Expert Mod 15k+
P: 31,485
Cephal0n, I'm pleased to see you taking Stewart's advice on board (particularly about the CODE tags). His advice is usually sound, and the point he makes about considering a solution in SQL rather than a more complicated and hard to maintain one in VBA is particularly so.

It's never easy to take on fundamental changes when you're a bit lost, but if you make sure you keep a good backup then it can be tried without risk. You may even learn some fundamental concepts that will help you in your future database work ;)
Jun 6 '08 #9

P: 31

Plz send the code to change the Date Format in VB.Net....

Thanks and Regards,

Rajan Arora
Jun 7 '08 #10

P: 69
tried what you said about putting space between SELECT and the * and I encountered a new compile error: Expected: end of statement and when I debug the code it keeps pointing on the ("MyDate") field.
There's a semicolon missing at the end of the SQL Statement
Jun 8 '08 #11

Expert Mod 2.5K+
P: 2,545
Hi youmike. The presence or absence of a semicolon at the end will not stop the SQL statement from running, but thanks for checking the statement too...

Jun 9 '08 #12

Expert Mod 2.5K+
P: 2,545
Hi Rajan. Hijacking this thread to ask a different question is not appropriate, and in any event it is very unclear what you require. I cannot answer your post at present.

If you use the SEARCH facilities you should be able to find other posts which may answer your query within the VB forum.
Plz send the code to change the Date Format in VB.Net....
Jun 9 '08 #13

P: 38
Hi guys! I apologize for the delayed reply. Anyway I'm still working on my database.

Hi Stewart thank you for the logical opinion on how I should work on my coding
and when to use a more effective and useful solution. to be honest I really don't
know what came over me and why I keep pursuing coding it in VBA instead of using
simple sql. anyway I decide to use the REPLACE and FORMAT in my queries to get the Max
Date quicker and faster. I have a last question, is it possible to hide a query from
the Objects > Query, to avoid any changes made on my query design and for security purposes.

Although I made a decision on the using sql queries instead of vba, I will not give up
on the code you provided, I'm still working on the errors and hope I get it right soon.

thanks for the Big Help......All!
Jun 10 '08 #14

Expert Mod 15k+
P: 31,485
If you Right-Click on the database object (Query; Table; Form; whatever) and select Properties, then there is a CheckBox to set the Hidden attribute.
Jun 10 '08 #15

P: 38
Thanks NeoPa!

I tried what you said and it works!
Jun 11 '08 #16

Post your reply

Sign in to post your reply or Sign up for a free account.