'Type Mismatch' Error- PLEASE HELP

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!!! 
0
Utf
3/15/2010 2:50:01 PM
access.queries 6343 articles. 1 followers. Follow

13 Replies
909 Views

Similar Articles

[PageSpeed] 30

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


"khanner" wrote:

> 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!!! 
0
Utf
3/15/2010 3:30:02 PM
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" wrote:

> 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.
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "khanner" wrote:
> 
> > 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!!! 
0
Utf
3/15/2010 3:40:02 PM
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#)
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"khanner" wrote:

> 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" wrote:
> 
> > 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.
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "khanner" wrote:
> > 
> > > 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!!! 
0
Utf
3/15/2010 3:51:01 PM
thanks. so should i replace my formula with yours? 

"Jerry Whittle" wrote:

> 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#)
> -- 
> Jerry Whittle, Microsoft Access MVP 
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> 
> 
> "khanner" wrote:
> 
> > 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" wrote:
> > 
> > > 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.
> > > -- 
> > > Jerry Whittle, Microsoft Access MVP 
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > 
> > > 
> > > "khanner" wrote:
> > > 
> > > > 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!!! 
0
Utf
3/15/2010 3:58:01 PM
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:

> thanks. so should i replace my formula with yours? 
> 
> "Jerry Whittle" wrote:
> 
> > 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#)
> > -- 
> > Jerry Whittle, Microsoft Access MVP 
> > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > 
> > 
> > "khanner" wrote:
> > 
> > > 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" wrote:
> > > 
> > > > 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.
> > > > -- 
> > > > Jerry Whittle, Microsoft Access MVP 
> > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > 
> > > > 
> > > > "khanner" wrote:
> > > > 
> > > > > 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!!! 
0
Utf
3/15/2010 4:18:02 PM
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:
> 
> > thanks. so should i replace my formula with yours? 
> > 
> > "Jerry Whittle" wrote:
> > 
> > > 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#)
> > > -- 
> > > Jerry Whittle, Microsoft Access MVP 
> > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > 
> > > 
> > > "khanner" wrote:
> > > 
> > > > 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" wrote:
> > > > 
> > > > > 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.
> > > > > -- 
> > > > > Jerry Whittle, Microsoft Access MVP 
> > > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > > 
> > > > > 
> > > > > "khanner" wrote:
> > > > > 
> > > > > > 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!!! 
0
Utf
3/15/2010 4:28:01 PM
On Mon, 15 Mar 2010 09:28:01 -0700, khanner
<khanner@discussions.microsoft.com> 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? 

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

             John W. Vinson [MVP]
0
John
3/15/2010 5:28:29 PM
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:
>>
0
John
3/15/2010 5:33:45 PM
Sorry but I'm not much of a coder. I do almost all of my work with queries 
and SQL.
-- 
Jerry Whittle, Microsoft Access MVP 
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"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:
> > 
> > > thanks. so should i replace my formula with yours? 
> > > 
> > > "Jerry Whittle" wrote:
> > > 
> > > > 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#)
> > > > -- 
> > > > Jerry Whittle, Microsoft Access MVP 
> > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > 
> > > > 
> > > > "khanner" wrote:
> > > > 
> > > > > 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" wrote:
> > > > > 
> > > > > > 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.
> > > > > > -- 
> > > > > > Jerry Whittle, Microsoft Access MVP 
> > > > > > Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
> > > > > > 
> > > > > > 
> > > > > > "khanner" wrote:
> > > > > > 
> > > > > > > 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!!! 
0
Utf
3/15/2010 5:55:01 PM
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:
> >>
> .
> 
0
Utf
3/15/2010 6:31:01 PM
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:
>>>>
>> .
>>
0
John
3/15/2010 9:17:27 PM
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:
> >>>>
> >> .
> >>
> .
> 
0
Utf
3/15/2010 10:11:01 PM
"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!!! 
> 
0
John
3/16/2010 12:53:45 PM
Reply:

Similar Artilces:

