Hi,

Firstly, let me say I don't know how to code. My question is this.

I'm using Excel to query a Lotus Notes Database. I use Microsoft Query to do
it and it works just fine when simply retrieving data.

What I want to do is simply return data based on dates. I want records to be
returned between the current system date and the current system date minus 8
days.

E.g. Return all records between
(1) current system date 04/02/2005 and
(2) current system date 04/02/2005 MINUS 8 days
Expected result would be all records that have a date from and including
04/02/2005 and 27/01/2005

The SQL code that shows up in Microsoft query using the Wizard is this:
SELECT Copy_Of_Closed_."_8"
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

This is without the date filtering. As for the " " around the 8 (i.e.
Copy_Of_Closed_."_8"), I'm not sure why it does that, but it works!

I'd really appreciate any help with this and this little tiny piece of code
is stopping me from doing something really really simple!

Many thanks in advance.

Lena Gallagher

The solution is actually easier than you imagine, and should not require
coding either in VBA or SQL!

Use two cells in your worksheet to calculate the start and end date of your
query: e.g.
= Today()
= Today() - 8
Now, go into Microsoft Query to edit your query. Show the criteria grid and
drag your date field onto it. Set up a criteria that says something like
this:
Between [StartDate] and [EndDate]
The square braces indicate these are parameters.
Return to Excel - Query will ask for the dates, for now just type something
in.
When back in Excel, right-click on the query results list and choose
"Parameters.." You should see "StartDate" and "EndDate" in a list, and when
you highlight either of them you can specify how you will supply these
numbers. Choose "Get the parameter from the following cell:" and point to
the cell where you put the calculated date.

From then on, when you run your query it will take the calculated dates,
pass them to MSQuery, and return the proper data.

"Raminhos" wrote:

Hi,

Firstly, let me say I don't know how to code. My question is this.

I'm using Excel to query a Lotus Notes Database. I use Microsoft Query to do
it and it works just fine when simply retrieving data.

What I want to do is simply return data based on dates. I want records to be
returned between the current system date and the current system date minus 8
days.

E.g. Return all records between
(1) current system date 04/02/2005 and
(2) current system date 04/02/2005 MINUS 8 days
Expected result would be all records that have a date from and including
04/02/2005 and 27/01/2005

The SQL code that shows up in Microsoft query using the Wizard is this:
SELECT Copy_Of_Closed_."_8"
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

This is without the date filtering. As for the " " around the 8 (i.e.
Copy_Of_Closed_."_8"), I'm not sure why it does that, but it works!

I'd really appreciate any help with this and this little tiny piece of code
is stopping me from doing something really really simple!

Many thanks in advance.

Lena Gallagher

Hi there,

Thanks so much for your answer, BUT (and of course there had to be one),
I've tried it out and whilst I'm still in MS query and it asks me for the
date I get the error "SQL data type out of range". I've confirmed that the
date colums are actually date type fields. I've even tried to use the
To_Date() function but got no where with that. MS query keeps telling me I'm
missing an AND or in somes cases a FROM which I don't understand because I'm
not typing in the code. I'm simply using the Criteria fields to make
selections and dragging and dropping the columns I need. The only time I've
gone into the code is whan I tried the To_Date() but even that was simply
adding the function to the columns in the SELECT part of the query. And still
MS query moans about an AND or FROM missing.

Here's the code straight from MS Query:

SELECT Copy_Of_Closed_."_8", Copy_Of_Closed_."_2",
Copy_Of_Closed_.BriefDesc, Copy_Of_Closed_.Solution, Copy_Of_Closed_.Module,
Copy_Of_Closed_.Version, Copy_Of_Closed_.Category, Copy_Of_Closed_."_4",
Copy_Of_Closed_.Ref, Copy_Of_Closed_.Caller, Copy_Of_Closed_.Channel
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

I need Copy_Of_Closed_._2 to be between the current date and the current
date - 8 days.

Help!






"K Dales" wrote:

The solution is actually easier than you imagine, and should not require
coding either in VBA or SQL!

