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.

On Thu, 21 Apr 2011 11:36:33 -0700 (PDT), carl <(E-Mail Removed)> wrote:

> 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.
>

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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On Apr 21, 3:18*pm, John W. Vinson
<jvinson@STOP_SPAM.WysardOfInfo.com> wrote:
> On Thu, 21 Apr 2011 11:36:33 -0700 (PDT), carl <cmieda...@msn.com> wrote:
> > 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.
>
> 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/For...-US/accessdev/
> *http://social.answers.microsoft.com/.../en-US/addbuz/
> *and see alsohttp://www.utteraccess.com- Hide quoted text -
>
> - Show quoted text -

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 ?

On Thu, 21 Apr 2011 11:36:33 -0700 (PDT), carl <(E-Mail Removed)> wrote:

> 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.

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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

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

On 4/21/2011 8:37 PM, John W. Vinson wrote:
> On Thu, 21 Apr 2011 11:36:33 -0700 (PDT), carl<(E-Mail Removed)> wrote:
>
>> 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.
>
> 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.

On Apr 22, 9:04*am, John Spencer <JSPEN...@Hilltop.umbc> wrote:
> 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
>
> On 4/21/2011 8:37 PM, John W. Vinson wrote:
>
>
>
> > On Thu, 21 Apr 2011 11:36:33 -0700 (PDT), carl<cmieda...@msn.com> *wrote:
>
> >> 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.
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

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 ?

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

On 4/25/2011 12:51 PM, carl wrote:
> On Apr 22, 9:04 am, John Spencer<JSPEN...@Hilltop.umbc> wrote:
>> 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
>>
>> On 4/21/2011 8:37 PM, John W. Vinson wrote:
>>
>>
>>
>>> On Thu, 21 Apr 2011 11:36:33 -0700 (PDT), carl<cmieda...@msn.com> wrote:
>>
>>>> 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.
>>
>>> 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.- Hide quoted text -
>>
>> - Show quoted text -
>
> 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 ?
>