Run-time error '2147217900 (80040e14)
I have a tree list control I want to populate with Categories based on a Client. The client number is input into a text box & then the update event populates the tree based on the selection. I get a syntax error when it tries to open the recordset based on the SQL. Two tables are invloved (Client & Categories; in a 1-to-many relationship on pID field). Here'e the code: Dim rstCategory As New ADODB.recordset, rstComponent As New ADODB.recordset Dim rstSubComponent As New ADODB.recordset Dim tvwTree As Object Dim nodX As Node Dim I As Integer Dim blnAllRecs As B...

Removing characters from cell
Hope someone can help me with this one.... I have text in column "A" a list of parts like the following..... ...nhg1234 ..nhg1235.54 nhg3456 ....nhg1253.7 Is thier anyway of removing the dots in front the part number? I dont want the points moving after the the main part of the number. Please help TiA mag()() Do you part numbers all start with nhg? If yes, you could select column A edit|Replace what: .n with: n replace all And keep hitting the replace all button until all are fixed. "Mag()()" wrote: > > Hope someone can help me with this one.... >...

Action PostURL Error
Hi, I have a web service for integration, but when I test it sent the follow error "<description>Error in action posturl.</description><details>Failed to connect the url http://localhost/customwebservices" Thanks in advance Victor ...

Error Messages That Seem Incorrect
I have the following two messages appearing: "Error. An error has occurred. For more information, contact your system administrator" and "An error occurred while trying to send the e-mail. Try again." The first happens when closing activities or qualifying leads to accounts and contacts. The second when sending CRM e-mail from Outlook. In both cases the activity had actually been completed successfuly. eg customer received the e-mail, new account/contact created or activity showing in history. My users are repeating the process because of the error message and w...

Outlook 2003, Relaying denied error, Windows XP
I seem to have a problem when Outlook 2003 is first opened that it will not allow an email with an attachment to be sent. A 'Relaying denied' error message comes up. If I send an email without attachment this works OK and triggers the Norton anit virus software to check email before sent. After this the problem seems to go away i.e. I can now send attachments. Any ideas on why this error occurrs ? Is there a setup feature I've missed ? Have you set up authentication to your outgoing server ("More Settings..." on your account page, go to the "Outgoing Server&qu...

External Link-help!
Hi there, I am trying to get rid of an external link to my excel spreadsheet that I created. When I email the excel file to a coworker, the file asks to update links. But, I want to get rid of all the links so that when others open it, the message will not come up. Under the Edit menu, the Links option is not highlighted. Somehow, the external link got embedded into the file, and I need a solution to delete it out. My job is depending on this resolution! Thanks, Tina I could really use some help! Try Bill Manville's addin. It'll find those pesky ones. Findlink.zip ...

!!HELP!! OWA ans Outlook do not synch
OK so I rebuilt my SBS 2003 server and used Exmerge to exmerge all my users mailboxes out. Successfully exmerged them back into Exchange. Had to recreate new profiles on the users workstations but all appears to be good. One slight problem. For my external users who use Citrix and log onto the Citrix server desktop they cannot use Outlook there. I do have a case open with Microsoft regarding this issue however my immediate issue to resolve is why I can log on as a user here in the office, open Outlook and all their mail is there. However when I use OWA the mailbox is incomplete. Does no...

forgot password?please help
I FORGOT MY PASSWORD ON MY MICROSOFT MONEY 2000 BUSINESS - PERSONAL I TRYED EVERY NAME AND NUMBER FOR 2 DAYS tsger@aol.com wrote: > I FORGOT MY PASSWORD ON MY MICROSOFT MONEY 2000 BUSINESS - > PERSONAL I TRYED EVERY NAME AND NUMBER FOR 2 DAYS That's got to be VERY FRUSTRATING! Not only that, but your CAPS LOCK KEY is stuck! I'm sorry for you! ...

Error when using RMS EDC Settle function
Hello. We run RMSsp2, and use PCCharge PaymntSvr EDC software/First Data processor. PCCharge settles fine, if trying from within RMS, receive error on: 1st attempt: "Invalid Merchant" box error appears. 2nd and subsequent attempts "Invalid Destination Zip" box error appears. Erro occurs withing few seconds of settle attempt, settle progress doesnt go beyond 1/10 of progress meter and this error pops up. Any ideas? Our EDC config is fine and all batches and items are ok in PCCharge. thanks luke ...

