p2p.wrox.com Forums

Need to download code?

View our list of code downloads.


  Return to Index  

crystal_reports thread: Santa clause parameter


Message #1 by "Michel Medawar" <mmedawar@t...> on Sat, 22 Dec 2001 19:35:00 +0200
This is a multi-part message in MIME format.



------=_NextPart_000_002D_01C18B1F.BEA1B400

Content-Type: text/plain;

	charset="windows-1256"

Content-Transfer-Encoding: quoted-printable



Hi everybody



Hope your doing fine before the holidays...



I have a little problem,

I'm preparing a query for Santa Clause

telling him that i want a very big gift,

and showing him a list of all gifts

not less than 500$ value, so how can i make

a parameter for an Access query, when the field is

"Currency" and my parameter may change from >500

to =3D0 if Santa had no money, or <0 in that case i'll give

him a gift, without making 3 separate queries.



So my question is how can i change an operator Like

=3D, >, < before the parameter value, or using it as a

parameter.



Hope that someone would help before Xmas :)))



Have a nice Holiday all of you,

Merry Xmas.



Michel Medawar








Message #2 by "Yama Kamyar" <y.kamyar@e...> on Sat, 22 Dec 2001 18:53:42 -0000
This is a multi-part message in MIME format.



------=_NextPart_000_0002_01C18B19.F9BF6D80

Content-Type: text/plain;

	charset="windows-1256"

Content-Transfer-Encoding: 7bit



I don't understand your question. But in my opinion you can simply create a

string of all your application requirements then add it to the parameter.





----------------------------------------------------------------------------

----



In VB/ASP



Function myOperator(ByVal m_curGifts, ByVal m_curCurrency)



    While m_curCurrency < 500

        m_curSpend = m_curCurrency - m_curGifts

        If m_curSpend = 0 Then Exit While

    Wend



    If m_curSpend < 400 Then

        myOperator = ">"

    ElseIf m_curSpend > 400 Then

        myOperator = "<"

    ElseIf m_curSpend = 0 Then

         myOperator = "="

    End If



End Function



----------------------------------------------------------------------------

----



In SQL



USE pubs

GO

SELECT    'Price Category' 

      CASE

         WHEN price IS NULL THEN 'Not yet priced'

         WHEN price < 10 THEN 'Very Reasonable Title'

         WHEN price >= 10 and price < 20 THEN 'Coffee Table Title'

         ELSE 'Expensive book!'

      END,

   CAST(title AS varchar(20)) AS 'Shortened Title'

FROM titles

ORDER BY price

GO



Here is the result set:



Price Category        Shortened Title

--------------------- --------------------

Not yet priced        Net Etiquette

Not yet priced        The Psychology of Co

Very Reasonable Title The Gourmet Microwav

Very Reasonable Title You Can Combat Compu

Very Reasonable Title Life Without Fear

Very Reasonable Title Emotional Security:

Coffee Table Title    Is Anger the Enemy?

Coffee Table Title    Cooking with Compute

Coffee Table Title    Fifty Years in Bucki

Coffee Table Title    Sushi, Anyone?

Coffee Table Title    Prolonged Data Depri

Coffee Table Title    Silicon Valley Gastr

Coffee Table Title    Straight Talk About

Coffee Table Title    The Busy Executive's

Expensive book!       Secrets of Silicon V

Expensive book!       Onions, Leeks, and G

Expensive book!       Computer Phobic And

Expensive book!       But Is It User Frien



(18 row(s) affected)





Hope this gives you some ideas...



Happy Holidays,



Yama Kamyar

Sr. Software Engineer





  -----Original Message-----

  From: Michel Medawar [mailto:mmedawar@t...]

  Sent: Saturday, December 22, 2001 5:35 PM

  To: crystal_reports

  Subject: [crystal_reports] Santa clause parameter





  Hi everybody



  Hope your doing fine before the holidays...



  I have a little problem,

  I'm preparing a query for Santa Clause

  telling him that i want a very big gift,

  and showing him a list of all gifts

  not less than 500$ value, so how can i make

  a parameter for an Access query, when the field is

  "Currency" and my parameter may change from >500

  to =0 if Santa had no money, or <0 in that case i'll give

  him a gift, without making 3 separate queries.



  So my question is how can i change an operator Like

  =, >, < before the parameter value, or using it as a

  parameter.



  Hope that someone would help before Xmas :)))



  Have a nice Holiday all of you,

  Merry Xmas.



  Michel Medawar






$subst('Email.Unsub').








Message #3 by "Michel Medawar" <mmedawar@t...> on Sun, 23 Dec 2001 11:07:34 +0200
This is a multi-part message in MIME format.



------=_NextPart_000_004C_01C18BA2.05FFD660

Content-Type: text/plain;

	charset="windows-1256"

Content-Transfer-Encoding: quoted-printable



Hi Yama



Thanks for your help, i see that my question was

not very clear, so i'll say it in another way:



I have to make a query(Stored P.) in Microsoft Access

where i have a 2 fields :"Product: Text field" and "Amount:

currency field", in my vb form i'm specifying a criteria for

the amount field, like operators: ><=3D<> in a combo box,

and a amount value text box, so i may have 4 different criterias

>, <, =3D and <>.



So my question how can define the parameter in the query,

to achieve this process without having to create 4 queries

each with a different operator.



Thanks in advance



Michel Medawar.





  ----- Original Message -----

  From: Yama Kamyar

  To: crystal_reports

  Sent: Saturday, December 22, 2001 8:53 PM

  Subject: [crystal_reports] RE: Santa clause parameter





  I don't understand your question. But in my opinion you can simply 

create a string of all your application requirements then add it to the 

parameter.

  



-------------------------------------------------------------------------

-----



  In VB/ASP

  

  Function myOperator(ByVal m_curGifts, ByVal m_curCurrency)

  

      While m_curCurrency < 500

          m_curSpend =3D m_curCurrency - m_curGifts

          If m_curSpend =3D 0 Then Exit While

      Wend

  

      If m_curSpend < 400 Then

          myOperator =3D ">"

      ElseIf m_curSpend > 400 Then

          myOperator =3D "<"

      ElseIf m_curSpend =3D 0 Then

           myOperator =3D "=3D"

      End If

  

  End Function



-------------------------------------------------------------------------

-----



  In SQL

  

  USE pubs

  GO

  SELECT    'Price Category' =3D

        CASE

           WHEN price IS NULL THEN 'Not yet priced'

           WHEN price < 10 THEN 'Very Reasonable Title'

           WHEN price >=3D 10 and price < 20 THEN 'Coffee Table Title'

           ELSE 'Expensive book!'

        END,

     CAST(title AS varchar(20)) AS 'Shortened Title'

  FROM titles

  ORDER BY price

  GO



  Here is the result set:



Price Category        Shortened Title     

--------------------- --------------------

Not yet priced        Net Etiquette       

Not yet priced        The Psychology of Co

Very Reasonable Title The Gourmet Microwav

Very Reasonable Title You Can Combat Compu

Very Reasonable Title Life Without Fear   

Very Reasonable Title Emotional Security: 

Coffee Table Title    Is Anger the Enemy? 

Coffee Table Title    Cooking with Compute

Coffee Table Title    Fifty Years in Bucki

Coffee Table Title    Sushi, Anyone?      

Coffee Table Title    Prolonged Data Depri

Coffee Table Title    Silicon Valley Gastr

Coffee Table Title    Straight Talk About 

Coffee Table Title    The Busy Executive's

Expensive book!       Secrets of Silicon V

Expensive book!       Onions, Leeks, and G

Expensive book!       Computer Phobic And 

Expensive book!       But Is It User Frien



(18 row(s) affected)





  Hope this gives you some ideas...

  

  Happy Holidays,

  

  Yama Kamyar

  Sr. Software Engineer

         

          

    -----Original Message-----

    From: Michel Medawar [mailto:mmedawar@t...]

    Sent: Saturday, December 22, 2001 5:35 PM

    To: crystal_reports

    Subject: [crystal_reports] Santa clause parameter





    Hi everybody



    Hope your doing fine before the holidays...



    I have a little problem,

    I'm preparing a query for Santa Clause

    telling him that i want a very big gift,

    and showing him a list of all gifts

    not less than 500$ value, so how can i make

    a parameter for an Access query, when the field is

    "Currency" and my parameter may change from >500

    to =3D0 if Santa had no money, or <0 in that case i'll give

    him a gift, without making 3 separate queries.



    So my question is how can i change an operator Like

    =3D, >, < before the parameter value, or using it as a

    parameter.



    Hope that someone would help before Xmas :)))



    Have a nice Holiday all of you,

    Merry Xmas.



    Michel Medawar






