dbase code to Access 1

[joell165]

Yeah, hate to admit it, but I'm still sometimes more comfortable in dBASE than Access.

I wonder if anyone can show me how the following dBASE code can be written in VBA or VB for changing the dates in a field (DATE)from 020603 to 06/03/02 in a new field(DATE1)

In dBASE it's:

replace all DATE1 with subs(DATE,3,2) + "/" + subs(DATE,5,2) + "/" + subs(DATE,1,2)

Also, if anyone knows of a book I can buy with dBASE to Access info, I'd appreciate finding out about it. I've been converting Access tables to dBASE doing the changes, and conveting them back to Access. I'd like to just work with VB and VBA with Access.

Thanks

 

[RickSpr]

The equivalent to the dBase code is the following query:
UPDATE <table name>
SET DATE1 = Mid$([DATE], 3, 2) & &quot;/&quot;
& Mid$([DATE], 5, 2) & &quot;/&quot;
& Mid$([DATE], 1, 2)

If you create a new query, switch it to SQL view, and copy the above SQL code into it, it should become an Update Query that does what you want. However, &quot;DATE&quot; is the name of a VBA built-in function, so there's some conflict in the naming. Using a different field name would be a good idea. Nevertheless, I believe it should work correctly even when named DATE.

Sorry, I can't help you with the book. Rick Sprague

 

[joell165]

Thanks Rick. The code works perfectly. I hate to ask again, but there is one more piece I need.

The names are entered in most downloads I get in the format

LAST FIRST (space no comma). To reverse the order in dBASE I do the following:

I create two fields,lname and fname. Then I use the following code:

replace all lname with left(name,at (&quot; &quot;,name))
replace all fname with stuff (name,1,at(&quot; &quot;,name),&quot;&quot


Could you translate that into VBA?

Thanks again.