Use two cells in your worksheet to calculate the start and end date of your
query: e.g.
= Today()
= Today() - 8
Now, go into Microsoft Query to edit your query. Show the criteria grid and
drag your date field onto it. Set up a criteria that says something like
this:
Between [StartDate] and [EndDate]
The square braces indicate these are parameters.
Return to Excel - Query will ask for the dates, for now just type something
in.
When back in Excel, right-click on the query results list and choose
"Parameters.." You should see "StartDate" and "EndDate" in a list, and when
you highlight either of them you can specify how you will supply these
numbers. Choose "Get the parameter from the following cell:" and point to
the cell where you put the calculated date.

From then on, when you run your query it will take the calculated dates,
pass them to MSQuery, and return the proper data.

"Raminhos" wrote:

Hi,

Firstly, let me say I don't know how to code. My question is this.

I'm using Excel to query a Lotus Notes Database. I use Microsoft Query to do
it and it works just fine when simply retrieving data.

What I want to do is simply return data based on dates. I want records to be
returned between the current system date and the current system date minus 8
days.

E.g. Return all records between
(1) current system date 04/02/2005 and
(2) current system date 04/02/2005 MINUS 8 days
Expected result would be all records that have a date from and including
04/02/2005 and 27/01/2005

The SQL code that shows up in Microsoft query using the Wizard is this:
SELECT Copy_Of_Closed_."_8"
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

This is without the date filtering. As for the " " around the 8 (i.e.
Copy_Of_Closed_."_8"), I'm not sure why it does that, but it works!

I'd really appreciate any help with this and this little tiny piece of code
is stopping me from doing something really really simple!

Many thanks in advance.

Lena Gallagher

Might be tough to solve because it might be specifics of the SQL syntax used
by Lotus Notes, which I am not familiar with. One question does strike me,
though - why the quotes, e.g. Copy_Of_Closed_."_8"? This could be throwing
off the SQL interpreter since I am not aware of any situation where quotes
would be accepted in a field name. And is the "_8" coming from the 8 days?
"Normal" SQL syntax for your condition would be:
SELECT Copy_Of_Closed_._2, .....
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_._2 Between ? and ?) AND ...
This is assuming you have used the parameters.

Try omitting the quotes, and editing the SQL to look like the above for your
date range; to simplify you can also forget the Copy_Of_Closed_. before
everything and just make it:

SELECT _8, _2, BriefDesc, SolutionModule, Version, Category, _4, Ref,
Caller, Channel
FROM Copy_Of_Closed_
WHERE (Priority='0 - Critical') AND (CallType='P') AND (Status='Closed') AND
(_2 Between ? And ?)

If the above still bombs, it is probably due to a problem with the
interpreter understanding ield names starting with an underscore - I would
need to see documentation on how Lotus Notes interprets SQL to try to figure
that out!

"Raminhos" wrote:

Hi there,

Thanks so much for your answer, BUT (and of course there had to be one),
I've tried it out and whilst I'm still in MS query and it asks me for the
date I get the error "SQL data type out of range". I've confirmed that the
date colums are actually date type fields. I've even tried to use the
To_Date() function but got no where with that. MS query keeps telling me I'm
missing an AND or in somes cases a FROM which I don't understand because I'm
not typing in the code. I'm simply using the Criteria fields to make
selections and dragging and dropping the columns I need. The only time I've
gone into the code is whan I tried the To_Date() but even that was simply
adding the function to the columns in the SELECT part of the query. And still
MS query moans about an AND or FROM missing.

Here's the code straight from MS Query:

SELECT Copy_Of_Closed_."_8", Copy_Of_Closed_."_2",
Copy_Of_Closed_.BriefDesc, Copy_Of_Closed_.Solution, Copy_Of_Closed_.Module,
Copy_Of_Closed_.Version, Copy_Of_Closed_.Category, Copy_Of_Closed_."_4",
Copy_Of_Closed_.Ref, Copy_Of_Closed_.Caller, Copy_Of_Closed_.Channel
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

I need Copy_Of_Closed_._2 to be between the current date and the current
date - 8 days.

Help!






"K Dales" wrote:

The solution is actually easier than you imagine, and should not require
coding either in VBA or SQL!

