|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
OK, Same database, same spreadsheet, running on two different XP pro
machines, accessed from a network drive.
I have a macro that includes a transferspreadsheet command.
The spreadsheet is in excel 2000 format.
I am doing an import, with field names, with the range a1:n2001
The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box
saying that fields in two records were deleted. THIS IS FINE AND EXPECTED.
In access 2003, I do NOT get this dialog box, and the data does not
import correctly. I end up with data that looks mostly ok, but does not
process right.
I have a query with a single criteria;
WHERE (((CCDataDump.[MCC Code])<>"0000"));
If the data was pulled under 2002, this query works fine. About 6 in
300 records have "0000" in this field, so I get about 294 records.
If the data was pulled in via 2003, I get NO RESULTS, even though I can
look at the table, and see that almost NO records contain "0000" for
that field. IF I delete that criteria from the query, I get all the
records.
Please explain?
Phil
|
|
Hi Phil,
My first thought is that you should install the Office 2003 service
packs (at least SP1).
Also: if you're importing to a new table, are the field types identical
in the two versions? If not, read Help on "Initializing the Microsoft
Excel Driver" and look for differences in the relevant registry keys.
On Thu, 27 Jul 2006 14:10:59 -0700, Phil <phil_at_nhs[ at ]hotmail.com>
wrote:
[Quoted Text] >OK, Same database, same spreadsheet, running on two different XP pro
>machines, accessed from a network drive.
>I have a macro that includes a transferspreadsheet command.
>The spreadsheet is in excel 2000 format.
>I am doing an import, with field names, with the range a1:n2001
>
>The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box
>saying that fields in two records were deleted. THIS IS FINE AND EXPECTED.
>
>In access 2003, I do NOT get this dialog box, and the data does not
>import correctly. I end up with data that looks mostly ok, but does not
>process right.
>
>I have a query with a single criteria;
>WHERE (((CCDataDump.[MCC Code])<>"0000"));
>
>If the data was pulled under 2002, this query works fine. About 6 in
>300 records have "0000" in this field, so I get about 294 records.
>If the data was pulled in via 2003, I get NO RESULTS, even though I can
>look at the table, and see that almost NO records contain "0000" for
>that field. IF I delete that criteria from the query, I get all the
>records.
>
>Please explain?
>
>Phil
>
>
>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
I thought about updates, so ran all of them. The 2003 version is now
totally up to date, no difference.
The spreadsheet is being transfered into an existing table. However, I
will look into that help document anyway, perhaps something will spark.
John Nurick wrote:
[Quoted Text] > Hi Phil, > > My first thought is that you should install the Office 2003 service > packs (at least SP1). > > Also: if you're importing to a new table, are the field types identical > in the two versions? If not, read Help on "Initializing the Microsoft > Excel Driver" and look for differences in the relevant registry keys. > > > > On Thu, 27 Jul 2006 14:10:59 -0700, Phil <phil_at_nhs[ at ]hotmail.com> > wrote: > > >>OK, Same database, same spreadsheet, running on two different XP pro >>machines, accessed from a network drive. >>I have a macro that includes a transferspreadsheet command. >>The spreadsheet is in excel 2000 format. >>I am doing an import, with field names, with the range a1:n2001 >> >>The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box >>saying that fields in two records were deleted. THIS IS FINE AND EXPECTED. >> >>In access 2003, I do NOT get this dialog box, and the data does not >>import correctly. I end up with data that looks mostly ok, but does not >>process right. >> >>I have a query with a single criteria; >>WHERE (((CCDataDump.[MCC Code])<>"0000")); >> >>If the data was pulled under 2002, this query works fine. About 6 in >>300 records have "0000" in this field, so I get about 294 records. >>If the data was pulled in via 2003, I get NO RESULTS, even though I can >>look at the table, and see that almost NO records contain "0000" for >>that field. IF I delete that criteria from the query, I get all the >>records. >> >>Please explain? >> >>Phil >> >> >> > > > -- > John Nurick [Microsoft Access MVP] > > Please respond in the newgroup and not by email. |
|
|
[Quoted Text] > WHERE (((CCDataDump.[MCC Code])<>"0000"));
> If the data was pulled in via 2003, I get NO RESULTS,
That tells us that there has been a failure on that column,
and that the value of MCC Code is Null or Error, but
how or why we do not know.
If you are fully patched, the call from Jet to the Excel
IISAM has been replaced with a call to an Access DLL,
(dunno how that works if you have both installed on the
same PC) . But I've got no useful suggestions.
(david)
"Phil" <phil_at_nhs[ at ]hotmail.com> wrote in message
news:uh6lpEcsGHA.2376[ at ]TK2MSFTNGP04.phx.gbl...
> OK, Same database, same spreadsheet, running on two different XP pro
> machines, accessed from a network drive.
> I have a macro that includes a transferspreadsheet command.
> The spreadsheet is in excel 2000 format.
> I am doing an import, with field names, with the range a1:n2001
>
> The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box
> saying that fields in two records were deleted. THIS IS FINE AND
EXPECTED.
>
> In access 2003, I do NOT get this dialog box, and the data does not
> import correctly. I end up with data that looks mostly ok, but does not
> process right.
>
> I have a query with a single criteria;
> WHERE (((CCDataDump.[MCC Code])<>"0000"));
>
> If the data was pulled under 2002, this query works fine. About 6 in
> 300 records have "0000" in this field, so I get about 294 records.
> If the data was pulled in via 2003, I get NO RESULTS, even though I can
> look at the table, and see that almost NO records contain "0000" for
> that field. IF I delete that criteria from the query, I get all the
> records.
>
> Please explain?
>
> Phil
>
>
>
>
|
|
Here is the probelm though. If it is NULL, I should still get results,
because Null <>"0000".
IF it is Error, I would see that when I open the table manually.
You say something that might hold the key, but i am not sure i
understand it.
Are you saying that a 2002 call for data to this spreadsheet, is using
something from an excell library, but a fully patched 2003 copy of
access would be using an Access routine instead?
These installations are installed on two different machines.
david[ at ]epsomdotcomdotau wrote:
[Quoted Text] >>WHERE (((CCDataDump.[MCC Code])<>"0000")); >>If the data was pulled in via 2003, I get NO RESULTS, > > > That tells us that there has been a failure on that column, > and that the value of MCC Code is Null or Error, but > how or why we do not know. > > If you are fully patched, the call from Jet to the Excel > IISAM has been replaced with a call to an Access DLL, > (dunno how that works if you have both installed on the > same PC) . But I've got no useful suggestions. > > (david) > > > > > > "Phil" <phil_at_nhs[ at ]hotmail.com> wrote in message > news:uh6lpEcsGHA.2376[ at ]TK2MSFTNGP04.phx.gbl... > >>OK, Same database, same spreadsheet, running on two different XP pro >>machines, accessed from a network drive. >>I have a macro that includes a transferspreadsheet command. >>The spreadsheet is in excel 2000 format. >>I am doing an import, with field names, with the range a1:n2001 >> >>The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box >>saying that fields in two records were deleted. THIS IS FINE AND > > EXPECTED. > >>In access 2003, I do NOT get this dialog box, and the data does not >>import correctly. I end up with data that looks mostly ok, but does not >>process right. >> >>I have a query with a single criteria; >>WHERE (((CCDataDump.[MCC Code])<>"0000")); >> >>If the data was pulled under 2002, this query works fine. About 6 in >>300 records have "0000" in this field, so I get about 294 records. >>If the data was pulled in via 2003, I get NO RESULTS, even though I can >>look at the table, and see that almost NO records contain "0000" for >>that field. IF I delete that criteria from the query, I get all the >>records. >> >>Please explain? >> >>Phil >> >> >> >> > > > |
|
No, Null is not <> to "0000". Null is not equal to anything,
but still not 'not equal' to anything. Null is a third state, not
equal, not unequal.
This sometimes causes problems in import from Excel, because
some versions of Access import blank cells as Null, and some
import blank cells as "" empty strings - or that may be the way
the import is configured. Whatever, it doesn't appear to be your
problem.
More likely to be your problem, "0000" has come in as 0,
causing a type comparison error, causing the criteria to fail,
returning 'error' for the criteria instead of 'true', and not including
any records.
This might be a configuration problem. causing the value to come
in as text on one PC, and as a number on the other PC.
For many years, Jet used an Installable ISAM called MSEXCL35
or MSEXCL40.dll for import, export, update of excel spreadsheets.
Following a patent dispute, MS released an Access patch for 2002
and 2003, which replaced the reference to MSEXCL with a
reference to an Access DLL, but did not provide any documentation,
(this seems to be the pattern for Access now) so it's anybodies
guess how Access and Jet work with Excel. Some of my stuff
just stopped working when I applied the patch, but I've got multiple
versions installed, and , lacking any documentation, I've got no idea
if the failure was typical or atypical.
(david)
"Phil" <phil_at_nhs[ at ]hotmail.com> wrote in message
news:OsFYSlNtGHA.4748[ at ]TK2MSFTNGP03.phx.gbl...
[Quoted Text] > Here is the probelm though. If it is NULL, I should still get results, > because Null <>"0000". > IF it is Error, I would see that when I open the table manually. > > You say something that might hold the key, but i am not sure i > understand it. > > Are you saying that a 2002 call for data to this spreadsheet, is using > something from an excell library, but a fully patched 2003 copy of > access would be using an Access routine instead? > > These installations are installed on two different machines. > > > > david[ at ]epsomdotcomdotau wrote: > >>WHERE (((CCDataDump.[MCC Code])<>"0000")); > >>If the data was pulled in via 2003, I get NO RESULTS, > > > > > > That tells us that there has been a failure on that column, > > and that the value of MCC Code is Null or Error, but > > how or why we do not know. > > > > If you are fully patched, the call from Jet to the Excel > > IISAM has been replaced with a call to an Access DLL, > > (dunno how that works if you have both installed on the > > same PC) . But I've got no useful suggestions. > > > > (david) > > > > > > > > > > > > "Phil" <phil_at_nhs[ at ]hotmail.com> wrote in message > > news:uh6lpEcsGHA.2376[ at ]TK2MSFTNGP04.phx.gbl... > > > >>OK, Same database, same spreadsheet, running on two different XP pro > >>machines, accessed from a network drive. > >>I have a macro that includes a transferspreadsheet command. > >>The spreadsheet is in excel 2000 format. > >>I am doing an import, with field names, with the range a1:n2001 > >> > >>The transferspreadsheet ommand, in Access 2002 sp2, I get a dialog box > >>saying that fields in two records were deleted. THIS IS FINE AND > > > > EXPECTED. > > > >>In access 2003, I do NOT get this dialog box, and the data does not > >>import correctly. I end up with data that looks mostly ok, but does not > >>process right. > >> > >>I have a query with a single criteria; > >>WHERE (((CCDataDump.[MCC Code])<>"0000")); > >> > >>If the data was pulled under 2002, this query works fine. About 6 in > >>300 records have "0000" in this field, so I get about 294 records. > >>If the data was pulled in via 2003, I get NO RESULTS, even though I can > >>look at the table, and see that almost NO records contain "0000" for > >>that field. IF I delete that criteria from the query, I get all the > >>records. > >> > >>Please explain? > >> > >>Phil > >> > >> > >> > >> > > > > > >
|
|
|