Results 1 to 6 of 6
  1. #1
    Join Date
    May 2006
    Posts
    30

    Unanswered: Query not Working as Intended

    I am trying to make an easy way for users unfamiliar with Access to filter the account transactions in an Access 2010 database to show entries made between two dates. The query (SQL below) is supposed to show the form “FrmEntryDateRange” (picture attached) for the user to enter the dates. Instead it shows the “Enter Parameter Value” form (picture attached). The query is run by clicking a button on the main form. It works the same way when it is run using he Access Run button. The query itself is identical to the query that correctly displays the account transactions unfiltered, except for the WHERE clause. How can I make this work as intended?



    SELECT [Account Transactions].*, Categories.*, IIf([Categories].[Income/Expense]="Expense",-([Account Transactions]![Transaction Amount]),[Account Transactions]![Transaction Amount]) AS [Actual Amount], [Account Transactions].[Entry Date]

    FROM [Account Transactions] LEFT JOIN Categories ON [Account Transactions].Category = Categories.ID

    WHERE ((([Account Transactions].[Entry Date]) Between [Forms]![FrmEntryDateRange]![TextFromDate] And [Forms]![FrmEntryDateRange]![TextToDate]))

    ORDER BY [Account Transactions].[Entry Date];
    Attached Thumbnails Attached Thumbnails entry date form.png   Parameter Value Form.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    looks like as good an example of why not to use spaces in tabel & column names as I've seen in a while

    try removing the brackets in the where clause
    eg

    WHERE [Account Transactions].[Entry Date] Between Forms!FrmEntryDateRange!TextFromDate And Forms!FrmEntryDateRange!TextToDate

    the square brackets are certainly superfluous on the forms! part.
    the round brackets don't do anything but they may upset the SQL parser

    you only need to use the square brackets becuase you have used a space in the table and column names. however Access uses square brackets to delimit parameter prompts

    FWIW if you define a table thats stores transaction codes and their effect you can getr rid of the ugly IIF
    table TransCodes
    Code
    Effect 'can be +1 or -1

    store the TX code in the account transactions and multiply by effect....

    or handle the +/- bit in the front end, you don't have to do EVERYthing in the SQL y'know
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2006
    Posts
    30

    Query Not Working as Expected 2

    Thanks for the reply. In my defense: The database "Personal Account Ledger" is one furnished by Microsoft that I have modified for my use. So most of the names are Microsoft's choice as is the "ugly" IIF statement, since the query in question was made from a copy of an existing query furnished by Microsoft. As for the extra brackets and parentheses, these were not in my original entry in the design view of the query, but were added by Access. Please don't get me wrong, I am far from an expert in Access and am eager to learn from those who are.

    I cut and pasted your version of the WHERE statement into the SQL and, unfortunately, the result was identical to the one in my original post.

    Respectfully,

    Charles

  4. #4
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Hello, Charles,

    The reason for the problem is that queries do not open forms for data entry. They are supposed to be used, and fired, after the data entry has been completed.

    You are obviously calling the SQL statement, or query, from some VBA event or other; you haven't given us a hint which event you're using. The proper way to do this is as follows: Open the form, and have the user fill out the 'from' date. In the TextFromDate's AfterUpdate event, SetFocus to the TextToDate tb. In the TextToDate's AfterUpdate event, call the query.

    The above should work, so if you prefer the query designer - and as a bit of a novice, it might be preferable - that's probably the way to go.

    However, you have another option as well, which may take some practice to perfect. In the TextToDate's AfterUpdate event, Dim a variable strSQL As String. Build strSQL piece by piece, as follows:
    Code:
    strSQL = "SELECT [Account Transactions].*, Categories.*, IIf([Categories].[Income/Expense]="Expense",-([Account Transactions]![Transaction Amount]),[Account Transactions]![Transaction Amount]) AS [Actual Amount] " _
    & "FROM [Account Transactions] LEFT JOIN Categories ON [Account Transactions].Category = Categories.ID " _
    & "WHERE ((([Account Transactions].[Entry Date]) Between #" & [Forms]![FrmEntryDateRange]![TextFromDate] & "# And #" & [Forms]![FrmEntryDateRange]![TextToDate] & "#)) ORDER BY [Account Transactions].[Entry Date]"
    (Note the hash marks; they are date delimiters, and they have to be placed exactly where they appear. Also, note that I removed the "[Account Transactions].[Entry Date]" at the end of the SELECT clause. You don't need it, as it's included in the "[Account Transactions].*" in the begining of the clause.)

    I'm sure you are aware that you are only building the recordset with this query, and not doing any processing with it. You have to follow up by opening the recordset and analyzing the contents. You would do this by DIMming another variable as a recordset, such as rstXActions, and executing a Set rstXActions = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset) to open it and prepare it for analysis.

    Sam
    Last edited by Sam Landy; 01-21-13 at 13:19. Reason: finishing off the thought

  5. #5
    Join Date
    May 2006
    Posts
    30
    Hello Sam,

    Thanks for your help. I followed your first suggestion. It works fine, but I found that instead of using the after update event to call the query, a button on the form works better. I also changed my own idea about calling the query. What I really wanted was to change the record source of the main form of the database to the query we discussed. So the button doesn't call the query, it makes the query the record source of the main form and then closes the date entry form.

    Charles

  6. #6
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You're very welcome. Glad I could be of help.

    Sam

Posting Permissions

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