Data Mistype error

  • Thread starter Thread starter Iamlovespar
  • Start date Start date
I

Iamlovespar

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.
 
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
 
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?
 
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
 
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?
 
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
 
INSERT INTO [FMS Process - History] ( Process1, Process2, Process3, Process4,
Process5, Process6, Process7, Process8, Process9, Process10, Process11,
Process12, Process13, Process14, Process15, Process16, Process17, Process18,
Process19, Process20, Process21, Process22, Process23, Process24, Process25,
Process26, Process27, Process28, Process29, Process30, Process31, Process32,
Process33, Process34, Process35, Process36, Process37, Process38, Process39,
Process40, Process41, Process42, Process43, Process44, Process45, Process46,
Process47, Process48, Process49, Process50, Process51, Process52, Process53,
Process54, Process55, Process56, Process57, [Input Date], Process58,
Process59, Process60 )
SELECT [Process PDG Link].Process1, [Process PDG Link].Process2, [Process
PDG Link].Process3, [Process PDG Link].Process4, [Process PDG Link].Process5,
[Process PDG Link].Process6, [Process PDG Link].Process7, [Process PDG
Link].Process8, [Process PDG Link].Process9, [Process PDG Link].Process10,
[Process PDG Link].Process11, [Process PDG Link].Process12, [Process PDG
Link].Process13, [Process PDG Link].Process14, [Process PDG Link].Process15,
[Process PDG Link].Process16, [Process PDG Link].Process17, [Process PDG
Link].Process18, [Process PDG Link].Process19, [Process PDG Link].Process20,
[Process PDG Link].Process21, [Process PDG Link].Process22, [Process PDG
Link].Process23, [Process PDG Link].Process24, [Process PDG Link].Process25,
[Process PDG Link].Process26, [Process PDG Link].Process27, [Process PDG
Link].Process28, [Process PDG Link].Process29, [Process PDG Link].Process30,
[Process PDG Link].Process31, [Process PDG Link].Process32, [Process PDG
Link].Process33, [Process PDG Link].Process34, [Process PDG Link].Process35,
[Process PDG Link].Process36, [Process PDG Link].Process37, [Process PDG
Link].Process38, [Process PDG Link].Process39, [Process PDG Link].Process40,
[Process PDG Link].Process41, [Process PDG Link].Process42, [Process PDG
Link].Process43, [Process PDG Link].Process44, [Process PDG Link].Process45,
[Process PDG Link].Process46, [Process PDG Link].Process47, [Process PDG
Link].Process48, [Process PDG Link].Process49, [Process PDG Link].Process50,
[Process PDG Link].Process51, [Process PDG Link].Process52, [Process PDG
Link].Process53, [Process PDG Link].Process54, [Process PDG Link].Process55,
[Process PDG Link].Process56, [Process PDG Link].Process57, [Enter Month-End
Date] AS [Month], [Process PDG Link].Process58, [Process PDG Link].Process59,
[Process PDG Link].Process60
FROM [Process PDG Link]
WHERE ((([Enter Month-End Date]) Between #6/30/2008# And #5/31/2009#));

The Process PDG link is the table marked with the excel symbol.
It has updated with the correct monthly information.
 
Before you go any further, consider reading up on "normalization" and
"relational database design". Your INSERT statement looks like you are
putting data into a spreadsheet.

Access is not a spreadsheet! To get the best (and easiest) use of Access'
relationally-oriented features and functions, you can't feed it 'sheet data.

Take the time now to normalize your Access table structure or you (and
Access) will be paying for it with everything you try to do. ... or, if you
don't really need a relational database, keep your data in your spreadsheet.

Regards

Jeff Boyce
Microsoft Office/Access MVP


Iamlovespar said:
INSERT INTO [FMS Process - History] ( Process1, Process2, Process3,
Process4,
Process5, Process6, Process7, Process8, Process9, Process10, Process11,
Process12, Process13, Process14, Process15, Process16, Process17,
Process18,
Process19, Process20, Process21, Process22, Process23, Process24,
Process25,
Process26, Process27, Process28, Process29, Process30, Process31,
Process32,
Process33, Process34, Process35, Process36, Process37, Process38,
Process39,
Process40, Process41, Process42, Process43, Process44, Process45,
Process46,
Process47, Process48, Process49, Process50, Process51, Process52,
Process53,
Process54, Process55, Process56, Process57, [Input Date], Process58,
Process59, Process60 )
SELECT [Process PDG Link].Process1, [Process PDG Link].Process2, [Process
PDG Link].Process3, [Process PDG Link].Process4, [Process PDG
Link].Process5,
[Process PDG Link].Process6, [Process PDG Link].Process7, [Process PDG
Link].Process8, [Process PDG Link].Process9, [Process PDG Link].Process10,
[Process PDG Link].Process11, [Process PDG Link].Process12, [Process PDG
Link].Process13, [Process PDG Link].Process14, [Process PDG
Link].Process15,
[Process PDG Link].Process16, [Process PDG Link].Process17, [Process PDG
Link].Process18, [Process PDG Link].Process19, [Process PDG
Link].Process20,
[Process PDG Link].Process21, [Process PDG Link].Process22, [Process PDG
Link].Process23, [Process PDG Link].Process24, [Process PDG
Link].Process25,
[Process PDG Link].Process26, [Process PDG Link].Process27, [Process PDG
Link].Process28, [Process PDG Link].Process29, [Process PDG
Link].Process30,
[Process PDG Link].Process31, [Process PDG Link].Process32, [Process PDG
Link].Process33, [Process PDG Link].Process34, [Process PDG
Link].Process35,
[Process PDG Link].Process36, [Process PDG Link].Process37, [Process PDG
Link].Process38, [Process PDG Link].Process39, [Process PDG
Link].Process40,
[Process PDG Link].Process41, [Process PDG Link].Process42, [Process PDG
Link].Process43, [Process PDG Link].Process44, [Process PDG
Link].Process45,
[Process PDG Link].Process46, [Process PDG Link].Process47, [Process PDG
Link].Process48, [Process PDG Link].Process49, [Process PDG
Link].Process50,
[Process PDG Link].Process51, [Process PDG Link].Process52, [Process PDG
Link].Process53, [Process PDG Link].Process54, [Process PDG
Link].Process55,
[Process PDG Link].Process56, [Process PDG Link].Process57, [Enter
Month-End
Date] AS [Month], [Process PDG Link].Process58, [Process PDG
Link].Process59,
[Process PDG Link].Process60
FROM [Process PDG Link]
WHERE ((([Enter Month-End Date]) Between #6/30/2008# And #5/31/2009#));

The Process PDG link is the table marked with the excel symbol.
It has updated with the correct monthly information.

Jeff Boyce said:
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
Click to expand...
Click to expand...
 
To make a long story short, and to help any future adventureres in Access,
my mistype error occured because I had formula errors pulled in to my linking
spreadsheet. When I replaced them with a "1" the error went away and I was
able to do my upload. I was misdirected, thinking it was the date field. Once
I corrected that; my upload still would not work with an "Uploading 0 rows
message".
My problem has been resolved, thanks for you help.
 
Back
Top