Complie Error
Hi: When I open Excel 2003 I get a Microsoft Vusual Basic error message: "Compile Error in hidden module:AutoExecNew". My excel program runs fine after I close that message. But when I try to close Excel, I get a different message: ""Compile Error in hidden module: DistMon" I could not find any help on Micorsoft's Knowledgebase. Could any please help solving this problem? Thanks Mohan Hi have a look at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q307410 -- Regards Frank Kabel Frankfurt, Germany mohan wrote: > Hi: > > When I open Ex...

Hyperlink Error
I have a hyperlink set-up for a netwrok file. It works fine, but when the file is copied the hyperlin chages to the folder that the file has been copied to. original link - L:\folder\folder\file Changes to - C:\file How can I get it to keep the original link? Justin ...

need a bit of help.....
I have generated a form that shows money amounts. yet i want two colums that separates the dollar amount from cents. I have for example colum *H* as the dollar amount and colum *I* as the cent amounts. Now i have tried Format Cell to set it up but it doesnt work. for example i tried the custom format for the dollar amount and it rounds it. and if there's an amount of 1108.83 the cell will show 1109. so it does not show the true value amount. i also tried the same approach with the =RIGHT(H1,2) but with the left and Mid but still didnt work, because i would have to enter the same amount o...

Help! page range prob in print
Hi I worked on print.Everythings working perfectly except one problem.ie. When i set the PageRange option to 'ALL' option and press print button..instead of displaying only the pages which has information ..its displaying that all pages from 1 to 65535 r going to be printed. How do i remove this problem? thanks, vani I thing you may failed to set the maximum number of pages to print. Check CPrintInfo::SetMaxPage(nMaxPage); method regds Jibesh -- ------------------------------------------------------------------------- FIGHT BACK AGAINST SPAM! Download Spam Inspecto...

MSP 2007 resources help
Hi all, Three questions: 1. What the constraint A<B,C means and how can I entering in the MSP? 2. I have 3 kinds of human resources. Lets say for example, kind A are engineers. I have 8 of them and everyone is costing 1500 Euros/month. Task A needs 4 engineers. How can I assign them to the task and add the appropriate cost? 3. I have 3 machines as a resource. Each machine has a usage cost of 3000 euros per task and function cost 2400 euros per month. How can I assign them in the appropriate tasks? And how can I distribute their cost? Thank you in advance -- - Hello...

Outlook 2007 CRM error
I get an error when starting Outlook that I have no CRM functionality, event ID 5897 shows up in the app logs with an autentication error message 0x8007002. Any ideas on what I should be looking for or how to fix this? ...

'error PRJ0019: A tool returned an error code from "Performing registration" Project Name
This is a multi-part message in MIME format. ------=_NextPart_000_0006_01C80055.07F02D20 Content-Type: text/plain; charset="gb2312" Content-Transfer-Encoding: quoted-printable Hi, all I wrote a COM project with VS2003, when I compile this project = ,finally I get the error like 'error PRJ0019: A tool returned an error = code from "Performing registration" Project Name. Any helpful ideas would be highly appreciated! -ja ------=_NextPart_000_0006_01C80055.07F02D20 Content-Type: text/html; charset="gb2312" Content-Transfer-Encoding: quoted-printable...

SDK Q: "taxes distribution(s) does not equal the actual amount" error when posting
I am trying to have our orders processing system properly populate the Great Plains tables so we can TRANSFER our orders to invoices (at which point third-party tools perform so WBS re-distribution) and then POST them to GL. Thanks to rrs1977@gmail.com for your previous help. With that, I've been able to get the system to create orders and batches which Dynamics allows our users to open and Transfer to invoice (and properly triggering our third-party redistribution utilities). However, when they try to post the invoices to GL, an error shows up indicating "taxes distribution(s...

