Type Mismatch - not sure why

[laakins]

I'm using Access 2000 & am trying to automate a process for another person. I'm getting a type mismatch where I indicate below. The next line of similar code runs fun, but for some reason it doesn't like that one line of code. I've tried Dim a string variable & putting it all in there & then running the string, but I get the type mismatch when I assign the string variable. Here is the code:

Dim i As Integer
Dim NumOfFiles As Integer
Dim Path As String

Path = GetDBPath()

NumOfFiles = InputBox("Please enter the number of Bid files to import.", _
"Number of Bid Files&quot
For i = 1 To NumOfFiles
txtStatus.Value = "Lot" & i & ": Importing"
DoCmd.TransferText acImportFixed, "USE THIS Import Specification", "Lot" & i, Path & "BIDS0" & i & ".txt", False
txtStatus.Value = "Lot" & i & ": Changing DBM field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN DBM TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing LPayDate field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN LPayDate TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing LPurDate field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN LPurDate TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing LCADate field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN LCADate TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing LStatUpd field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN LStatUpd TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing DateNAChg field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN DateNAChg TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing RefDate field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN RefDate TEXT(10);"
txtStatus.Value = "Lot" & i & ": Changing WODate field size to 10"
CurrentDb.Execute "ALTER TABLE Lot" & i & " ALTER COLUMN WODate TEXT(10);"
txtStatus.Value = "Lot" & i & ": Converting all Date/Time Fields"
***********Error Occurs Here************************
CurrentDb.Execute "UPDATE Lot" & i & " " & _
"SET Lot" & i & ".DBM = DateValue(Mid([DBM],4,2) & " / " & Mid([DBM],6,2) & " / " & Mid([DBM],2,2)), " & _
"Lot" & i & ".LPayDate = DateValue(Mid([LPayDate],4,2) & " / " & Mid([LPayDate],6,2) & " / " & Mid([LPayDate],2,2)), " & _
"Lot" & i & ".LPurDate = DateValue(Mid([LPurDate],4,2) & " / " & Mid([LPurDate],6,2) & " / " & Mid([LPurDate],2,2)), " & _
"Lot" & i & ".LCADate = DateValue(Mid([LCADate],4,2) & " / " & Mid([LCADate],6,2) & " / " & Mid([LCADate],2,2)), " & _
"Lot" & i & ".LStatUpd = DateValue(Mid([LStatUpd],4,2) & " / " & Mid([LStatUpd],6,2) & " / " & Mid([LStatUpd],2,2)), " & _
"Lot" & i & ".DateNAChg = DateValue(Mid([DateNAChg],4,2) & " / " & Mid([DateNAChg],6,2) & " / " & Mid([DateNAChg],2,2)), " & _
"Lot" & i & ".RefDate = DateValue(Mid([RefDate],4,2) & " / " & Mid([RefDate],6,2) & " / " & Mid([RefDate],2,2)), " & _
"Lot" & i & ".WODate = DateValue(Mid([WODate],4,2) & " / " & Mid([WODate],6,2) & " / " & Mid([WODate],2,2));"
************************************************************
txtStatus.Value = "Lot" & i & ": Converting all Numeric Fields"
!!!!!!!!!!!!!!This works fine!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
CurrentDb.Execute "UPDATE Lot" & i & " " & _
"SET Lot" & i & ".APR = [APR]/1000, Lot" & i & ".LPayAmt = [LPayAmt]/100, " & _
"Lot" & i & ".OrigBal = [OrigBal]/100, Lot" & i & ".CurrBal = [CurrBal]/100, " & _
"Lot" & i & ".RefAmt = [RefAmt]/100, Lot" & i & ".WOAmt = [WOAmt]/100, Lot" & i & ".WOInt = [WOInt]/100;"
!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
txtStatus.Value = "Lot" & i & ": Done"
Next

Any insight would be appreciated.

 

[JoJ]

3 wild guesses:

1) Your windows date format settings changed.

2) The field .DBM is a text field and doesn't like dates. (I think it should be OK though?!)

3) The value of incoming [DBM] does not correspond to the string assignment of your DateValue function.

Good luck

 

[CajunCenturion]

Firstly, I would most definately create another variable for the table name. I think you'll this much more efficient as you'll be eliminating lots of concatenation operations.

Dim LotTable as String
LotTable = "Lot" & i

To the specifics of the problem, I can see three possible places where the Type Mismatch error may be occurring. It looks like the fields that you reformatting are dates. What might be confusing to the Date value function is the spaces around the slashes. First thing I would try is to remove those spaces. Another possibly may be that the Mid function is having trouble with the date field. You might converting those dates to strings before using Mid. A third possibility is to surround the field with the standard date delimiters #. Appling all three options, the statement would like the following:

