Importing from Excel

[kparadise]

Hello,

I have an excel sheet with two tabs. I also have an access database.

Excel [Tab 1] is a listing of manually entered items (ID, Type, Assigned To, Objective, Status, Name, etc.). Excel [Tab 2] simply is a reference table from [Tab 1]; and has some formulas related to [Tab 1]. [Tab 2] was built to be imported into my access file, and only holds the pertinent info we need (only 5 columns).

Two of the columns in [Tab 2] have an IF formula as such. =IF(Tab1!A5="","",Tab1!A5) and =IF(Tab1!D5="","",Tab1!D5). So, basically if there is no value in [Tab 1], it just shows as a blank in [Tab 2]. This works all well in excel with no issues; it is when I import this file into access is my issue.

When I run the SavedImport Command in Access...I am getting a Type Conversion Error on those rows where I have a blank in my [Tab 2]. One field is a date, on field is a text field. Does this make sense? How do I fix it.

 

[WaterGypsy]

Is it possible that Access defines the fields as required ? I'm no expert in Access by the way

 

[kparadise]

I have the table in Access defined as a Date/Time field.

 

[Joe4]

Yes, I think the issue is with your Date/Time field.

It is important to understand how Excel and Access store dates & times. They store them as numbers, specifically the number of days since 1/0/1900 (so time is a fraction, as it is "part" of one day).
I believe that "" will be interpretted as a Text entry, and you cannot have Text entries in Numeric or Date/Time fields.
So I think that is where you issue is occurring.

It should be easy to check/confirm this. Try importing a file that does not have any "blanks" in the data records for this field, and see if it works.

 

[kparadise]

Yea, that worked. So how would i fix this? I want to keep the formulas on my [Tab 2].

 

[Joe4]

Maybe update your formula so that it returns 0 (zero) instead of ""?

 

[james_lankford]

can't this
=IF(Tab1!D5="","",Tab1!D5)
just be rewritten as
=Tab1!D5