(Msg. 1) Posted: Mon Oct 06, 2008 9:51 am
Post subject: Data Mistype error Archived from groups: microsoft>public>access (more info?)
Please help! I keep getting a data mistyupe error when I try to use a Macro
that calls for an update query to append a table. The date field has no
format (date and time) and shows just the monthend date of 9/30/2008. The
macro and query both call for the date to be input. When I type in 9/30/2008
or any combination of the above 09/30/2008, 9/30/08 I get the mismatch error.
I tried changeing the format in the table to be short date,but I can't modify
the table since it is linked.
This update has worked for 2 years but at this time I am skipping from
5/31/08 to 9/30/2008 because of a time span when we did not keep these
numbers.
Any help is appreciated.
(Msg. 2) Posted: Mon Oct 06, 2008 10:02 am
Post subject: Re: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
sounds like formating on the control, or maybe an input mask.
(Msg. 3) Posted: Mon Oct 06, 2008 10:02 am
Post subject: Re: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Are you able to run the query itself without using the macro?
Can you "hardcode" the date #9/30/2008# as a criterion in your query?
(NOTE: the "format" affects only how the date is displayed, not how it is
stored)
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Iamlovespar" <Iamlovespar.RemoveThis@discussions.microsoft.com> wrote in message
news:FC4FB484-4467-428C-B34F-1785344F4C1B@microsoft.com...
> Please help! I keep getting a data mistyupe error when I try to use a
> Macro
> that calls for an update query to append a table. The date field has no
> format (date and time) and shows just the monthend date of 9/30/2008. The
> macro and query both call for the date to be input. When I type in
> 9/30/2008
> or any combination of the above 09/30/2008, 9/30/08 I get the mismatch
> error.
> I tried changeing the format in the table to be short date,but I can't
> modify
> the table since it is linked.
> This update has worked for 2 years but at this time I am skipping from
> 5/31/08 to 9/30/2008 because of a time span when we did not keep these
> numbers.
> Any help is appreciated.
(Msg. 4) Posted: Mon Oct 06, 2008 10:12 am
Post subject: RE: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
Instead of appending the user input, try datevalue([USER INPUT])
"Iamlovespar" wrote:
> Please help! I keep getting a data mistyupe error when I try to use a Macro
> that calls for an update query to append a table. The date field has no
> format (date and time) and shows just the monthend date of 9/30/2008. The
> macro and query both call for the date to be input. When I type in 9/30/2008
> or any combination of the above 09/30/2008, 9/30/08 I get the mismatch error.
> I tried changeing the format in the table to be short date,but I can't modify
> the table since it is linked.
> This update has worked for 2 years but at this time I am skipping from
> 5/31/08 to 9/30/2008 because of a time span when we did not keep these
> numbers.
> Any help is appreciated.
(Msg. 5) Posted: Mon Oct 06, 2008 10:57 am
Post subject: Re: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I was able to put criteria in for the input date of between #6/30/2008# and
#5/31/2009#and it will now accept a date. But all my appends end up in 0 rows
appended. I have data in the linking spreadsheet (database) and I have
verified it is correct, but it won't append to the table in the back end. I
have changed the append
to be to a different database and wrote in the path to the Back end. My
links are still valid and good. I have tried putting in the date in the table
with no other data and still no append.
Any thoughts?
"Jeff Boyce" wrote:
> Are you able to run the query itself without using the macro?
>
> Can you "hardcode" the date #9/30/2008# as a criterion in your query?
>
> (NOTE: the "format" affects only how the date is displayed, not how it is
> stored)
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
>
> "Iamlovespar" <Iamlovespar.RemoveThis@discussions.microsoft.com> wrote in message
> news:FC4FB484-4467-428C-B34F-1785344F4C1B@microsoft.com...
> > Please help! I keep getting a data mistyupe error when I try to use a
> > Macro
> > that calls for an update query to append a table. The date field has no
> > format (date and time) and shows just the monthend date of 9/30/2008. The
> > macro and query both call for the date to be input. When I type in
> > 9/30/2008
> > or any combination of the above 09/30/2008, 9/30/08 I get the mismatch
> > error.
> > I tried changeing the format in the table to be short date,but I can't
> > modify
> > the table since it is linked.
> > This update has worked for 2 years but at this time I am skipping from
> > 5/31/08 to 9/30/2008 because of a time span when we did not keep these
> > numbers.
> > Any help is appreciated.
>
>
>
(Msg. 6) Posted: Mon Oct 06, 2008 12:51 pm
Post subject: Re: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
If this were mine, I'd convert the append query back to a select query and
make sure the query grabs the rows.
Then I check the indexing on the table to make sure the append isn't running
up against a key ...
Regards
Jeff Boyce
Microsoft Office/Access MVP
""Iamlovespar" <Iamlovespar.TakeThisOut@discussions.microsoft.com> wrote in message
news:3A246477-30FF-4CC3-8F95-92BCED510A8C@microsoft.com...
>I was able to put criteria in for the input date of between #6/30/2008# and
> #5/31/2009#and it will now accept a date. But all my appends end up in 0
> rows
> appended. I have data in the linking spreadsheet (database) and I have
> verified it is correct, but it won't append to the table in the back end.
> I
> have changed the append
> to be to a different database and wrote in the path to the Back end. My
> links are still valid and good. I have tried putting in the date in the
> table
> with no other data and still no append.
> Any thoughts?
>
> "Jeff Boyce" wrote:
>
>> Are you able to run the query itself without using the macro?
>>
>> Can you "hardcode" the date #9/30/2008# as a criterion in your query?
>>
>> (NOTE: the "format" affects only how the date is displayed, not how it
>> is
>> stored)
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>>
>> "Iamlovespar" <Iamlovespar.TakeThisOut@discussions.microsoft.com> wrote in message
>> news:FC4FB484-4467-428C-B34F-1785344F4C1B@microsoft.com...
>> > Please help! I keep getting a data mistyupe error when I try to use a
>> > Macro
>> > that calls for an update query to append a table. The date field has no
>> > format (date and time) and shows just the monthend date of 9/30/2008.
>> > The
>> > macro and query both call for the date to be input. When I type in
>> > 9/30/2008
>> > or any combination of the above 09/30/2008, 9/30/08 I get the mismatch
>> > error.
>> > I tried changeing the format in the table to be short date,but I can't
>> > modify
>> > the table since it is linked.
>> > This update has worked for 2 years but at this time I am skipping from
>> > 5/31/08 to 9/30/2008 because of a time span when we did not keep these
>> > numbers.
>> > Any help is appreciated.
>>
>>
>>
(Msg. 7) Posted: Mon Oct 06, 2008 1:56 pm
Post subject: Re: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
I copied the query and changed it back to a select query. It did not pick up
the rows and did not pick up the input date as a field. I am really confused
now. As you can tell I did not create this database. The original table that
was updated is an Excel link, marked with the excel symbol. I am trying to
find where the Link is appended to the table but so far no luck. Is that what
I am missing?
"Jeff Boyce" wrote:
> If this were mine, I'd convert the append query back to a select query and
> make sure the query grabs the rows.
>
> Then I check the indexing on the table to make sure the append isn't running
> up against a key ...
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> ""Iamlovespar" <Iamlovespar.DeleteThis@discussions.microsoft.com> wrote in message
> news:3A246477-30FF-4CC3-8F95-92BCED510A8C@microsoft.com...
> >I was able to put criteria in for the input date of between #6/30/2008# and
> > #5/31/2009#and it will now accept a date. But all my appends end up in 0
> > rows
> > appended. I have data in the linking spreadsheet (database) and I have
> > verified it is correct, but it won't append to the table in the back end.
> > I
> > have changed the append
> > to be to a different database and wrote in the path to the Back end. My
> > links are still valid and good. I have tried putting in the date in the
> > table
> > with no other data and still no append.
> > Any thoughts?
> >
> > "Jeff Boyce" wrote:
> >
> >> Are you able to run the query itself without using the macro?
> >>
> >> Can you "hardcode" the date #9/30/2008# as a criterion in your query?
> >>
> >> (NOTE: the "format" affects only how the date is displayed, not how it
> >> is
> >> stored)
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >>
> >> "Iamlovespar" <Iamlovespar.DeleteThis@discussions.microsoft.com> wrote in message
> >> news:FC4FB484-4467-428C-B34F-1785344F4C1B@microsoft.com...
> >> > Please help! I keep getting a data mistyupe error when I try to use a
> >> > Macro
> >> > that calls for an update query to append a table. The date field has no
> >> > format (date and time) and shows just the monthend date of 9/30/2008.
> >> > The
> >> > macro and query both call for the date to be input. When I type in
> >> > 9/30/2008
> >> > or any combination of the above 09/30/2008, 9/30/08 I get the mismatch
> >> > error.
> >> > I tried changeing the format in the table to be short date,but I can't
> >> > modify
> >> > the table since it is linked.
> >> > This update has worked for 2 years but at this time I am skipping from
> >> > 5/31/08 to 9/30/2008 because of a time span when we did not keep these
> >> > numbers.
> >> > Any help is appreciated.
> >>
> >>
> >>
>
>
>
(Msg. 8) Posted: Mon Oct 06, 2008 2:43 pm
Post subject: Re: Data Mistype error [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
If your Access database (.mdb file) no longer shows a link to that Excel
"data", and if your query is trying to read that data, it won't work.
Please post the SQL statement of the query.
Regards
Jeff Boyce
Microsoft Office/Access MVP
"Iamlovespar" <Iamlovespar.RemoveThis@discussions.microsoft.com> wrote in message
news:7F640259-CE72-4F89-989C-6DCE833C9740@microsoft.com...
>I copied the query and changed it back to a select query. It did not pick
>up
> the rows and did not pick up the input date as a field. I am really
> confused
> now. As you can tell I did not create this database. The original table
> that
> was updated is an Excel link, marked with the excel symbol. I am trying to
> find where the Link is appended to the table but so far no luck. Is that
> what
> I am missing?
>
> "Jeff Boyce" wrote:
>
>> If this were mine, I'd convert the append query back to a select query
>> and
>> make sure the query grabs the rows.
>>
>> Then I check the indexing on the table to make sure the append isn't
>> running
>> up against a key ...
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> ""Iamlovespar" <Iamlovespar.RemoveThis@discussions.microsoft.com> wrote in message
>> news:3A246477-30FF-4CC3-8F95-92BCED510A8C@microsoft.com...
>> >I was able to put criteria in for the input date of between #6/30/2008#
>> >and
>> > #5/31/2009#and it will now accept a date. But all my appends end up in
>> > 0
>> > rows
>> > appended. I have data in the linking spreadsheet (database) and I have
>> > verified it is correct, but it won't append to the table in the back
>> > end.
>> > I
>> > have changed the append
>> > to be to a different database and wrote in the path to the Back end. My
>> > links are still valid and good. I have tried putting in the date in the
>> > table
>> > with no other data and still no append.
>> > Any thoughts?
>> >
>> > "Jeff Boyce" wrote:
>> >
>> >> Are you able to run the query itself without using the macro?
>> >>
>> >> Can you "hardcode" the date #9/30/2008# as a criterion in your query?
>> >>
>> >> (NOTE: the "format" affects only how the date is displayed, not how
>> >> it
>> >> is
>> >> stored)
>> >>
>> >> Regards
>> >>
>> >> Jeff Boyce
>> >> Microsoft Office/Access MVP
>> >>
>> >>
>> >> "Iamlovespar" <Iamlovespar.RemoveThis@discussions.microsoft.com> wrote in message
>> >> news:FC4FB484-4467-428C-B34F-1785344F4C1B@microsoft.com...
>> >> > Please help! I keep getting a data mistyupe error when I try to use
>> >> > a
>> >> > Macro
>> >> > that calls for an update query to append a table. The date field has
>> >> > no
>> >> > format (date and time) and shows just the monthend date of
>> >> > 9/30/2008.
>> >> > The
>> >> > macro and query both call for the date to be input. When I type in
>> >> > 9/30/2008
>> >> > or any combination of the above 09/30/2008, 9/30/08 I get the
>> >> > mismatch
>> >> > error.
>> >> > I tried changeing the format in the table to be short date,but I
>> >> > can't
>> >> > modify
>> >> > the table since it is linked.
>> >> > This update has worked for 2 years but at this time I am skipping
>> >> > from
>> >> > 5/31/08 to 9/30/2008 because of a time span when we did not keep
>> >> > these
>> >> > numbers.
>> >> > Any help is appreciated.
>> >>
>> >>
>> >>
>>
>>
>>
All times are: Eastern Time (US & Canada) (change) Goto page 1, 2
Page 1 of 2
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other