SET " & LotTable & ".DBM = #DateValue(Mid(CStr([DBM]),4,2) & "/" & Mid(CStr([DBM]),6,2) & "/" & Mid(CStr([DBM]),2,2))#, " & _

Good Luck
--------------
[i]As a circle of light increases so does the circumference of darkness around it. - Albert Einstein[/i]

 

[chickey]

The mismatch problem is that when you are trying to
put the / into the text you are executing, it is ending
the string with the first " which means Access then
sees a / out on its own i.e. not part of the string you
are trying to build.

You need to replace all of the " / "
with
" & chr(34) & " / " & chr(34) & "
to keep the inverted commas and the slash in the text
string you are building.

Don't know if you get my meaning from that so the
result would be:

CurrentDb.Execute "UPDATE Lot" & i & " " & _
   "SET Lot" & i & ".DBM = DateValue(Mid([DBM],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([DBM],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([DBM],2,2)), " & _
   "Lot" & i & ".LPayDate = DateValue(Mid([LPayDate],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LPayDate],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LPayDate],2,2)), " & _
   "Lot" & i & ".LPurDate = DateValue(Mid([LPurDate],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LPurDate],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LPurDate],2,2)), " & _
   "Lot" & i & ".LCADate = DateValue(Mid([LCADate],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LCADate],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LCADate],2,2)), " & _
   "Lot" & i & ".LStatUpd = DateValue(Mid([LStatUpd],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LStatUpd],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([LStatUpd],2,2)), " & _
   "Lot" & i & ".DateNAChg = DateValue(Mid([DateNAChg],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([DateNAChg],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([DateNAChg],2,2)), " & _
   "Lot" & i & ".RefDate = DateValue(Mid([RefDate],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([RefDate],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([RefDate],2,2)), " & _
   "Lot" & i & ".WODate = DateValue(Mid([WODate],4,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([WODate],6,2) & " & Chr(34) & " / " & Chr(34) & " & Mid([WODate],2,2));"

After that, it may have issues with the date formatting as
already said in the previous posts. Reply if you still have
problems.

Regards...

 

[laakins]

Okay, I've changed to use a variable for table name which helps readability. Access adds the space around the slashes (I've tried to delete & when I move away from that line Access adds the spaces).

In the lines above the error I increase the field size to 10 & in so doing also make sure all the fields in the LotTable are Text. The actual values in those fields come in like 1010928 or 0900627 where the first digit is a century indicator (don't ask) & can be ignored, then yymmdd. This file goes out externally & that date format is difficult for others so I'm trying to change it to the traditional mm/dd/yy format (the field will still remain text after the update for the format). Minus the LotTable changes the query runs fine if I use in a normal query.

I'll try the suggestion above & let you know. Thanks

 

[laakins]

I've fixed it. I'm not real big on using the chr() function. Usually when you have to use double quotes you can use ' instead which is what I did & it worked.

CurrentDb.Execute "UPDATE " & LotTable & " " & _
"SET " & LotTable & ".DBM = DateValue(Mid([DBM],4,2) & '/' & Mid([DBM],6,2) & '/' & Mid([DBM],2,2)), " & _
LotTable & ".LPayDate = DateValue(Mid([LPayDate],4,2) & '/' & Mid([LPayDate],6,2) & '/' & Mid([LPayDate],2,2)), " & _
LotTable & ".LPurDate = DateValue(Mid([LPurDate],4,2) & '/' & Mid([LPurDate],6,2) & '/' & Mid([LPurDate],2,2)), " & _
LotTable & ".LCADate = DateValue(Mid([LCADate],4,2) & '/' & Mid([LCADate],6,2) & '/' & Mid([LCADate],2,2)), " & _
LotTable & ".LStatUpd = DateValue(Mid([LStatUpd],4,2) & '/' & Mid([LStatUpd],6,2) & '/' & Mid([LStatUpd],2,2)), " & _
LotTable & ".DateNAChg = DateValue(Mid([DateNAChg],4,2) & '/' & Mid([DateNAChg],6,2) & '/' & Mid([DateNAChg],2,2)), " & _
LotTable & ".RefDate = DateValue(Mid([RefDate],4,2) & '/' & Mid([RefDate],6,2) & '/' & Mid([RefDate],2,2)), " & _
LotTable & ".WODate = DateValue(Mid([WODate],4,2) & '/' & Mid([WODate],6,2) & '/' & Mid([WODate],2,2));"

Thanks everybody for you help.