'Type Mismatch' Error- PLEASE HELP

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" wrote:

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

khanner wrote:
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?

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"khanner" wrote:

.

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 wrote:
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" wrote:

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

khanner wrote:
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?

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"khanner" wrote:

.

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" wrote:

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 wrote:
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" wrote:

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

khanner wrote:
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?

"Jerry Whittle" wrote:

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"khanner" wrote:

.

.

"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

khanner wrote:
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!!!