Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel

Onion - the missing ingredient for Sage Line 50 / Sage Instant accounts packs in Excel
Full audit trails to underlying transactions from P&Ls, Balance Sheets, graphs, PivotTables, and departmental TBs in a stand-alone Excel file. Aged Debtors and Aged Creditor files too. Free 30 day trials. Download today at www.onionrs.co.uk

Friday 1 October 2010

Functions available with Text File ODBC driver

I can't find documentation anywhere.  The functions available seem to be mainly VB Scipt functions. This is my work in progress list and some comments thereon.  I refer to VB Script documentation for questions on syntax.

http://msdn.microsoft.com/en-us/library/3ca8tfek(VS.85).aspx


Use [ ] or `` with field names.  (` is the character on the key to the left of the number 1 at the top of the keyboard)

isnull() returns true or false. It is a good idea to use iif(isnull(<field>),0,<field>) to set a value for null entries if you might ever want to perform aggregation on the field.

mid()
left()
right()
strcomp()


Concatenate using + e.g. 'Dropped'+' '+'Pennies'

Use single quotes (not double quotes) for text.

year('2009-12-31') = 2009
month('2009-12-31') = 12
day('2009-12-31') = 31

dateadd('yyyy',1,`Date`)
dateserial()
datevalue()
now()
weekday()

isdate()
isnumeric() (watch for nulls)
isempty()

len()
lcase()
ucase()
trim()
ltrim()
rtrim()
string()

chr()
asc()
instr()

rnd()
str() converts number to string (leaves space for minus)
space()

sgn()
abs()

round()
CBool()
CByte()
CCur()
CDate()
CDbl()
CInt()
CLng()
CSng()
CStr()

timer()
typename()

No comments:

Post a Comment