TransferSpreadsheet with Formula Issue

[Gary]

I'am trying to transfer an excel spreadsheet with one of the columns that has
a formula like =IF(AND(LEN(X3)=2,W3="R"),MID(X3,2,1),""). I have fill this
down on column AC of my excel sheet with the index changing of course.

I do the following DoCmd in Access 2000 & 2003:

DoCmd.TransferSpreadsheet acImport,
acSpreadsheetTypeExcel9, "USCNEWENTRY", TeamDirectory, True,
"A:AY"

I get an conversion error for all of the records that don't have a value
(when its executed). The Table has that field set to acccept "empty string"
too.

When I change the formula to ="" it's OK.

It generates a ErrorTable everytime the DoCmd is executed. I do have "On
Error Resume Next", but that doesn't work here because its one statemenet.


Can someone help to to stop the generation of the conversion error or table
???

Thank You,

G

 

[kc-mass]

I believe the problem is definitely on the Excel side. The formula as
written could not be computed.

Maybe its supposed to be something like:
=IIF(Len(X3)=2,"R","")

Reread what you have written carefully - I think you'll see the problems.

Regards

Kevin

 

[Douglas J. Steele]

Kevin: Excel uses IF, not IIF, so I don't think that's correct.

Gary: Exactly how are you putting the formula into your query? What's its
SQL?

 

[kc-mass]

Hi Doug
Sorry about the extra "F" - habit.
Read the logical test at the beginning of the statement:
"=IF(AND(LEN(X3)=2".
How could that be evaluated
Then in the midst of the function there is an assignment :"W3="R" "
It could be but is not preceded by an additional "IF"so it's not a test,
it's an assignment.

Kevin

 

[Douglas J. Steele]

=IF(AND(LEN(X3)=2,W3="R"),MID(X3,2,1),"")

looks valid to me.

In Access, the equivalent would be

=IIF(LEN(X3)=2 And W3="R",MID(X3,2,1),"").

 

[Douglas J. Steele]

Just occurred to me that if the problem is cells without values, you might
have to "cheat":

=IF(AND(LEN(X3 & "")=2,(W3 & "")="R"),MID(X3,2,1),"")

 

[Gary]

That didn't work, but if I did put a space when I don't have a value, it works.

I was luck in that I also following this transferSpreadsheet with a Append
Query, and I used a Trim() function. That was how I resolved the issue
here.


Thanks,