[AccessD] Problem with Transfer Spreadsheet

Darryl:

I'll give it a try but wonder why it works here and not there. 

I suppose I have to also replace the NZ([Total Cost]) with IIf
(Isnull([Total Cost],0,[Total Cost]) then?

Rocky


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Darryl Collins
Sent: April 26, 2013 9:47 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Problem with Transfer Spreadsheet

Rocky,

I think the problem is the NZ function in the query. It is only recognised
within Access.  Replace with NZ with a "Is Null" syntax instead.

That should fix your issue.

regards
Darryl.

________________________________________
From: accessd-bounces at databaseadvisors.com
[accessd-bounces at databaseadvisors.com] on behalf of Rocky Smolin
[rockysmolin at bchacc.com]
Sent: Saturday, 27 April 2013 1:58 AM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] Problem with Transfer Spreadsheet

Dear List:

I have a user running my app (2003 mde) on two machines - one is a W7 PC
with Access 2010.  The other is a Mac running Parallels and is using the
run-time of my app created with Wise/Sagekey.

I added a gross margin spreadsheet export (the gross margin report works
fine) and he gets the same error on both machines:

"The expression OnClick you entered as the event property setting produced
the following error: Function is not available in expressions in query
expression "Format(IIf(Nz([SellingPrice])=0,"",([SellingPrice]-Nz([Total
Cost]))/[SellingPrice]),"Percent")'

* The expression may not result in the name of a macro, the name of a user
defined function, or [Event Procedure].
* There may have been an error evaluating the function, event, or macro.

I'm guessing the line of code it's barfing on is:

    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9,
"qryGrossMarginExport", strFrontEndPath & "GrossMarginExport.xls", True

If I could get it to fail here, I'm sure I could fix it.  But no cigar.

Works like a champ on my 2003 machine and my 2010 box (but that box also has
Office 2003 installed).  I also tried it on a VM with Office 2007 installed.
But I do not have a VM with 2010 installed.

Nevertheless, you would think the run time on the Mac would work.

So I'm left with speculating what the problem could be and trying various
solution without being able to test them here.

I thought about deleting the optional argument acSpreadsheetTypeExcel9, but
help says it defaults to 8 or 9 anyway. As an added bonus, this is an app
that has to run on target machines that may have 2003, 2007, 2010 or 2013
loaded.

Any ideas on what might be going wrong here or what to look at next?

MTIA

Rocky Smolin
Beach Access Software
858-259-4334
www.bchacc.com <http://www.bchacc.com/>
www.e-z-mrp.com <http://www.e-z-mrp.com/>
Skype: rocky.smolin



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com