Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Sort by Calculated Field

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Sort by Calculated Field

    Hi, A5ers -
    I am struggling to learn A5, having been told that it is MUCH more capable than A4. Thus far I find the same shortcoming:

    It would seem to be an everyday requirement of businesses to display ONLY those 'Customer Balances' which calculate to Non-Zero. We require this.

    While I can get my 'Customer Balance Form' to display a calculated field for customer balance, I can find NO WAY to sort for the set for Zero Balance.

    What am I missing here?
    - Doug Barr

    #2
    RE: Sort by Calculated Field

    By "sort for the set for Zero Balance" I assume you mean query the set for a form calculated field which evaluates to zero.

    Since the same fields are available in the query by table genie that are in the calculated field genie, merely enter the same expression that defines the calculated field into the query by table genie.
    There can be only one.

    Comment


      #3
      RE: Sort by Calculated Field

      Doug, you haven't told us much to go on...

      If your form is based on a set, and the set has customers in the parent table, and transactions in a linked child table, one approach to your problem would be to add a running_balance field in the parent table. Update it when transaction records change. Use the new field in the parent table to filter those records where running_balance zero.

      -- tom

      Comment


        #4
        RE: Sort by Calculated Field

        Doug,

        Yes it can be done. Here's how:

        Create a calculated field in the tables structure. It then has all the same properties as a regular field and you can sort on it, query on it etc.

        Calculated fields created on a form, browse or report, do not have the same properties as a calc field (read only)created in a table. Don't know why, but that's the way it is.

        kenn
        TYVM :) kenn

        Knowing what you can achieve will not become reality until you imagine and explore.

        Comment


          #5
          RE: Sort by Calculated Field

          Doug:

          Here is one other thing to look at.

          When calculated fields are placed on a form by default they have their "disable" property checked. You can verify this by opening the form in design mode, selecting the field, and right clicking to examine its properties.

          Uncheck the box and you will then be able to sort on the field. For example, you could do a query by form to find non-zero balances.

          Bill
          Bill Hanigsberg

          Comment


            #6
            RE: Sort by Calculated Field

            Ahhh Bill,

            I learned something new. Your solution must be kempt in mind.

            kenn
            TYVM :) kenn

            Knowing what you can achieve will not become reality until you imagine and explore.

            Comment


              #7
              RE: Sort by Calculated Field

              First I want to thank everyone who has responded, so quickly.

              Stan Mathews wrote:
              -------------------------------
              By "sort for the set for Zero Balance" I assume you mean query the set for a form calculated field which evaluates to zero.

              >>>Actually I misspoke. I'm actually looking for values that are non-zero. Same method, though.

              Since the same fields are available in the query by table genie that are in the calculated field genie, merely enter the same expression that defines the calculated field into the query by table genie.

              >>>I tried the calc.field genie, tried entering the same expression in table form (also qbf) - Still "Invalid Expression" error message.

              Thanks for the idea, I truly think I did what you suggested, and it simply won't work on this particular set structure.
              -Doug

              Comment


                #8
                RE: Sort by Calculated Field

                Yes, it's a Customer parent table, with linked db's for Receipts, Invoice Items (sublinked to product prices), Shipping, Adjustments, and Taxes.

                Perhaps you're right: posting to running fields in the master db is the only way it will work. I was hoping to avoid doing something in A5 that (I am told) is unnecessary in Access. Besides, if this is the route to success, then I can forego the upgrade to A5, and do it in A4.

                - Doug


                Tom Cone Jr wrote:
                -------------------------------
                Doug, you haven't told us much to go on...

                If your form is based on a set, and the set has customers in the parent table, and transactions in a linked child table, one approach to your problem would be to add a running_balance field in the parent table. Update it when transaction records change. Use the new field in the parent table to filter those records where running_balance zero.

                -- tom

                Comment


                  #9
                  RE: Sort by Calculated Field

                  Ken -
                  I do have a field allotted for running balance in the master db (not much use at present). Unfortunately, that field cannot do a complex calculation involving a set structure that 'does not yet exist' (since the set that does the calculation among related db's is based on this master db.
                  I am aware of the difference between calcfields in forms vs databases. I wish I could get one to work for me.
                  Thanks,
                  Doug

                  Comment


                    #10
                    RE: Sort by Calculated Field

                    I have done just that - disabling the 'balance-calc' field, and after that I tried all the sub-calculations that lead up to it.
                    Yes, it allows me to enter data into that field, but no data is anything but "Invalid Expression". I've entered the actual expression, as written in the balance-calc field. I've tried simpler expressions as well. Apparently there is no way to enter data in a calculated field in a query.

                    Unless... more ideas?

                    - Doug

                    Comment


                      #11
                      RE: Sort by Calculated Field

                      Doug:

                      "I have done just that - disabling the 'balance-calc' field...."

                      You want to *enable* the field on the form. Uncheck the "disable" box.

                      Go to query by form. In the (now enabled) calc field enter >0.01 and run the query. You should get all records except those with a nul balance.

                      Or do I misunderstand what you are trying to do.

                      Bill
                      Bill Hanigsberg

                      Comment


                        #12
                        RE: Sort by Calculated Field

                        Here's a little example that demonstrates what I thought you wanted.
                        There can be only one.

                        Comment


                          #13
                          RE: Sort by Calculated Field

                          Doug, are you saying that you can't enter ">0" in the calc field while in Query by Form mode?


                          Or, are you trying to enter data into the calc field while the form is in change or enter mode? This is not possible for a calc field, which derives its value from the calc expression you define... it may not be overridden by values supplied directly to the field by the user.


                          - tom

                          Comment


                            #14
                            RE: Sort by Calculated Field

                            Hi, Bill
                            No misunderstanding, you know what I'm trying to do. In my rush, I misspoke. I DID enable, not disable.
                            But when I entered >0.01 (or anything else) in that field, the returned message is "Invalid Expression". I still have this gut feeling that I'm asking A5 to do more than it's capable of. Prove me wrong.
                            - Doug

                            Cust_bal.set:
                            Customer
                            =>Cust_rcp (Receipts from sale)
                            =>Inv_head (Shipping & Adjustment per invoice)
                            =>Inv_item (qty of Products sold)
                            ->Products (Sale price)
                            ->Tax_rate (multiplier if line item calls for tax)

                            In the form:
                            Calcs: Total of all Receipts (+)
                            Total of all Shipping (-)
                            Total of all Adjustments (+)
                            Total of Extended Products (qty*saleprice)
                            *Tax Rate (-)

                            And a final Calc to add all those up. Balance displayed.

                            Comment


                              #15
                              RE: Sort by Calculated Field

                              Tom -
                              Yes, I CAN enter that figure. (having Enabled use of that field, per Bill).
                              1) I begin a query, either by Form or by Table
                              2) Mouse into the balance field (tabbing to it is not possible - tabbing just cycles among the 3 ID/Name fields at top of form - to me, an indicator of troubles to come)
                              3) Enter the data (>0.01 for example)
                              4) Click the lightning icon (run)
                              The Alert box "Invalid Expression" appears.

                              See the post above for a description of the set.
                              - Doug

                              ps - I hope I'm not asking too much here. In my 'day job' I'm a contributing member of an AutoCad customization group, and I always love a good challenge. Thanks again.

                              Comment

                              Working...
                              X