Another Query Modification Question

C

carl

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");


Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.


Thanks in advance.
 
J

John W. Vinson

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");


Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.


Thanks in advance.
Click to expand...

Assuming that the date field is of Text datatype (as appears to be the case)
then yes:

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date) LIKE [EnterDate yyyymmdd or yyyymm] & "*") AND
((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");

This might be a bit awkward: if the user enters 2011 they'll get all records
for that year, if they enter 20 they'll get all records from the 21st century,
if they enter a blank they'll get all records.

I'd probably use a Date/Time field for the date field (and would *NOT* use the
reserved word Date as a fieldname) and a Form to collect criteria, rather than
a prompt.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
C

carl

I use this query...
Click to expand...
TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");
Click to expand...
Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.
Click to expand...
Thanks in advance.
Click to expand...

Assuming that the date field is of Text datatype (as appears to be the case)
then yes:

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date) LIKE [EnterDate yyyymmdd or yyyymm] & "*")  AND
((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");

This might be a bit awkward: if the user enters 2011 they'll get all records
for that year, if they enter 20 they'll get all records from the 21st century,
if they enter a blank they'll get all records.

I'd probably use a Date/Time field for the date field (and would *NOT* use the
reserved word Date as a fieldname) and a Form to collect criteria, ratherthan
a prompt.
--

             John W. Vinson [MVP]
 Microsoft's replacements for these newsgroups:
 http://social.msdn.microsoft.com/Forums/en-US/accessdev/
 http://social.answers.microsoft.com/Forums/en-US/addbuz/
 and see alsohttp://www.utteraccess.com- Hide quoted text -

- Show quoted text -
Click to expand...

Thanks. I should have mentioned that the date format is number.

Seems like the "Like" operator does not work on numbers ?

Is there a similar one that will work on a number ?
 
J

John W. Vinson

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");


Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.
Click to expand...

Number, eh? Ouch.

In that case you'll need to parse the input:

WHERE (((OCC_Data.[date])>= IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
100*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:])) AND (OCC_Data.[date]< IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
101*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:]) + 1)

Ugly as sin, because of the nonstandard storage of the date. Again, your life
would be much easier if you used a Date datatype.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com
 
J

John Spencer

Other options would be

Inefficient (Force the number to a string] - cannot use indexes
WHERE [Date] & "" Like [Enter Date String] & "*"

More efficient )one calculation to get the beginning and ending of the date
numeric range.
WHERE [Date] >= Val(Left([Enter Date String]&"00000000",8))
AND [Date] <= Val(Left([Enter Date String] & "99999999",8))

That means you can return data by Century, decade, year, month, or day.


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I use this query...

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");


Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.
Click to expand...

Number, eh? Ouch.

In that case you'll need to parse the input:

WHERE (((OCC_Data.[date])>= IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
100*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:])) AND (OCC_Data.[date]< IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
101*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:]) + 1)

Ugly as sin, because of the nonstandard storage of the date. Again, your life
would be much easier if you used a Date datatype.
Click to expand...
 
C

carl

Other options would be

Inefficient (Force the number to a string] - cannot use indexes
WHERE [Date] & "" Like [Enter Date String] & "*"

More efficient )one calculation to get the beginning and ending of the date
numeric range.
WHERE [Date] >= Val(Left([Enter Date String]&"00000000",8))
AND [Date] <= Val(Left([Enter Date String] & "99999999",8))

That means you can return data by Century, decade, year, month, or day.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I use this query...
TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");
Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.
Click to expand...
Click to expand...
Number, eh? Ouch.
Click to expand...
In that case you'll need to parse the input:
Click to expand...
WHERE (((OCC_Data.[date])>= IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
100*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:])) AND (OCC_Data.[date]<  IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
101*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:]) + 1)
Click to expand...
Ugly as sin, because of the nonstandard storage of the date. Again, your life
would be much easier if you used a Date datatype.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks again.

I am trying this one:

More efficient )one calculation to get the beginning and ending of the
date
numeric range.
WHERE [Date] >= Val(Left([Enter Date String]&"00000000",8))
AND [Date] <= Val(Left([Enter Date String] & "99999999",8))



Here's my SQL...


TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (OCC_Data.date>=Val(left([EnterDate yyyymmdd] & "00000000",8))
And OCC_Data.date<=Val(left([EnterDate yyyymmdd] & "99999999",8)) And
((OCC_Data.Product) In ('OSTK','OIND')) And OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX'))
GROUP BY date
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");

When I run it, I get prompted for only one data entry. and I get no
results.

Is my syntax incorrect ?
 
J

John Spencer

The two parameters must have different names. Sorry, I was unclear

TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (OCC_Data.date>=Val(left([EnterStartDate yyyymmdd] & "00000000",8))
And OCC_Data.date<=Val(left([EnterEndDate yyyymmdd] & "99999999",8)) And
((OCC_Data.Product) In ('OSTK','OIND')) And OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX'))
GROUP BY date
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");


John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

Other options would be

Inefficient (Force the number to a string] - cannot use indexes
WHERE [Date]& "" Like [Enter Date String]& "*"

More efficient )one calculation to get the beginning and ending of the date
numeric range.
WHERE [Date]>= Val(Left([Enter Date String]&"00000000",8))
AND [Date]<= Val(Left([Enter Date String]& "99999999",8))

That means you can return data by Century, decade, year, month, or day.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

I use this query...
Click to expand...
TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT OCC_Data.underlying, Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM
Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (((OCC_Data.date)=[EnterDate yyyymmdd]) AND ((OCC_Data.Product)
In ('OSTK','OIND'))) and OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX')
GROUP BY OCC_Data.underlying
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");
Click to expand...
Which prompts me for a date. Is there a way to change the query so
when the user gets prompted to enter a date, that the can enter
20110410 for April 19 2011 OR enter 201104 and get all records for
April 2011.
Click to expand...
Number, eh? Ouch.
Click to expand...
In that case you'll need to parse the input:
Click to expand...
WHERE (((OCC_Data.[date])>= IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
100*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:])) AND (OCC_Data.[date]< IIF(Len([EnterDate yyyymmdd or yyyymm:]) = 6,
101*Val([EnterDate yyyymmdd or yyyymm:])), Val([EnterDate yyyymmdd or
yyyymm:]) + 1)
Click to expand...
Ugly as sin, because of the nonstandard storage of the date. Again, your life
would be much easier if you used a Date datatype.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks again.

I am trying this one:

More efficient )one calculation to get the beginning and ending of the
date
numeric range.
WHERE [Date]>= Val(Left([Enter Date String]&"00000000",8))
AND [Date]<= Val(Left([Enter Date String]& "99999999",8))



Here's my SQL...


TRANSFORM Sum(Nz([PC Vol]/2,0)+Nz([BD Vol]/2,0)+Nz([MM Vol]/2,0)) AS
Expr1
SELECT Sum([PC Vol]/2)+Sum([BD Vol]/2)+Sum([MM Vol]/2) AS OCC_Vol
FROM OCC_Data
WHERE (OCC_Data.date>=Val(left([EnterDate yyyymmdd]& "00000000",8))
And OCC_Data.date<=Val(left([EnterDate yyyymmdd]& "99999999",8)) And
((OCC_Data.Product) In ('OSTK','OIND')) And OCC_Data.underlying Not In
('VIX','SPX','OEX','DJX'))
GROUP BY date
PIVOT OCC_Data.exchange In ("A","B","C","I","P","Q","W","X","Z");

When I run it, I get prompted for only one data entry. and I get no
results.

Is my syntax incorrect ?
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query Question 3
Query Question 1
Query Question / 4

Top