(Type mismatch in expression) Update Query - Linked Excel

[Robbie]

I have a product table that needs to have one of its fields updated. I have
an excel file that has the latest info and I've created an update query to
the linked table to update. However I get a
Type mismatch in expression


I have joined them by a common field (product number) but I think the
problem might be that the excel file has records the mdb does not. Does this
sound like it might cause the mismatch?

If so, how can I write the query so it ignores products that aren't common
between the two? I just used the design view wizard to make the query it's
pretty basic.

UPDATE Products INNER JOIN [csv-products] ON Products.ShortISBN13 =
[csv-products].ShortISBN SET Products.CaseQty = [csv-products].[CaseQTY]
WHERE (((Products.CaseQty)>1) AND (([csv-products].PublicationStatus) Is
Null));

I have another app that updates the db with new products but I can't do that
yet because of other issues.

 

[Golfinray]

Type mismatch is almost always because one field in the table is set to text,
one number, or one date, one text, etc.

 

[Robbie]

Okay I see it now. The linked excel field that I am joining is set to Number
while the field in the mdb is Text.
I was hoping to avoid having to do anything to the file. Ideally I'd just
like to replace the excel file with a new version every time it is available
and run the query.

I'd rather not have to open the excel file, change the field to text and
then resave it because my data gets corrupted.

For example, the field ShortISBN is a 13 digit number. If I open it up in
excel and change the formatting from general to text and then save it, all
of the fields appear like this in access
9.7808423002e+012

Therefore making it impossible for me to create any join.

Type mismatch is almost always because one field in the table is set to
text,
one number, or one date, one text, etc.

I have a product table that needs to have one of its fields updated. I
have
an excel file that has the latest info and I've created an update query
to
the linked table to update. However I get a
Type mismatch in expression


I have joined them by a common field (product number) but I think the
problem might be that the excel file has records the mdb does not. Does
this
sound like it might cause the mismatch?

If so, how can I write the query so it ignores products that aren't
common
between the two? I just used the design view wizard to make the query
it's
pretty basic.

UPDATE Products INNER JOIN [csv-products] ON Products.ShortISBN13 =
[csv-products].ShortISBN SET Products.CaseQty = [csv-products].[CaseQTY]
WHERE (((Products.CaseQty)>1) AND (([csv-products].PublicationStatus) Is
Null));

I have another app that updates the db with new products but I can't do
that
yet because of other issues.

 

[Paul Shapiro]

The excel data isn't corrupted, it's being displayed in exponential
notation. What happens if you specify a fixed format with zero decimals in
Excel, and then specify the column should be text? Make sure the column is
wide enough to display the number, although I doubt that affects what Access
sees.

If you can't get the linked Excel file to work correctly, you can import the
data to a temporary table and clean it up before running your update. All of
that can be automated with code if this is a recurring task.

Okay I see it now. The linked excel field that I am joining is set to
Number while the field in the mdb is Text.
I was hoping to avoid having to do anything to the file. Ideally I'd just
like to replace the excel file with a new version every time it is
available and run the query.

I'd rather not have to open the excel file, change the field to text and
then resave it because my data gets corrupted.

For example, the field ShortISBN is a 13 digit number. If I open it up in
excel and change the formatting from general to text and then save it, all
of the fields appear like this in access
9.7808423002e+012

Therefore making it impossible for me to create any join.

Type mismatch is almost always because one field in the table is set to
text,
one number, or one date, one text, etc.

I have a product table that needs to have one of its fields updated. I
have
an excel file that has the latest info and I've created an update query
to
the linked table to update. However I get a
Type mismatch in expression


I have joined them by a common field (product number) but I think the
problem might be that the excel file has records the mdb does not. Does
this
sound like it might cause the mismatch?

If so, how can I write the query so it ignores products that aren't
common
between the two? I just used the design view wizard to make the query
it's
pretty basic.

UPDATE Products INNER JOIN [csv-products] ON Products.ShortISBN13 =
[csv-products].ShortISBN SET Products.CaseQty = [csv-products].[CaseQTY]
WHERE (((Products.CaseQty)>1) AND (([csv-products].PublicationStatus) Is
Null));

I have another app that updates the db with new products but I can't do
that
yet because of other issues.

 

[Robbie]

Even If I increase the width in excel it doesn't read as the actual number.
But like I told you when I linked the table in access it still displayed it
like that. When the field is too narrow it shows a bunch of pound signs.
Increasing the width will show text or numbers, but when the number is
displaying in exponential it doesn't matter if you increase the width.

Is it possible to turn off the exponential feature? I come across this issue
a lot in other documents that contain tracking numbers that are sometimes up
to 30 numbers long.

The excel data isn't corrupted, it's being displayed in exponential
notation. What happens if you specify a fixed format with zero decimals in
Excel, and then specify the column should be text? Make sure the column is
wide enough to display the number, although I doubt that affects what
Access sees.

If you can't get the linked Excel file to work correctly, you can import
the data to a temporary table and clean it up before running your update.
All of that can be automated with code if this is a recurring task.

Okay I see it now. The linked excel field that I am joining is set to
Number while the field in the mdb is Text.
I was hoping to avoid having to do anything to the file. Ideally I'd just
like to replace the excel file with a new version every time it is
available and run the query.

I'd rather not have to open the excel file, change the field to text and
then resave it because my data gets corrupted.

For example, the field ShortISBN is a 13 digit number. If I open it up in
excel and change the formatting from general to text and then save it,
all of the fields appear like this in access
9.7808423002e+012

Therefore making it impossible for me to create any join.

Type mismatch is almost always because one field in the table is set to
text,
one number, or one date, one text, etc.

:

I have a product table that needs to have one of its fields updated. I
have
an excel file that has the latest info and I've created an update query
to
the linked table to update. However I get a
Type mismatch in expression


I have joined them by a common field (product number) but I think the
problem might be that the excel file has records the mdb does not. Does
this
sound like it might cause the mismatch?

If so, how can I write the query so it ignores products that aren't
common
between the two? I just used the design view wizard to make the query
it's
pretty basic.

UPDATE Products INNER JOIN [csv-products] ON Products.ShortISBN13 =
[csv-products].ShortISBN SET Products.CaseQty =
[csv-products].[CaseQTY]
WHERE (((Products.CaseQty)>1) AND (([csv-products].PublicationStatus)
Is
Null));

I have another app that updates the db with new products but I can't do
that
yet because of other issues.