Excel2000: Is there a way to use name as parameter for ODBC query from Excel table

  • Thread starter Thread starter Arvi Laanemets
  • Start date Start date
A

Arvi Laanemets

Hi

I have an Excel workbook where dates, started from 01.01.2003 and up to some
year in future (2011 at moment), along with some other information (i.e.
workday/weekend/holiday, working hours etc.), are listed.

This table is read into another workbook, using ODBC query. But the problem
is, that I don't need all those future dates. I can't use any functions
exept MIN, MAX, SUM and COUNT in query from Excel files, but is there a way
to pass a name as parameter. P.e. I define a name:
CurrDat=TODAY()
and use it in query's WHERE condition, like
.... WHERE MyTable.Datefield Is Not Null And MyTable.Datefield<CurrDat+100...
(this syntax didn't wok of-course)
 
Arvi Laanemets said:
I can't use any functions
exept MIN, MAX, SUM and COUNT in query from Excel files
Click to expand...

Incorrect. I don't have a definitive list but AFAIK Jet supports most
of the VBA 'functions' (as distinct from 'methods') e.g. choosing some
at random from the Object Browser:

SELECT RGB(255,255,255) FROM [Sheet8$];
SELECT CHOOSE(3,1,2,3,4,5) FROM [Sheet8$]
SELECT DATEADD('yyyy',1,CDATE(ROUND(NOW(),1))) FROM [Sheet8$];

Your list seems to be sql aggregate functions (i.e. those you'd use
with a GROUP BY clause) of which there are others. Again, no
definitive list (MS Access documentation perhaps?) but I know they
include AVG and STDEV.
I define a name:
CurrDat=TODAY()
and use it in query's WHERE condition, like
... WHERE MyTable.Datefield Is Not Null And MyTable.Datefield<CurrDat+100...
(this syntax didn't wok of-course)
Click to expand...

Best to use the date/time from the server machine, so try something
like:

MyTable.Datefield<NOW()+100

Jamie.

--
 
Thanks. With NOW() it really worked.

(You learn as long you live!)


--
Arvi Laanemets
(When sending e-mail, use address arvil<At>tarkon.ee)


Jamie Collins said:
Arvi Laanemets said:
I can't use any functions
exept MIN, MAX, SUM and COUNT in query from Excel files
Click to expand...

Incorrect. I don't have a definitive list but AFAIK Jet supports most
of the VBA 'functions' (as distinct from 'methods') e.g. choosing some
at random from the Object Browser:

SELECT RGB(255,255,255) FROM [Sheet8$];
SELECT CHOOSE(3,1,2,3,4,5) FROM [Sheet8$]
SELECT DATEADD('yyyy',1,CDATE(ROUND(NOW(),1))) FROM [Sheet8$];

Your list seems to be sql aggregate functions (i.e. those you'd use
with a GROUP BY clause) of which there are others. Again, no
definitive list (MS Access documentation perhaps?) but I know they
include AVG and STDEV.
I define a name:
CurrDat=TODAY()
and use it in query's WHERE condition, like
... WHERE MyTable.Datefield Is Not Null And MyTable.Datefield<CurrDat+100...
(this syntax didn't wok of-course)
Click to expand...

Best to use the date/time from the server machine, so try something
like:

MyTable.Datefield<NOW()+100

Jamie.

--
Click to expand...
 
Back
Top