Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Red face Unanswered: Query - Dialog Box is pointless

    I have created a Query (in Access 2010) which has 4 different 'Expressions' (eg. calculated data) based on other Fields in the same Query.

    When I try to Run the Query, a dialog box pops up and asks for the ‘Parameter Value’ of each of the 4 Calculated Fields.

    But the Query runs correctly whether I input data into the dialog boxes (x4) or even if I leave the dialog boxes (x4) empty and just hit “OK”.

    Would anyone know why these dialog boxes are appearing and how can I stop them from appearing (as they do not seem to have any function anyway)?

    Thanks.
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    This means that there is something in the expressions that Access does not understand. Can you post the SQL text of the query?
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Thanks for your reply. I have copied the code below (I am sorry that it is a little bit complicated?? Well it is to me, anyway!!! I don't know much about SQL code, I just use the automated menus!!!

    SELECT [Contract Table].[Contract ID], [Contract Table].[Start Date (Original)], [Contract Table].[End Date (Original)], [Rental Income Table].[Transaction Type], Sum([Rental Income Table].Amount) AS SumOfAmount, [Contract Table].[Monthly Rent], DateDiff("m",[Start Date (Original)],[End Date (Original)])+(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)]))+1 AS [Contract Months], DateDiff("m",[Start Date (Original)],Date()+(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)])))+1 AS [Months Today], IIf([Months Today]<[Contract Months],[Months Today]*[Monthly Rent],[Contract Months]*[Monthly Rent]) AS [Rent Due], [SumOfAmount]-[Rent Due] AS Owed

    FROM [Contract Table] INNER JOIN [Rental Income Table] ON [Contract Table].[Contract ID] = [Rental Income Table].[Contract ID]
    GROUP BY [Contract Table].[Contract ID], [Contract Table].[Start Date (Original)], [Contract Table].[End Date (Original)], [Rental Income Table].[Transaction Type], [Contract Table].[Monthly Rent], DateDiff("m",[Start Date (Original)],[End Date (Original)])+(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)]))+1, DateDiff("m",[Start Date (Original)],Date()+(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)])))+1, IIf([Months Today]<[Contract Months],[Months Today]*[Monthly Rent],[Contract Months]*[Monthly Rent]), [SumOfAmount]-[Rent Due]
    HAVING ((([Rental Income Table].[Transaction Type])="Rental Income"));
    What would you attempt to do if you knew you would not fail?

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    I am still playing with this. I have just noticed that if the "Amount" is not Summed then only one dialog box appears (requesting info for the SumOfAmount).
    But then the "Owed" figure cannot be calculated without the SumOfAmount figure.

    Don't know if that helps?
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    What I don't understand is why the Dialog Boxes appear (requesting some data) but then (regardless of if I put any data or not) it still calculates the Query correctly??

    This is why I think the pop-up dialog box is pointless?
    What would you attempt to do if you knew you would not fail?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    the box appears because the SQL parser cannot find the column in the specified table or table list so it interprets that as a request for a parameter at runtime.

    so check the spelling of the column(s) that are being displayed inthe dialog box check for additional spaces or other issues.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Yup, I checked that. Spelling and spaces seem to be OK.

    These 4 fields are Expressions in a calculated Query field (therefore the fields do not belong to a table).

    Example:
    Contract Months: DateDiff("m",[Start Date (Original)],[End Date (Original)])+(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)]))+1

    Example:
    Owed: [SumOfAmount]-[Rent Due]

    Actually, I didn;t type the Control name coz I just double-clicked in the Expression Builder box.
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Aaaargh has no one pointed out to you not to use reserved words, not to use spaces in table / column names
    you don't need spaces.. you cna set a property as part of the column design which will accpet sapces that will be used in (new) forms and reports

    bear in mind you dont' 'have' to do everything in a Query
    you can do additional calculations and so on in a form or report

    you may need to simplify the query buy breaking it down into several queries, glueing it all together at the top level
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Spaces in names are never 'OK,' just manageable, in most cases! Also, you cannot use punctuation marks in names, and I believe that the Access Gnomes consider Opening and Closing Parenthesis to be Punctuation. So, first off I think you need to get rid of the parens, and since you're doing that you may as well get rid of the Spaces, too! So change

    [Start Date (Original)] and [End Date (Original)]

    to

    Start_Date_Original and End_Date_Original

    Parens, (), are a particularly bad choice as part of a name, because they have special meanings in VBA code.

    I suspect, although I'm not going to try to wade through all of that code, that components of some of your calculations are not yet available, when Access first encounters the calculations, causing the Parameter Boxes to pop up, but are available after the entire Query has run, hence the delayed results.

    Linq ;0)>
    Last edited by Missinglinq; 11-14-12 at 13:07.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  10. #10
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Moreover, I'm not sure to understand why you built some expressions the way you did:
    Code:
    [Contract Months]: DateDiff("m",[Start Date (Original)],[End Date (Original)])
                     +(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)]))
                     +1
    a) DateDiff("m",[Start Date (Original)],[End Date (Original)]): difference in months between [Start Date (Original)] and [End Date (Original)].

    b) +(DatePart("d",[Start Date (Original)])>DatePart("d",[End Date (Original)])) In this expression, > (greater than) is an evaluation operator that can return True (-1) if the day part in [Start Date (Original)] is greater than the day part in [End Date (Original)], False (0) if the day part in [Start Date (Original)] is less than or equal to the day part in [End Date (Original)], or Null if [Start Date (Original)], [End Date (Original)] or both are null.

    This means that in your expression you add -1 (i.e. substract 1), add 0 (i.e. do nothing) or add Null (i.e. render the whole expression Null) to the difference in months between [Start Date (Original)] and [End Date (Original)].

    c) +1 Then you add 1 to the expression to get [Contract Months].

    The same principle is used for [Months Today]. Is there a rational explanation for such expressions?

    It looks as if you were mixing the syntax of the DateDiff() function with the syntax of the IIF() function together.
    Have a nice day!

  11. #11
    Join Date
    Nov 2009
    Posts
    223
    Aaaargh has no one pointed out to you not to use reserved words, not to use spaces in table / column names
    you don't need spaces..
    Whoops. Yes, I kinda heard about not using spaces but everything has seemed to be working correctly with spaces included, so i thought why not?

    Honestly, I do not know what reserved words were, and so would not have known about that. Now I have researched and take note. Although the () didn;t seem to be in the reserved list?

    Cheers.
    What would you attempt to do if you knew you would not fail?

  12. #12
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    They're not, they're considered punctuation marks, which are also forbidden in Access names!

    Linq ;0)>
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  13. #13
    Join Date
    Nov 2009
    Posts
    223
    I have redone my database and removed all spaces in names.

    Therefore, my Query has changed (and become more simplified).

    Good news - now only 2 Dialog Boxes pop up asking for data (it was 4). But only on the following Expressions with Calculated Fields:-

    MonthsToday: DateDiff("m",[StartDate],Date()+(DatePart("d",[StartDate])>DatePart("d",[EndDate])))+1

    ContractMonths: DateDiff("m",[StartDate],[EndDate])+(DatePart("d",[StartDate])>DatePart("d",[EndDate]))+1
    What would you attempt to do if you knew you would not fail?

  14. #14
    Join Date
    Nov 2009
    Posts
    223
    Any ideas on why the Dialog Boxes are popping up asking for data, but when i put NO DATA in, the Query returns with the correct calculation in these Fields?

    And what I can do to resolve it?

    Any help is, as always, very much appreciated.
    What would you attempt to do if you knew you would not fail?

  15. #15
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Basically, this means that Access cannot "understand" something in the query expression.

    I would try first to replace:
    Code:
    DateDiff("m",[StartDate],Date()+(DatePart("d",[StartDate])>DatePart("d",[EndDate])))+1
    with:
    Code:
    DateDiff("m",[StartDate],(Date()+(DatePart("d",[StartDate])>DatePart("d",[EndDate]))))+1
    If it does not work, I would try:
    Code:
    DateDiff("m",[StartDate],Date()+(IIf(DatePart("d",[StartDate])>DatePart("d",[EndDate]), -1, 0)))+1
    If it still does not work, I would then try replacing the addition of Date() and a boolean expression:
    Code:
    Date()+(DatePart("d"...
    with a DateAdd() function, etc... until the query interpreter stops nagging me.

    When the solution is found, I would apply it to the expression computing ContractMonths.
    Have a nice day!

Posting Permissions

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