$subst('Email.Unsub').




$subst('Email.Unsub').






Message #4 by "Preethi" <preethi@s...> on Mon, 24 Dec 2001 08:51:04 +0600
This is a multi-part message in MIME format.



------=_NextPart_000_000E_01C18C58.1ED6D150

Content-Type: text/plain;

	charset="windows-1256"

Content-Transfer-Encoding: quoted-printable



Hi Michel

Let me assume that your product is in txtProduct, Amount in txtAmount 

and the Operator in cboAmount



sql =3D"Select * from Products where ProductCode =3D'" & txtproduct.text 

& "' and Amount " & cboAmount.text & txtamount





Hope this is what you want.



Good Luck, and Merry Christmas.

Preethi



  ----- Original Message -----

  From: Michel Medawar

  To: crystal_reports

  Sent: Sunday, December 23, 2001 3:07 PM

  Subject: [crystal_reports] RE: Santa clause parameter





  Hi Yama



  Thanks for your help, i see that my question was

  not very clear, so i'll say it in another way:



  I have to make a query(Stored P.) in Microsoft Access

  where i have a 2 fields :"Product: Text field" and "Amount:

  currency field", in my vb form i'm specifying a criteria for

  the amount field, like operators: ><=3D<> in a combo box,

  and a amount value text box, so i may have 4 different criterias

  >, <, =3D and <>.



  So my question how can define the parameter in the query,

  to achieve this process without having to create 4 queries

  each with a different operator.



  Thanks in advance



  Michel Medawar.





    ----- Original Message -----

    From: Yama Kamyar

    To: crystal_reports

    Sent: Saturday, December 22, 2001 8:53 PM

    Subject: [crystal_reports] RE: Santa clause parameter





    I don't understand your question. But in my opinion you can simply 

create a string of all your application requirements then add it to the 

parameter.

    



-------------------------------------------------------------------------

---



    In VB/ASP

    

    Function myOperator(ByVal m_curGifts, ByVal m_curCurrency)

    

        While m_curCurrency < 500

            m_curSpend =3D m_curCurrency - m_curGifts

            If m_curSpend =3D 0 Then Exit While

        Wend

    

        If m_curSpend < 400 Then

            myOperator =3D ">"

        ElseIf m_curSpend > 400 Then

            myOperator =3D "<"

        ElseIf m_curSpend =3D 0 Then

             myOperator =3D "=3D"

        End If

    

    End Function



-------------------------------------------------------------------------

---



    In SQL

    

    USE pubs

    GO

    SELECT    'Price Category' =3D

          CASE

             WHEN price IS NULL THEN 'Not yet priced'

             WHEN price < 10 THEN 'Very Reasonable Title'

             WHEN price >=3D 10 and price < 20 THEN 'Coffee Table Title'

             ELSE 'Expensive book!'

          END,

       CAST(title AS varchar(20)) AS 'Shortened Title'

    FROM titles

    ORDER BY price

    GO



    Here is the result set:



Price Category        Shortened Title     

--------------------- --------------------

Not yet priced        Net Etiquette       

Not yet priced        The Psychology of Co

Very Reasonable Title The Gourmet Microwav

Very Reasonable Title You Can Combat Compu

Very Reasonable Title Life Without Fear   

Very Reasonable Title Emotional Security: 

Coffee Table Title    Is Anger the Enemy? 

Coffee Table Title    Cooking with Compute

Coffee Table Title    Fifty Years in Bucki

Coffee Table Title    Sushi, Anyone?      

Coffee Table Title    Prolonged Data Depri

Coffee Table Title    Silicon Valley Gastr

Coffee Table Title    Straight Talk About 

