Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Parameters Set By What? (2000)

    If I open the Mask2 form in the attached mdb, select any option in the two combo boxes and click on the Report button, Access asks me for a couple of parameters but, even if I don't enter anything and click the OK button, the report is correctly shown. The record source for the report doesn't contain any parameters so why does Access ask for those?
    Attached Files Attached Files

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query Parameters Set By What? (2000)

    Access is asking for parameters for two reasons.

    Open your report in Design Mode and go to Properties, Data, Record Source.

    1. Your IF statement reads as follows:

    IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])<>False)

    Your IF statement references Tabella1.Dipendenza, however, you do not include Tabella1.Dipendenza as part of the record source available to the report. (Thus, access asks for this parameter.) To correct this, add Tabella1.Dipendenza as part of the record source available to the report.

    2. The same issue appliess to the field Data.
    To correct the other parameter, make the Field Data available to the report as well.

    With these two changes, the reports opens without requesting the additional parameters.

    HTH
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Parameters Set By What? (2000)

    What should the IIF be? I don't understand this command.
    I thought the IIF command was IIF(condition, True, False).
    Pat <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>

  4. #4
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query Parameters Set By What? (2000)

    I believe the following will correct your problem. See Attachment for graphical change of last two fields of your query generating the report data.

    SELECT Sum(Tabella1.Combustibile) AS SumOfCombustibile, Sum(Tabella1.EnergiaElettrica) AS SumOfEnergiaElettrica, Sum(Tabella1.[Prestazioni di Terzi]) AS [SumOfPrestazioni di Terzi], Sum(Tabella1.MaterialiConsumo) AS SumOfMaterialiConsumo, Sum(Tabella1.Manodopera) AS SumOfManodopera, Sum(Tabella1.MaterialiManutenzione) AS SumOfMaterialiManutenzione, Sum(Tabella1.PersonaleManutenzione) AS SumOfPersonaleManutenzione, Sum(Tabella1.PrestazioniManutenzione) AS SumOfPrestazioniManutenzione, Sum(Tabella1.Appalti) AS SumOfAppalti, Sum(Tabella1.ManodoperaSG) AS SumOfManodoperaSG, Sum(Tabella1.Stipendi) AS SumOfStipendi, Sum(Tabella1.PrestazioniTerziAusiliari) AS SumOfPrestazioniTerziAusiliari, Sum(Tabella1.MaterialiAusiliari) AS SumOfMaterialiAusiliari, Sum(Tabella1.Spese) AS SumOfSpese, Sum(Tabella1.Ammortamenti) AS SumOfAmmortamenti, Sum(Tabella1.Consumo) AS SumOfConsumo, Sum(Tabella1.ServiziSede) AS SumOfServiziSede, IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])<>False) AS DIPENDENZA, Tabella1.Data
    FROM Tabella1
    GROUP BY IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])<>False), Tabella1.Data
    HAVING (((IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])<>False))<>False) AND ((Tabella1.Data)=[Forms]![Mask2]![Data]));
    Attached Files Attached Files
    Regards,

    Gary
    (It's been a while!)

  5. #5
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters Set By What? (2000)

    Hi Gary,
    If the following is what you mean:

    SELECT Sum(Tabella1.Combustibile) AS SumOfCombustibile, Sum(Tabella1.EnergiaElettrica) AS SumOfEnergiaElettrica, Sum(Tabella1.[Prestazioni di Terzi]) AS [SumOfPrestazioni di Terzi], Sum(Tabella1.MaterialiConsumo) AS SumOfMaterialiConsumo, Sum(Tabella1.Manodopera) AS SumOfManodopera, Sum(Tabella1.MaterialiManutenzione) AS SumOfMaterialiManutenzione, Sum(Tabella1.PersonaleManutenzione) AS SumOfPersonaleManutenzione, Sum(Tabella1.PrestazioniManutenzione) AS SumOfPrestazioniManutenzione, Sum(Tabella1.Appalti) AS SumOfAppalti, Sum(Tabella1.ManodoperaSG) AS SumOfManodoperaSG, Sum(Tabella1.Stipendi) AS SumOfStipendi, Sum(Tabella1.PrestazioniTerziAusiliari) AS SumOfPrestazioniTerziAusiliari, Sum(Tabella1.MaterialiAusiliari) AS SumOfMaterialiAusiliari, Sum(Tabella1.Spese) AS SumOfSpese, Sum(Tabella1.Ammortamenti) AS SumOfAmmortamenti, Sum(Tabella1.Consumo) AS SumOfConsumo, Sum(Tabella1.ServiziSede) AS SumOfServiziSede, <font color=red>Tabella1.Dipendenza, Tabella1.Data</font color=red>
    FROM Tabella1
    WHERE (((IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])<>False))<>False) AND ((Tabella1.Data)=[Forms]![Mask2]![Data]))
    GROUP BY Tabella1.Dipendenza, Tabella1.Data;

    it doesn't work because, notwithstanding the fact I get rid of the dialog boxes asking for parameters, the record source of the report and, consequently, the report give incorrect results if, in the Dipendenza combo box, I select either "Totale Idro" or "Totale IdroTermo". This is due to the fact that the record source query returns multiple records instead of one and the report picks up only the first one without summing them as conversely happens with the original query.
    Any other lead?

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Parameters Set By What? (2000)

    I can see now your help was sorely needed.
    I'd have never been able to solve it by myself. Thanks.
    Maybe the following could help answer Pat's question :
    I didn't put "<>false" in the query text, Access did. In fact the following query (which does without "<>false") gives the same results as yours:

    SELECT Sum(Tabella1.Combustibile) AS SumOfCombustibile, Sum(Tabella1.EnergiaElettrica) AS SumOfEnergiaElettrica, Sum(Tabella1.[Prestazioni di Terzi]) AS [SumOfPrestazioni di Terzi], Sum(Tabella1.MaterialiConsumo) AS SumOfMaterialiConsumo, Sum(Tabella1.Manodopera) AS SumOfManodopera, Sum(Tabella1.MaterialiManutenzione) AS SumOfMaterialiManutenzione, Sum(Tabella1.PersonaleManutenzione) AS SumOfPersonaleManutenzione, Sum(Tabella1.PrestazioniManutenzione) AS SumOfPrestazioniManutenzione, Sum(Tabella1.Appalti) AS SumOfAppalti, Sum(Tabella1.ManodoperaSG) AS SumOfManodoperaSG, Sum(Tabella1.Stipendi) AS SumOfStipendi, Sum(Tabella1.PrestazioniTerziAusiliari) AS SumOfPrestazioniTerziAusiliari, Sum(Tabella1.MaterialiAusiliari) AS SumOfMaterialiAusiliari, Sum(Tabella1.Spese) AS SumOfSpese, Sum(Tabella1.Ammortamenti) AS SumOfAmmortamenti, Sum(Tabella1.Consumo) AS SumOfConsumo, Sum(Tabella1.ServiziSede) AS SumOfServiziSede, IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])) AS DIPENDENZA, Tabella1.Data
    FROM Tabella1
    GROUP BY IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])), Tabella1.Data
    HAVING IIf([Forms]![Mask2]![Dipendenza]="Totale Idro",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto",IIf([Forms]![Mask2]![Dipendenza]="Totale IdroTermo",[Tabella1].[Dipendenza]="Centrali Idroelettriche Lombardia" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Borgo S.Dalmazzo" Or [Tabella1].[Dipendenza]="Centrale Idroelettrica Vittorio Veneto" Or [Tabella1].[Dipendenza]="Centrale Termoelettrica Villa di Serio",[Tabella1].[Dipendenza]=[Forms]![Mask2]![Dipendenza])) AND Tabella1.Data=[Forms]![Mask2]![Data];

    Do you know why Access adds "<>false"?

  7. #7
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Parameters Set By What? (2000)

    Gary, I tried your query and when saving it in the source of the report I got a message saying the string was too long for the builder to save.
    Non-plussed?
    Pat <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  8. #8
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Query Parameters Set By What? (2000)

    I have never encountered a message indicating that the string was too long for the builder to save before. Actually, I created the query using the query building (See prior response with attachment) and just copied to code for you to review.

    Anyone else have any ideas on this?
    Regards,

    Gary
    (It's been a while!)

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Query Parameters Set By What? (2000)

    It does happen sometimes (no idea why) on expressions strings that are between 1023 and 2048 characters in length. This MS KB article describes the problem in more detail - I believe that 2002 also exhibits this behavior.
    Wendell

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Parameters Set By What? (2000)

    That's interesting Wendell, because my SQL string length was 1616 characters long.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Parameters Set By What? (2000)

    Well, that *is* between 1028 and 2048 isn't it? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Charlotte

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Parameters Set By What? (2000)

    No it is not, well yes it is between 1028 and 2048, but Wendell said 1023 and 2048, get it right Charlotte. <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>
    I was merely supporting Wendell and his observations.
    Pat <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Query Parameters Set By What? (2000)

    <img src=/S/blush.gif border=0 alt=blush width=15 height=15> Well, *I* knew what I meant to type, even if my fingers didn't. I'll blame it on my kitten again ... except that this time it's his brother I was fending off. <img src=/S/catty.gif border=0 alt=catty width=19 height=18>
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •