'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!!!

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!!!

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!!!

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!!!

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!!!

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!!!

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!!!

On Mon, 15 Mar 2010 09:28:01 -0700, 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?

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

John W. Vinson [MVP]

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:

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!!!