Coffee Table Title    The Busy Executive's

Expensive book!       Secrets of Silicon V

Expensive book!       Onions, Leeks, and G

Expensive book!       Computer Phobic And 

Expensive book!       But Is It User Frien



(18 row(s) affected)





    Hope this gives you some ideas...

    

    Happy Holidays,

    

    Yama Kamyar

    Sr. Software Engineer

           

            

      -----Original Message-----

      From: Michel Medawar [mailto:mmedawar@t...]

      Sent: Saturday, December 22, 2001 5:35 PM

      To: crystal_reports

      Subject: [crystal_reports] Santa clause parameter





      Hi everybody



      Hope your doing fine before the holidays...



      I have a little problem,

      I'm preparing a query for Santa Clause

      telling him that i want a very big gift,

      and showing him a list of all gifts

      not less than 500$ value, so how can i make

      a parameter for an Access query, when the field is

      "Currency" and my parameter may change from >500

      to =3D0 if Santa had no money, or <0 in that case i'll give

      him a gift, without making 3 separate queries.



      So my question is how can i change an operator Like

      =3D, >, < before the parameter value, or using it as a

      parameter.



      Hope that someone would help before Xmas :)))



      Have a nice Holiday all of you,

      Merry Xmas.



      Michel Medawar






$subst('Email.Unsub').




$subst('Email.Unsub').




$subst('Email.Unsub').






Message #5 by "Michel Medawar" <mmedawar@t...> on Mon, 24 Dec 2001 07:47:07 +0200
This is a multi-part message in MIME format.



------=_NextPart_000_0025_01C18C4F.3007B100

Content-Type: text/plain;

	charset="windows-1256"

Content-Transfer-Encoding: quoted-printable



Hi Preethi



Thanks for your help, the sql statement is very easy and

flexible you can define and do anything in code, but what

i want is to make it as a stored procedure in MSAccess,

like a query with parameters.



Like how to change for example a parameter from >500

to <500 to =3D500 knowing that the operators have to be

defined before.

Because what i used to do is to make a different query

for every operator.



Hope you got what i mean.



Thanks

Merry Xmas Everyone, Santa is coming tonight :)



Michel Medawar





  ----- Original Message -----

  From: Preethi

  To: crystal_reports

  Sent: Monday, December 24, 2001 4:51 AM

  Subject: [crystal_reports] RE: Santa clause parameter





  Hi Michel

  Let me assume that your product is in txtProduct, Amount in txtAmount 

and the Operator in cboAmount



  sql =3D"Select * from Products where ProductCode =3D'" & 

txtproduct.text & "' and Amount " & cboAmount.text & txtamount





  Hope this is what you want.



  Good Luck, and Merry Christmas.

  Preethi



    ----- Original Message -----

    From: Michel Medawar

    To: crystal_reports

    Sent: Sunday, December 23, 2001 3:07 PM

    Subject: [crystal_reports] RE: Santa clause parameter





    Hi Yama



    Thanks for your help, i see that my question was

    not very clear, so i'll say it in another way:



    I have to make a query(Stored P.) in Microsoft Access

    where i have a 2 fields :"Product: Text field" and "Amount:

    currency field", in my vb form i'm specifying a criteria for

    the amount field, like operators: ><=3D<> in a combo box,

    and a amount value text box, so i may have 4 different criterias

    >, <, =3D and <>.



    So my question how can define the parameter in the query,

    to achieve this process without having to create 4 queries

    each with a different operator.



    Thanks in advance



    Michel Medawar.





      ----- Original Message -----

      From: Yama Kamyar

      To: crystal_reports

      Sent: Saturday, December 22, 2001 8:53 PM

      Subject: [crystal_reports] RE: Santa clause parameter





      I don't understand your question. But in my opinion you can simply 

create a string of all your application requirements then add it to the 

parameter.

      



-------------------------------------------------------------------------

