Table insert append delete module

  • Thread starter Thread starter Mike T
  • Start date Start date
M

Mike T

I have a situation where I use a number of queries to
complete a task and I know there is a better faster way of
doing it and I'm hoping someone can help. I have a table
the is imported in where the date field is in the yyyymmdd
format and is set as a number. what I need to do is
convert it to mm/dd/yyyy. Now I do this by using this
code Mid([DATE_COMPLETE_JBMSTR],5,2) & "/" & Mid
([DATE_COMPLETE_JBMSTR],7,2) & "/" & Left
([DATE_COMPLETE_JBMSTR],4)

What I'm looking to do is create a module that either
converts it for me by changing the table format to text
then updating it with this type of code or creading a
module with would incorporate the queries that have now
that do the job. I'm very new to modules and have a basic
understanding of VBA. If you could point me in the right
direction or have some code that will do what I have
mention above that would be great. Code that shows me how
to create a module that will append data to a nother table
then delete data from a table that would help too.

TIA
Mike
 
Mike

Try this (watch out for line wrapping)

Function MyFunction(

Dim dbs As DAO.Databas
Dim tdf As DAO.TableDe
Dim strTableName As Strin
Dim strNewFieldName As Strin
Dim strSQL As Strin

strTableName = "MyTable
strNewFieldName = "MyField

strSQL = "UPDATE " & strTableName & " SET " & strTableName & "." & strNewFieldName & " = Mid([DATE_COMPLETE_JBMSTR],5,2) & ""/"" & Mid([DATE_COMPLETE_JBMSTR],7,2) & ""/"" & Left([DATE_COMPLETE_JBMSTR],4);

Set dbs = CurrentDb(
Set tdf = dbs.TableDefs(strTableName

tdf.Fields.Append .CreateField(strNewFieldName, dbDate

dbs.Execute strSQ

tdf.Fields.Delete "DATE_COMPLETE_JBMSTR

Set tdf = Nothin
Set dbs = Nothin

End Functio

Jak


----- Mike T wrote: ----

I have a situation where I use a number of queries to
complete a task and I know there is a better faster way of
doing it and I'm hoping someone can help. I have a table
the is imported in where the date field is in the yyyymmdd
format and is set as a number. what I need to do is
convert it to mm/dd/yyyy. Now I do this by using this
code Mid([DATE_COMPLETE_JBMSTR],5,2) & "/" & Mi
([DATE_COMPLETE_JBMSTR],7,2) & "/" & Lef
([DATE_COMPLETE_JBMSTR],4

What I'm looking to do is create a module that either
converts it for me by changing the table format to text
then updating it with this type of code or creading a
module with would incorporate the queries that have now
that do the job. I'm very new to modules and have a basic
understanding of VBA. If you could point me in the right
direction or have some code that will do what I have
mention above that would be great. Code that shows me how
to create a module that will append data to a nother table
then delete data from a table that would help too

TI
Mik
 
Mike...try this instead. I changed

tdf.Fields.Append .CreateField(strNewFieldName, dbDate

to

With td
.Fields.Append .CreateField(strNewFieldName, dbDate
End Wit

Function MyFunction(

Dim dbs As DAO.Databas
Dim tdf As DAO.TableDe
Dim strTableName As Strin
Dim strNewFieldName As Strin
Dim strSQL As Strin

strTableName = "MyTable
strNewFieldName = "MyField

strSQL = "UPDATE " & strTableName & " SET " & strTableName & "." & strNewFieldName & " = Mid([DATE_COMPLETE_JBMSTR],5,2) & ""/"" & Mid([DATE_COMPLETE_JBMSTR],7,2) & ""/"" & Left([DATE_COMPLETE_JBMSTR],4);

Set dbs = CurrentDb(
Set tdf = dbs.TableDefs(strTableName

With td
.Fields.Append .CreateField(strNewFieldName, dbDate
End Wit

dbs.Execute strSQ

tdf.Fields.Delete "DATE_COMPLETE_JBMSTR

Set tdf = Nothin
Set dbs = Nothin

End Functio

Jake
 
Back
Top