Please help with my code - adding slashes in yymmdd

[tincan56]

I am trying to format a column from "test" to "TempTable" table.
In test, the data is yymmdd. I need to format this data
into yy/mm/dd (including slashes) and copy to the new TempTable. I have a command button which you would be able to populate when you click on it. However I am getting a "mismatch type" error.
What is wrong with the code? How can I rectify this issue?
I tried and tried but couldn't re-solve it, so I need some help...

Both tables have all "text" fields.

Thanks so much!
************************************************
Public Sub Command0_Click()

Dim db As Database
Dim rec As Recordset
Dim txtAccountNumber, txtCurrency, curAmount, dtDate, tempdtDate, strSQL As String

Set db = CurrentDb()
Set rec = db.OpenRecordset("test&quot

DoCmd.SetWarnings False

rec.MoveFirst
While Not (rec.EOF)

curAmount = rec("Field1&quot
txtAccountNumber = rec("Field2&quot
dtDate = rec("Field3&quot
txtCurrency = rec("Field4&quot

'Format dtDate with slashes yy/dd/mm
tempdtDate = Left(dtDate, 2) & "/" & Mid(dtDate, 3, 2) & "/" & Right(dtDate, 2)
Debug.Print tempdtDate

strSQL = "INSERT INTO TempTable([Field1],[Field2],[Field3]" & _
"[Field4])" & _
"Values(" & _
"'" & curAmount & "'," & _
"'" & txtAccountNumber & "'," & _
"'" & tempdtDate & "'," & _
"'" & txtCurrency & "'" & _
&quot "
rec.MoveNext

Wend

rec.Close
DoCmd.RunSQL (strSQL)
End Sub

 

[mwolf00]

"#" & tempdtDate& "#," & Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[mwolf00]

Oopss! "all 'text' fields" - Sorry.

Does the debug.print work? Comment out lines of code until you can see where the error is happening. Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[tincan56]

I am getting a "type mismatch" error 13 after
"Set rec = db.OpenRecordset("test&quot" code.
The rec = nothing, when i hit the debug button.

Does this have to do with my table? however, both my table are all text values though.

Thanks for the help!

 

[mwolf00]

Is "test" the name of your table? I haven't used these methods before (I'm more of an asp guy - Sorry). Should that statement load the whole table into the recordset? Get the Best Answers! faq333-2924
"A witty saying proves nothing." - Voltaire
mikewolf@tst-us.com

 

[aristos]

Try this one...

Function ReverseDateAsString (dtDate as Date) as String
dim tempdtDate as String
tempdtDate = right(str(year(dtDate),2)
tempdtDate = tempdtDate & "/" & right(str("0" & month(dtDate)) ,2)
tempdtDate = tempdtDate & "/" & right(str("0" & day(dtDate)),2)

ReverseDateAsString = tempdtDate
End Function

I didn't actually run this, but it should work...

Mike

 

[tincan56]

Yes "test" is the table name. And also as TempTable.
The original data is in table table.
I needed to format the yymmdd and get update into
TempTable (with yy/mm/dd).

I don't have problem with putting the slashes in yymmdd cuz I tried the same code in query and it worked.
I just have the problem with the "type mismatch" error
after Set rec = db.OpenRecordset("test&quot code.
Somehow rec is equal to nothing or empty. And I have no clue why.

Thanks so much!

 

[aristos]

The line

Set rec = db.OpenRecordset("test&quot

"test" should be the name of a table or a query.
The argument could also be an SQL statement such as

set rec = db.OpenRecordset("SELECT * FROM USERS&quot

In the first case ("test&quot you are saying: "Make the object 'rec' be the same thing as the table or query named 'test'"

In the second, you are saying: "Make the object 'rec' be the same thing as the results of the SQL statement"

HTH

Mike

 

[tincan56]

Sorry I meant The original data is in "Test" table.

 

[tincan56]

I am still having trouble with this problem... : (

yes my table names are the same as it was in the code.

Set rec = db.OpenRecordset("test&quot

Anymore suggestions please?

 

[tincan56]

Thanks for everyone's time and help.
I had both ActiveX Data Objects and DAO checked in
Tools->References section. And the problem went away after I unchecked the ActiveX Data Objects box. So now I am only using the DAO.

Everyone have a great day and thanks again for all the suggestions!