Use two cells in your worksheet to calculate the start and end date of your
query: e.g.
= Today()
= Today() - 8
Now, go into Microsoft Query to edit your query. Show the criteria grid and
drag your date field onto it. Set up a criteria that says something like
this:
Between [StartDate] and [EndDate]
The square braces indicate these are parameters.
Return to Excel - Query will ask for the dates, for now just type something
in.
When back in Excel, right-click on the query results list and choose
"Parameters.." You should see "StartDate" and "EndDate" in a list, and when
you highlight either of them you can specify how you will supply these
numbers. Choose "Get the parameter from the following cell:" and point to
the cell where you put the calculated date.

From then on, when you run your query it will take the calculated dates,
pass them to MSQuery, and return the proper data.

"Raminhos" wrote:

Hi,

Firstly, let me say I don't know how to code. My question is this.

I'm using Excel to query a Lotus Notes Database. I use Microsoft Query to do
it and it works just fine when simply retrieving data.

What I want to do is simply return data based on dates. I want records to be
returned between the current system date and the current system date minus 8
days.

E.g. Return all records between
(1) current system date 04/02/2005 and
(2) current system date 04/02/2005 MINUS 8 days
Expected result would be all records that have a date from and including
04/02/2005 and 27/01/2005

The SQL code that shows up in Microsoft query using the Wizard is this:
SELECT Copy_Of_Closed_."_8"
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

This is without the date filtering. As for the " " around the 8 (i.e.
Copy_Of_Closed_."_8"), I'm not sure why it does that, but it works!

I'd really appreciate any help with this and this little tiny piece of code
is stopping me from doing something really really simple!

Many thanks in advance.

Lena Gallagher

K Dales wrote:
One question does strike me,
though - why the quotes, e.g. Copy_Of_Closed_."_8"? This could be
throwing
off the SQL interpreter since I am not aware of any situation where
quotes
would be accepted in a field name.

It's in the standards! According to the Mimer SQL-92 Validator
(http://developer.mimer.com/validator...r92/index.tml), the
following is entry level SQL-92:

SELECT * FROM "Name has spaces";

You are probably more used to the proprietary Microsoft square brackets
syntax. Try this in the Validator:

SELECT * FROM [Name has spaces];

and it shows syntax errors.

Jamie.

--

Hi,

Thanks again for helping with this. To be honest, I've decided to just pull
all the data into Excel and manipulate it from there. I've spent to much time
on this, not to mention your time too!

As for the quotes thing, I have no idea why they kept being put in. I have
tried removing them but then the query fails. The _8 and _2 are date fields.
There's not been a single time when I've gone into the SQL code and changed
something and it worked. Not even when I've copied a simple piece of code
from Notepad and simply pasted it in. Perhaps the MS Query I'm using is just
p00! () Then again, the problem may just exist between my chair and keyboard!

Again, thank you for your time on this. It is appreciated.

Have a wonderful day.


"K Dales" wrote:

Might be tough to solve because it might be specifics of the SQL syntax used
by Lotus Notes, which I am not familiar with. One question does strike me,
though - why the quotes, e.g. Copy_Of_Closed_."_8"? This could be throwing
off the SQL interpreter since I am not aware of any situation where quotes
would be accepted in a field name. And is the "_8" coming from the 8 days?
"Normal" SQL syntax for your condition would be:
SELECT Copy_Of_Closed_._2, .....
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_._2 Between ? and ?) AND ...
This is assuming you have used the parameters.

Try omitting the quotes, and editing the SQL to look like the above for your
date range; to simplify you can also forget the Copy_Of_Closed_. before
everything and just make it:

SELECT _8, _2, BriefDesc, SolutionModule, Version, Category, _4, Ref,
Caller, Channel
FROM Copy_Of_Closed_
WHERE (Priority='0 - Critical') AND (CallType='P') AND (Status='Closed') AND
(_2 Between ? And ?)

If the above still bombs, it is probably due to a problem with the
interpreter understanding ield names starting with an underscore - I would
need to see documentation on how Lotus Notes interprets SQL to try to figure
that out!

"Raminhos" wrote:

Hi there,

