Changing DATE Format HELP

[cephal0n]

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.

Public Sub ConvertDate()

[i]Dim cnn1 As ADODB.Connection[/i]
[i]Dim rst1 As ADODB.Recordset[/i]
[i]Dim sql As String[/i]
[i]Dim mm As Variant[/i]
[i]Dim dd As Variant[/i]
[i]Dim yy As Variant[/i]

[i]Set cnn1 = New ADODB.Connection[/i]

[i]cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _[/i]
[i]"Data Source=" & CurrentProject.Path & _[/i]
[i]"\nope.mdb"[/i]



[i]Set rst1 = New ADODB.Recordset[/i]
[i]rst1.Open "Sheet1", cnn1, adOpenDynamic, adLockPessimistic, adCmdTable[/i]
[i]sql = "SELECT * FROM Sheet1" & Format(rst1.Fields("MyDate").Value, "mm/dd/yy")[/i]

[i]cnn1.Execute sql[/i]
[i]Debug.Print rst1.Fields("Mydate").Value[/i]
[i]End Sub[/i]

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!

[Stewart Ross]

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:

newdate = replace(SAPstring, ".", "/")

then convert this to a date - an actual date, not a string that is formatted to look like a date:

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)

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.

-Stewart

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.

[Stewart Ross]

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.

-Stewart

[cephal0n]

Hi Stewart!

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

Public Sub ConvertDate()
Dim cnn1 As ADODB.Connection 
Dim rst1 As ADODB.Recordset
Dim sql As String
Dim newdate As Date
Dim SAPstring As String

'take the current date string from SAP and replace the '.' separator
'with the recognise '/' separators:
'newdate = replace(SAPstring, ".", "/")
Set cnn1 = New ADODB.Connection

cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & CurrentProject.Path & _
            "\nope.mdb"

Set rst1 = New ADODB.Recordset
rst1.Open "Sheet1", cnn1, adOpenDynamic, adLockPessimistic, adCmdTable

'then convertthis date - an actual date, not a string that is formatted to look like a date:
newdate = CDate(Replace(SAPstring, ".", "/"))

'and finally add this as calculated field to correct SQL statement
sql = "SELECT*, CDate(replace(rst1.Fields("MyDate"), ".", "/")) AS newdate FROM Sheet1"


cnn1.Execute sql
Debug.Print rst1.Fields(1).Value

End Sub

But when I tried running the code I get the compile error: Syntax error
on the sql part:
sql = "SELECT*, CDate(replace(r st1.Fields("MyD ate"), ".", "/")) 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 :)

[Stewart Ross]

Hi Cephalon. There should have been a space between the SELECT and the asterisk in your code, but you missed it out:

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.

-Stewart

[Stewart Ross]

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.
-Stewart

[cephal0n]

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.

Dim cnn1 As ADODB.Connection
Dim rst As ADODB.Recordset
Dim SQl As String
Dim newdate As Date
Dim SAPstring As String


Set cnn1 = New ADODB.Connection

    With cnn1
        .Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=" & CurrentProject.Path & "\nope2.mdb"
            
    End With
'MsgBox "Provider:" & cnn1.Provider

Set rst = New ADODB.Recordset
    With rst
        .Open "Table1", cnn1, adOpenDynamic, adLockOptimistic, adCmdTableDirect
    End With
'take the cuurent date string from SAP and replace the '.' separator
'with the recognise '/' separators.
'newdate = replace(SAPstring,".","/")

'then convert this date - an actual date, not a string is formatted to look like a date.
'newdate = CDate(Replace(SAP, ".", "/"))

'and finally add this as calculated field to correct sql statement
SQL = "SELECT *, CDate(replace(rst1.Fields("MyDate"), ".", "/")) AS newdate FROM Table1"



cnn1.Execute SQl
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.

[Stewart Ross]

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("My Date"). 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.

-Stewart

[NeoPa]

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 ;)

[rajanji]

Hi,

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


Thanks and Regards,

Rajan Arora

[youmike]

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

[Stewart Ross]

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...

-Stewart

[Stewart Ross]

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....

MODERATOR

[cephal0n]

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!

[NeoPa]

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.