Linking to Excel spreadsheet

[Thedda]

Having problems with "time mismatch in JOIN expression"
I need to link to an excel spreadsheet. I have a column of part numbers that are set as text. When I link this sheet to access the field data type comes in as a "number". I can not change this field since it is a linked table. I have to have this field as a text field because I am joining this table with another table that is a text field. Need help!!!

 

[yellow]

Can you change the field in the all tables to a number?

If not you could have a third table. Then append the Excel spreadsheet to this table and have the field set as text.

 

[Thedda]

No I have to leave the fields as text due to the second table is also linked and can not be changed. I can not use a third table due to this has to be automated. I have worked on this for a while now and due to the automation everything is getting hung up. Do you know why Access brings the field in as a number???? Is there a default somewhere????

 

[yellow]

In my experience Access and Excel don't get on that well because they both think they know best! But they may just be me.

If it needs to be automated, then there is no reason why you can't use a third table. Write the query then have a form with a command button on it.

Under this command button put something like:

DoCmd.CopyObject "", "MyThirdTable", acTable, MyThirdTable"
docmd.openquery "AppendMyData"

 

[The_Doc_Man]

The other possibility is that time isn't what you think it is.

Time in an Excel spreadsheet is text unless you used a time function of some sort. I.e. if you enter 09/11/01, you don't NECESSARILY get a time/date value. This is because you can pre-declare columns to be text format. You can enclose dates in quotes. You can do a lot of things that defeat any nice automated things you want done. This is because data types have less importance (not NO importance) to Excel. In Excel, a non-empty cell is a number or text or "other" - where OTHER subsumes lots of different options.

Time in an Access database is stored as a Double (64 bit float) for which the integer part represents days since a reference date and the fraction is the fraction of the day since midnight. Times are very important to Access because it is counted as a native data type.

When you import that spreadsheet via linking, the linking wizard might just take one look at the slashes and declare the time column from your spreadsheet as text - because "/" is not a digit.

So how do you make the query work? Make them both have the same format. You can do one of two things:

1. Make the Excel column be interpreted as a date:

CDat(Nz([XLDate],"1/1/00")) rather than just [XLDate]

(You can pick another default date than 1/1/00 if you like.)

2. Make the Access column be interpreted as text:

Format([ACDate],"short date")

Use one or the other of these in the query. (Don't do both.)

 

[Thedda]

Now I am thoroughly confused.........
The field is not a date field, it is a part number field with seven digits no slash or dash just seven numbers. Is Access thinking this is a Date field????

 

[Pat Hartman]

No, Access thinks it is a number. If your spreadsheet is relatively stable, you can make a change to a single cell that will make Access interpret the part number as text. In the first partnumber cell, add a single quote to the left of the first number. This will be invisible but tells Excel that this data is text rather than numeric.