Thanks so much for your answer, BUT (and of course there had to be one),
I've tried it out and whilst I'm still in MS query and it asks me for the
date I get the error "SQL data type out of range". I've confirmed that the
date colums are actually date type fields. I've even tried to use the
To_Date() function but got no where with that. MS query keeps telling me I'm
missing an AND or in somes cases a FROM which I don't understand because I'm
not typing in the code. I'm simply using the Criteria fields to make
selections and dragging and dropping the columns I need. The only time I've
gone into the code is whan I tried the To_Date() but even that was simply
adding the function to the columns in the SELECT part of the query. And still
MS query moans about an AND or FROM missing.

Here's the code straight from MS Query:

SELECT Copy_Of_Closed_."_8", Copy_Of_Closed_."_2",
Copy_Of_Closed_.BriefDesc, Copy_Of_Closed_.Solution, Copy_Of_Closed_.Module,
Copy_Of_Closed_.Version, Copy_Of_Closed_.Category, Copy_Of_Closed_."_4",
Copy_Of_Closed_.Ref, Copy_Of_Closed_.Caller, Copy_Of_Closed_.Channel
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

I need Copy_Of_Closed_._2 to be between the current date and the current
date - 8 days.

Help!






"K Dales" wrote:

The solution is actually easier than you imagine, and should not require
coding either in VBA or SQL!

Use two cells in your worksheet to calculate the start and end date of your
query: e.g.
= Today()
= Today() - 8
Now, go into Microsoft Query to edit your query. Show the criteria grid and
drag your date field onto it. Set up a criteria that says something like
this:
Between [StartDate] and [EndDate]
The square braces indicate these are parameters.
Return to Excel - Query will ask for the dates, for now just type something
in.
When back in Excel, right-click on the query results list and choose
"Parameters.." You should see "StartDate" and "EndDate" in a list, and when
you highlight either of them you can specify how you will supply these
numbers. Choose "Get the parameter from the following cell:" and point to
the cell where you put the calculated date.

From then on, when you run your query it will take the calculated dates,
pass them to MSQuery, and return the proper data.

"Raminhos" wrote:

Hi,

Firstly, let me say I don't know how to code. My question is this.

I'm using Excel to query a Lotus Notes Database. I use Microsoft Query to do
it and it works just fine when simply retrieving data.

What I want to do is simply return data based on dates. I want records to be
returned between the current system date and the current system date minus 8
days.

E.g. Return all records between
(1) current system date 04/02/2005 and
(2) current system date 04/02/2005 MINUS 8 days
Expected result would be all records that have a date from and including
04/02/2005 and 27/01/2005

The SQL code that shows up in Microsoft query using the Wizard is this:
SELECT Copy_Of_Closed_."_8"
FROM Copy_Of_Closed_ Copy_Of_Closed_
WHERE (Copy_Of_Closed_.Priority='0 - Critical') AND
(Copy_Of_Closed_.CallType='P') AND (Copy_Of_Closed_.Status='Closed')

This is without the date filtering. As for the " " around the 8 (i.e.
Copy_Of_Closed_."_8"), I'm not sure why it does that, but it works!

I'd really appreciate any help with this and this little tiny piece of code
is stopping me from doing something really really simple!

Many thanks in advance.

Lena Gallagher

Raminhos wrote:
Hi,

Thanks again for helping with this. To be honest, I've decided to
just pull
all the data into Excel and manipulate it from there.

Shame. I was just about to suggest something like this:

SELECT Copy_Of_Closed_."_2", Copy_Of_Closed_."_4",
Copy_Of_Closed_."_8", Copy_Of_Closed_.BriefDesc,
Copy_Of_Closed_.Caller, Copy_Of_Closed_.Category,
Copy_Of_Closed_.Channel, Copy_Of_Closed_.Module, Copy_Of_Closed_.Ref,
Copy_Of_Closed_.Solution, Copy_Of_Closed_.Version, Copy_Of_Closed_."_2"
FROM Copy_Of_Closed_
WHERE Copy_Of_Closed_."_2" BETWEEN (CURRENT_TIMESTAMP-8) AND
CURRENT_TIMESTAMP;

CURRENT_TIMESTAMP is the ANSI standard way of getting the current
date/time so has the best chance of being understood by your DBMS. Some
don't adhere to the standards and have their own proprietary syntax
e.g. with MS Access/Jet you one must use NOW().

Jamie.

--