CRM integration with GP: BizTalk errors
I've completed the integration, but I'm getting 2 Biztalk errors. Any help or direction appreciated: APPLICATION ERROR: Event Type: Error Event Source: Application Error Event Category: (100) Event ID: 1000 Date: 8/24/2005 Time: 4:51:50 AM User: N/A Computer: NJBTS02 Description: Faulting application MSCIS.exe, version 3.0.2023.0, faulting module ntdll.dll, version 5.2.3790.1830, fault address 0x000396d0. For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp. Data: 0000: 41 70 70 6c 69 63 61 74 Applicat 0008: 69 6f 6e 20 46 61 69 6c ...

Link Help!!!
hi, How can I add or remove link from “My Work” under workplace, like Calendar or Reports...? And where the file sitelog.xml?? thnx, ...

Error after attempting to send message OTL6
each time i attempt to send a message in outlook6 the send and receive bar will proceed right to the end and immediately post following: unknown error protocal:SMTP, port:0, Secure(SSL):No Error Number OX800C0131 The new window: DIRECTDB.DLL HAS COMMITTED ERROR MSIMN WILL CLOSE NOW PROPERTIES OF DIRECTDB.DLL IS VERSION 6.0.2800.1106 HELPPPPPPPPPPPPPPPPPPPPPP!!!!! waynedog_7@passport.com <waynedog@prodigy.net> wrote: > each time i attempt to send a message in outlook6 Ask in an Outlook Express newsgroup. -- Brian Tillman ...

POP3 error
why does this keep appearing when I try to send pictures? Please post the entire error message (copy / paste). Is it only on picture emails - not on text emails? <maddogn@live.com> wrote in message news:u$x4XpEfKHA.2160@TK2MSFTNGP02.phx.gbl... > why does this keep appearing when I try to send pictures? Probably because you don't have your email account set up properly. It would have been helpful if you had included the complete error = message. Are you trying to set up your Live.com account in Windows Mail? If so, use the settings given here: http://mailcall...

Why Out of memory error when trying to create a thread?
I'm using MS Visual C++ .NET, Windows XP, and 1GB RAM. Why could be the reason why I get an error message saying Out of memory when running the following code CClientThread* pThread = (CClientThread*) AfxBeginThread(RUNTIME_CLASS(CClientThread), THREAD_PRIORITY_NORMAL, 0, CREATE_SUSPENDED); My application does only have this additional thread. Joachim wrote: > I'm using MS Visual C++ .NET, Windows XP, and 1GB RAM. > Why could be the reason why I get an error message saying > > Out of memory > > when running the following code > > CClientThread* pThread =...

trendline HELP!
I have a chart that i am making of PPMs for the year of 2006. I have jan-may in the data series and the rest are left blank. I am trying to get a trendline in where it will stop at whatever month I am at but update when a new month's data is entered into the box. However, I cannot figure out how to do this, when I do have a trend line it goes to like -4000. can anyone help me please? Make a dynamic chart: http://peltiertech.com/Excel/Charts/Dynamics.html http://www.tushar-mehta.com/excel/newsgroups/dynamic_charts/index.html http://www.stfx.ca/people/bliengme/ExcelTips/Dynamic.htm...

Help what kind of formula?
I've used Excel formulas in a basic way (sums, averages, divisions, and multiplication of cell data) for about a year. I came across this formula today for a project I need to complete. I've never seen the "SUMIF" or the dollar symbol, OR the ampersand! After hilighting the formula to see if I could just figure it out, I'm stumped. Any chance someone could explain what function these symbols serve Here is the formula =SUMIF($D$3:$D$28,"="&$C38,H$3:H$28) :confused: -- Emil0 ------------------------------------------------------------------------ Emil...

CrmCheckPrivilege failed error
We are using CRM 4.0, which was upgraded from 3.0. I just added new security roles into an existing business unit. Then I added new users and associated them with the new security roles. When the user tries to log in, I get this error: SecLib::CrmCheckPrivilege failed. Returned hr = -2147220960 on UserId: 9b85226b-0254-dd11-8024-001b78d05d74 and PrivilegeId: 588725dd-c878-41c5a4c3-5efc93cd3ffd If I take a user that existed in CRM before the upgrade and associate them to one of the new security roles, everything works ok. Does anyone have any idea how to fix this? I found knowledgebas...