-



      In VB/ASP

      

      Function myOperator(ByVal m_curGifts, ByVal m_curCurrency)

      

          While m_curCurrency < 500

              m_curSpend =3D m_curCurrency - m_curGifts

              If m_curSpend =3D 0 Then Exit While

          Wend

      

          If m_curSpend < 400 Then

              myOperator =3D ">"

          ElseIf m_curSpend > 400 Then

              myOperator =3D "<"

          ElseIf m_curSpend =3D 0 Then

               myOperator =3D "=3D"

          End If

      

      End Function



-------------------------------------------------------------------------

-



      In SQL

      

      USE pubs

      GO

      SELECT    'Price Category' =3D

            CASE

               WHEN price IS NULL THEN 'Not yet priced'

               WHEN price < 10 THEN 'Very Reasonable Title'

               WHEN price >=3D 10 and price < 20 THEN 'Coffee Table 

Title'

               ELSE 'Expensive book!'

            END,

         CAST(title AS varchar(20)) AS 'Shortened Title'

      FROM titles

      ORDER BY price

      GO



      Here is the result set:



Price Category        Shortened Title     

--------------------- --------------------

Not yet priced        Net Etiquette       

Not yet priced        The Psychology of Co

Very Reasonable Title The Gourmet Microwav

Very Reasonable Title You Can Combat Compu

Very Reasonable Title Life Without Fear   

Very Reasonable Title Emotional Security: 

Coffee Table Title    Is Anger the Enemy? 

Coffee Table Title    Cooking with Compute

Coffee Table Title    Fifty Years in Bucki

Coffee Table Title    Sushi, Anyone?      

Coffee Table Title    Prolonged Data Depri

Coffee Table Title    Silicon Valley Gastr

Coffee Table Title    Straight Talk About 

Coffee Table Title    The Busy Executive's

Expensive book!       Secrets of Silicon V

Expensive book!       Onions, Leeks, and G

Expensive book!       Computer Phobic And 

Expensive book!       But Is It User Frien



(18 row(s) affected)





      Hope this gives you some ideas...

      

      Happy Holidays,

      

      Yama Kamyar

      Sr. Software Engineer

             

              

        -----Original Message-----

        From: Michel Medawar [mailto:mmedawar@t...]

        Sent: Saturday, December 22, 2001 5:35 PM

        To: crystal_reports

        Subject: [crystal_reports] Santa clause parameter





        Hi everybody



        Hope your doing fine before the holidays...



        I have a little problem,

        I'm preparing a query for Santa Clause

        telling him that i want a very big gift,

        and showing him a list of all gifts

        not less than 500$ value, so how can i make

        a parameter for an Access query, when the field is

        "Currency" and my parameter may change from >500

        to =3D0 if Santa had no money, or <0 in that case i'll give

        him a gift, without making 3 separate queries.



        So my question is how can i change an operator Like

        =3D, >, < before the parameter value, or using it as a

        parameter.



        Hope that someone would help before Xmas :)))



        Have a nice Holiday all of you,

        Merry Xmas.



        Michel Medawar






$subst('Email.Unsub').




$subst('Email.Unsub').




$subst('Email.Unsub').




$subst('Email.Unsub').






Message #6 by "Yama Kamyar" <y.kamyar@e...> on Wed, 26 Dec 2001 10:40:54 -0800
Okay doc...



So what we want here is a way to manage to change the operator on the fly without having it to go with 4 (operators) different
queries...



The question is would you program it in your stored procedure or your vb application?



In SP:



//Pass three parameters ~~ The Amount, The Product, The Operator

CREATE PROCEDURE sp_getSantaResult (@amount varchar(15) = null, @product varchar(100) = null, @operator varchar(2) = null) AS



// TO DO



In VB:



'Call SP sp_getSantaResult 

'Assume operator to be ">" using RDS



Set ds = CreateObject("RDS.DataSpace")

Set df = ds.CreateObject("RDSServer.DataFactory",myServer)

SqlStr = "sp_getSantaResult @amount =" & txtAmount & _

         ", @product = " & txtProduct & _

         ", @operator = " & myOperator



Set rs = df.query(strCn, SqlStr)

With rs

  'TO DO ~~ Retrieve resultSet from your SP

End With





Yama Kamyar

sr. Software Engineer

--







Join 18 million Eudora users by signing up for a free Eudora Web-Mail account at http://www.eudoramail.com


  Return to Index