'Type Mismatch' Error- PLEASE HELP

[khanner]

we have a macro setup to update data info by uploading an excel spreadhseet
in the access database. However when i try this, i get a 'type mismatch'
error. the excel spreadsheet is labelled correctly. below is the line that
has a break ( and highlightes in yellow):

rptdate = CInt(Mid(FileList, 43, 2)) & "/" & CInt(Mid(FileList, 45, 2)) &
"/" & CInt(Mid(FileList, 47, 2))


any suggestions? thanks in advance!!!

 

[Jerry Whittle]

If rptdate is a Date/Time fields, what you've created isn't a date. You could
use the CDate function or wrap it around with # & and & # .

Be very sure that all your records return valid dates otherwise there can be
problems. You might want to wrap it in the IsDate function to make sure that
your data is 'clean'.

Also the CInt's aren't really needed unless you want to get rid of any
leading zeros. That could be a problem with the years from 2000 to 2009
anyway.

 

[khanner]

Thanks, any chance you can give me the code to write it in and let me know
where i should out it or what i should replace it with. im horrible at VBA
and fixing this for someone. Thanks!

 

[Jerry Whittle]

Here's how I would do it in a query. If you are doing it in VBA, it could be
a little more complex.

Basically it checks if the text could be evaluated as a date using IsDate.
If yes then it converts it to a date using CDate. If no it uses the bogus
date of 1/1/1950. You could change that date to something else if you want.
Later you may need to fix records with the bogus date.

rptdate: IIf(IsDate(Mid(FileList, 43, 2) & "/" & Mid(FileList, 45, 2) & "/"
& Mid(FileList, 47, 2))= True, CDate(Mid(FileList, 43, 2) & "/" &
Mid(FileList, 45, 2) & "/" & Mid(FileList, 47, 2)), #1/1/1950#)

 

[khanner]

thanks. so should i replace my formula with yours?

 

[Jerry Whittle]

You should test mine and see if it works properly. Also mine is for a query
and you were talking about VBA and a module. Therefore I don't know if what I
posted will work properly. I recommend making a backup of the database and
testing, testing, testing.

 

[khanner]

yes it dosent work, i am working on access and using modules, when i move my
mouse cursor ontop of 'rptdate" it says 12:00:00 AM. does that sound right?
and then when i move my nouse cursor to the remaining formula i get the link
to the excel file im trying to upload. thoughts?

 

[John W. Vinson]

yes it dosent work, i am working on access and using modules, when i move my
mouse cursor ontop of 'rptdate" it says 12:00:00 AM. does that sound right?
and then when i move my nouse cursor to the remaining formula i get the link
to the excel file im trying to upload. thoughts?

Please post your actual code. Neither Jerry nor anyone else can fix code that
they can't see!

 

[John Spencer]

Assuming that rptDate is specified as a Date in your code

Dim rptDate as Date

IF IsDate(Format(Mid(FileList,43,6),"@@-@@-@@")) Then
rptDate = CDate(Format(Mid(FileList,43,6),"@@-@@-@@"))
Else
rptDate =#1/1/1900#
END IF

Beyond that you have not shown us the remainder of the VBA. So this advice
while correct may not be what you need. First this assumes that characters 43
to 48 in the string specify a date. The assumption is that the date is in the
format mmddyy or perhaps ddmmyy. That depends on where in the world you are
and which date format you have set.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Jerry Whittle]

Sorry but I'm not much of a coder. I do almost all of my work with queries
and SQL.

 

[khanner]

here is the whole code:

ublic Function TransferDBPaydowns()

Dim Filename As FileDialog
Dim varFile As Variant
Dim FileList As String
FileList = ""
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
Dim rptdate As Date

'Get file to update from User
With Filename
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Formats", "*.xls,*.xlt"
If .Show = True Then
For Each varFile In .SelectedItems
FileList = varFile
Next
Else: Exit Function
End If
End With

rptdate = CInt(Mid(FileList, 43, 2)) & "/" & CInt(Mid(FileList, 45, 2)) &
"/" & CInt(Mid(FileList, 47, 2))

DoCmd.Hourglass True


CurrentDb.Execute "delete * FROM TBL_CFUpload"



and yes the ormat is MMDDYY

thanks for all your help guys!

 

[John Spencer]

So, at this point did you try any of the suggested methods.

Public Function TransferDBPaydowns()

Dim Filename As FileDialog
Dim varFile As Variant
Dim FileList As String
FileList = ""
Set Filename = Application.FileDialog(msoFileDialogFilePicker)
Dim rptdate As Date

'Get file to update from User
With Filename
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Formats", "*.xls,*.xlt"
If .Show = True Then
For Each varFile In .SelectedItems
FileList = varFile
Next
Else: Exit Function
End If
End With

Debug.Print "File List" & FileList 'What is there - If anything

If Len(FileList) > 42 Then
IF IsDate(Format(Mid(FileList,43,6),"@@-@@-@@")) Then
rptDate = CDate(Format(Mid(FileList,43,6),"@@-@@-@@"))
Else
rptDate =#1/1/1900#
END IF

Debug.Print rptDate

End IF


DoCmd.Hourglass True


CurrentDb.Execute "delete * FROM TBL_CFUpload"


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[khanner]

I tried what you suggested and replaced the break line with your suggestions
but it dosent recognize the "@@-@@-@@" and if i change it to dd-mm-yy it stil
dosent recognize it. im totally lost!!!

 

[John Spencer]

"but it dosent recognize the "@@-@@-@@" " Can you explain what you mean by
that statement?

I know the function works in Access 2003. I've tested it. At this point all
I can think of is that your VBA is corrupted.

Try Allen Browne's article on Recovering from Corruption at:
http://allenbrowne.com/ser-47.html

Also you take a look at Tony Toews' site
http://www.granite.ab.ca/access/corruptmdbs.htm

Jerry Whittle, Microsoft Access MVP has a white paper in a Word document named
Fix Corrupt Access Database towards the bottom this page:
http://www.rogersaccesslibrary.com/OtherLibraries.asp

It could be as simple as running the decompile on your code.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County