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

Incorrect Query Results

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

    Incorrect Query Results

    I have a simple Character field that gives the month/year when an item ships. Format mm/yy

    If I do a query by form and enter 01/01..12/01 included in my results are items from the year 2000. (mm/00)

    If I use substr(shipped,4,2)="01" the query is performed correctly.

    Am I missing something here or is this an error with A5?

    Thanks

    #2
    RE: Incorrect Query Results

    Philip,

    are you sure all fields have exactly five digits in them?

    If the range is:

    01/01..12/01

    and one of your fields is
    3/00

    I could see why it might be included.

    -- tom

    Comment


      #3
      RE: Incorrect Query Results

      Just to make sure I went back did the query and ALL the characters are in the proper location. The one thing I did notice was that the query started with 02/00 and cycled thru 12/01. I am not sure why it did not pick up 01/00???

      Not sure what to think....

      Comment


        #4
        RE: Incorrect Query Results

        You are sorting on a character field and character fields sort from let to right.To get a feel for this go to the interactive window and do something like the following:

        ?"01/00" > "01/01"
        = .F.
        ?"03/09" "01/01"
        = .T.

        Once you see how programs sort alpha characters you may decide a better way to save your field is YY/MM. If 1999 dates from history are involved, YYYY/MM would be better.
        John

        Comment


          #5
          RE: Incorrect Query Results

          I don't think you can sort or filter the way you are trying if you only have the month and year. This is a tip I picked up from this board many years ago. Assuming you first convert the year and month to character then use the following expression, you will finish up with a neat string to sort and filter on.

          STR(Val(Year)+(val(Month_No)*.01),10,2)

          You could add another field> 'sort_field' to your table and make it calculated under field rules with the above expression I use this frequently because I often have input with just a month and year to work on.

          Michael

          Comment


            #6
            RE: Incorrect Query Results

            How about using the field as a Date field. Set the field's format to show 01/01, for example. When you enter the date, if the 'day' is not critical, pick any day of the month and it would show month/year; 01/01. Your query would be:
            1/0/1/01..12/31/01 and that should work just fine.

            For grins, I ran the following query by form as: 1/1/99..12/31/99. It worked, as Lawrence used to say, Wonderful, Wonderful!!.

            kenn
            TYVM :) kenn

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

            Comment


              #7
              RE: Incorrect Query Results

              Ken,
              As you know sometimes using date fields can be difficult to handle. I have been using the mm/yy CHARACTER format for nearly 10 years. This goes way back to the early days of Alpha. I have not had any problems with this format until using A5. Please keep in mind that using the substr expression solves the problem and I don't mind using it. However, why have I been able to use this mm/yy format for all these years and now A5 doesn't like it.

              If you think about it, how can mm/00 be part of mm/01 when doing a query? 00 definitly is smaller than 01. There should be no reason why doing a query of 01/01..12/01 should include mm/00. That is why I have posted this to the site. There are many workarounds, and I was hoping someone from Alpha would have responded to this so that I knew I was not loosing my mind and misinterpreting how this type of simple query should or should not work. In my opinion this is a very simple, straight forward query.

              I am not sure where to go from here on this issue other than to say; I really appreciate the help that this board provides.

              Thanks to all...

              Comment


                #8
                RE: Incorrect Query Results

                philip,
                you write

                , how can mm/00 be part of mm/01 when doing a query? 00 definitly is smaller
                than 01. There should be no reason why doing a query of 01/01..12/01 should include mm/00.

                but in this case, alpha is right and you are wrong. you
                are defining characters and not dates. you would agree
                that if you defined your fields as

                aa/xx through kk/zz that if you created a query on
                aa/xx..bb/yy
                that the first one included would be
                aa/xx
                the next
                aa/xy
                aa/yy
                ab/xx
                ab/xy
                ab/yy
                ac/xx
                ac/xy
                ac/yy
                and so forth?

                you are asking for all values
                01/01..12/01
                you should get
                01/01
                02/00
                02/01
                03/00
                03/01
                04/00
                04/01
                05/00
                05/01
                and so on. as character strings,
                "02/00">"01/01"
                just as
                "ab/xx">"aa/xy"

                Comment


                  #9
                  RE: Incorrect Query Results

                  Philip,

                  John said that character fields sort form left to right. Thus they are querying on the month and not the year. You will get more than 1 year when doing a query. 01/00 will show along with 01/01 and 01/02. I ran into this problem at work with Access and had to switch it around.

                  kenn
                  TYVM :) kenn

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

                  Comment


                    #10
                    RE: Incorrect Query Results

                    Peter,

                    After reading your explanation, How much CROW would you like me to eat...you are correct and I AM WRONG.

                    Thank you very much

                    Comment


                      #11
                      RE: Incorrect Query Results

                      Philip,

                      There is a HUGE Crow table where many of us have been. I've been there more than once and no doubt will find myself there again. I've never gotten used to the taste of Crow but each time we eat Crow, we Grow. Maybe there should be a sloagan, "Grow with Crow"? Is there enough BBQ sauce to change the flavor?

                      kenn
                      TYVM :) kenn

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

                      Comment


                        #12
                        RE: Incorrect Query Results

                        Dakin Farm BBQ sauce from Vermont can do it. It has a nice tang with maple syrup....My holiday dish, crow with BBQ

                        Everyone have a good holiday season....

                        Comment


                          #13
                          RE: Incorrect Query Results

                          "Grow With Crow" and Dakin Farm BBQ sauce from Vermont.

                          See Philip for your bottle. Supplies are limited and going fast!

                          kenn
                          TYVM :) kenn

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

                          Comment


                            #14
                            RE: Incorrect Query Results

                            Peter,
                            I have finally taken the time to research why I did not have this query problem for the past decade. Because of the power of A5, I decided to leave more than one years worth of data for the table that the query is performed. Prior to A5 I would only have the current years data, otherwise the size of the table gets huge and it takes longer to query. That is why I did not have the problem in the past.

                            Once again thank you and have a good holiday.
                            Phil

                            Comment

                            Working...
                            X