If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
'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: . |
#12
|
|||
|
|||
'Type Mismatch' Error- PLEASE HELP
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: . |
#13
|
|||
|
|||
'Type Mismatch' Error- PLEASE HELP
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: . . |
#14
|
|||
|
|||
'Type Mismatch' Error- PLEASE HELP
"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!!! |
|
Thread Tools | |
Display Modes | |
|
|