Page 1 of 3 123 LastLast
Results 1 to 15 of 45
  1. #1
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71

    Query to find non-Caps in a field before a specific character help.

    I have a table with a text field called "LOCATION".



    The format for this is:

    "LOCATION : Cross Street"

    So the first part before the ':' needs to be in caps.

    I'm trying to get a query that will find all instances of that LOCATION where it is not in capitols or some variation at all.

    for example "LocaTION : Cross Street" or "location : Cross St"

    I figured I somehow use the LEFT function with the ASC function (ASC=58 for the ':' ) and then using the UCase function in there as well, but I'm having a hard time trying to piece it together.


    Thanks

  2. #2
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    UNTESTED; Maybe UPDATE tblMyTableName.LOCATION SET tblMyTableName.LOCATION = Ucase(Left([LOCATION],8)) & Mid([LOCATION],9)
    Then again, your post is somewhat confusing. You say you want to update part of the data in the field, which also contains the field name? Having the field name as part of the value in that same field would be strange. Need I point out that you should try anyone's suggestions on copies of tables (or whatever is involved) as my signature suggests?

    I'm understanding that you want to change Location : West Boundary to LOCATION : West Boundary; not LOCATION : WEST BOUNDARY
    Last edited by Micron; 04-19-2017 at 03:36 PM. Reason: clarification
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    That was only a example, I was trying to be generic in the namings. The official field name is "CountLocation" and the syntax for it is having the main street name before the ":" and then the cross streets after the ":".

    So for example:

    (correct entry)
    "FOOTHILLS BLVD: e/o Mountain Ave"



    Some times I'll find that users input the name without the caps so I need to flag these types of examples:

    (Incorrect entries)
    "Archibald Ave: n/o Base Line Rd"
    "8th ST: w/o Haven Ave"

    and so forth.

    So I just want a query that would return just those incorrect entries showing any text strings before the ":" that aren't in all caps in that single field "CountLocation".

    And I don't want to update them at all, I just need to query them out at this point so that I can see what records have been entered in this format and that's all...

  4. #4
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First and most import - separate the data! If you always require a "something : something" then make sure that the user can't enter anything else. Then you can always change the first part to caps and add the ":".

    To fix the data,
    - check that all fields contain a colon: criteria Like "*:*"
    - for those that do, change the first part to caps like Micron posted
    - change the second part to sentence case - StrConv([YourFieldName],3)

    All other updating will have to be done one at a time, list the data and see what it is showing, then create a query to change it
    - change all St to Street
    - etc

  5. #5
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Well it would be nice if I could separate the fields. This is not my database, I am just creating the end user form for manual entry. So I can't be splitting up fields and making new ones as there are so many other processes and tables linked to this form.

    In the CountLocation field they all have the ":" so that isn't the issue.

    I don't want to change (update query) anything at this point. I just need to view the records that have the incorrect format and that's it. I then need to cross check those entries and see whose been entering them, the dates and other things.

    With the attachments I tried what Mircon suggested, but when I run it, you can see in Attachment 2 that it returned all the entries and not just the ones (which I highlighted) that don't have all caps before the ":"
    Attached Thumbnails Attached Thumbnails Access1.JPG   access2.JPG  

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    First, you don't have to make table changes in order to separate the fields, that is purely a form function. In the AfterUpdate event of both unbound textboxes, add them together and put the value in the one bound field.

    The criteria that you are using is for an update query, not a select. The criteria is answering the question Which records do I want to see? You would need a new field in the query specifying true or false which would be your criteria - is the first part of the field in caps Y/N.

  7. #7
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I think this can be done by wrapping the Asc and Left functions in an IIF expression IF we can settle on one character of the first street name and determine whether or not it's upper case. If not, the query will have to call a function that examines each character up to the colon.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Well in every instance, the first letter if not numeric is generally always a capital, it's just the letters after that where the issue lies. And I don't want to add this into the actual FORM or anything. I just wanted to have a simple query that I could run separately to just see those instances right now.

    So seems it might be a lot harder than as you mentioned with having to check each character as there can be so many variations of this

    Street Name:
    STREET Name:
    333 Street name:

    etc

    And yes I guess I could then redesign the form to do what you stated aytee111. I have something similar to that already just in the reverse having a entire coordinates places in a text box and parsed out into to other text boxes attached to fields. Though I was just hoping to just have a simple query I could use and that was it.

    Seems like it's not as easy as I was thinking it might be...

  9. #9
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Then I think the query location field has to call a function that determines the portion length up to the space before the colon, starts from the left, tests is numeric and moves to the next character if not. If it is, it tests if the Ascii value lies within the range of capital letters. If it doesn't then return that record. Not too difficult methinks, but how to distinguish that record - not sure exactly how at the moment. Could be via using Like operator but having the unique record id might be better. Then again, using the same principles but building the sql and running in code would allow you to work from a recordset which provides that id. I think it's doable.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    It probably is doable, but probably not at my level of SQL and I think it's currently beyond what I can grasp atm. Since the characters before the : can be any number of length, I can't easily just say specific characters need to be in Caps as the string could be from 3 to 25 or more depending on the street name.

    One way would be to make a new table each time the query is run and then split the CountLocation by the : so I have Loc_Part1 and Loc_Part2 and then run the All caps query on the Loc_Part1 to check I guess. Though that's yet another table and then I couldn't easily go through to fix things...

  11. #11
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    If it is OK to find the values and dump them into a table, then I can figure it out. I have all the logic working, I just don't have a place to put it. It's not as simple as calling the function from a query, so if you know how to run a module level function you don't need a form with a button. If this is going to be a one time thing (I think not) then I'll explain how to do that. Otherwise, a form button and a table to put the results in is about all that's needed to run it. If you want to proceed, create a target table with one field that can be linked to the locations table. If locations has an autonumber ID, then that is great, you will join the target table to locations on ID in a query that joins these two tables. You put the locations table fields into the query so that you can return those details where the two ID's match. The target ID's come from the code procedure, which will have to wipe the target table clean at each start, otherwise you just keep adding to the ones you've already fixed. If locations doesn't have an autonumber ID, then the target table field has to be of the same type as the PK in your locations table since it will hold those unique values as well. Hope that's not too confusing.

  12. #12
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    I wasn't even going to have a button for this in the form at all. The form part was only were the user inputs the data in the incorrect format. It's kind of a new thing where I want to have the main street more defined and stand out that's why. So right now it's just a manual user entry thing that when printed out it just stands out better.

    And this is more like something I would run after a lot of data has been entered, so not all the time. I have a few other check queries I have that I run here and there as well (like 1-2 times a year when we do this project), so I was just hoping to add this query to those checks and make it simple.

    So a one time thing is kind of maybe a good place to start so I can at least see how that works. And yes the TBICOUNT table has an GeoID field which is a unique number based on the location.

    Just seems this might be more work than I want to put in. I was just thinking there had to be some simple SQL statement where I could use the ASC with the Ucase somehow.

    I would just be easier for me instead just to query all the new entries by the date field I have on the form that updates when the record changes. Then I can see all those recent records and see the ones that are entered in the wrong way.

    But thanks for trying to help...

  13. #13
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Too bad. I should have waited rather than invest the time creating a procedure, but sometimes I can't resist the challenge.
    Good luck with your project.

  14. #14
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    The query below gives the list of wanted/faulty entries:
    SELECT MyTable.Location FROM MyTable WHERE StrComp(LEFT(MyTable.Location, INSTR(MyTable.Location,":")-1), UCASE( LEFT(MyTable.Location, INSTR(MyTable.Location,":")-1)),0) <> 0;

  15. #15
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Micron, I got lost in your last post (which you thought I might). Anyways I've attached a sample table with only that field I need searched. The GeoID is the one I can use to link to other tables as it is unique.

    Also I have the query below in that table as well:


    Thanks ArviLaanemets, however when I do this code:

    SELECT TBICOUNT.CountLocation
    FROM TBICOUNT
    WHERE ((StrComp(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1),UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)),0)<>"0;"));


    I get the following error: "You tried to perform an operation involving an function of feature that was not installed in this version of Microsoft Access". It might because I have to use an .mdb (2002-2003) and not an .accdb because the latter doesn't work with ARCGIS.

    Though I imported the table into a new *.accdb (2007-2016) and still got the same errorTEST_TBICOUNT.accdb

Page 1 of 3 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 12-14-2014, 01:41 PM
  2. Replies: 2
    Last Post: 09-20-2014, 11:28 AM
  3. Replies: 8
    Last Post: 02-09-2014, 07:25 PM
  4. Replies: 5
    Last Post: 05-15-2013, 03:18 PM
  5. Find specific value in any field
    By bkvisler in forum Queries
    Replies: 8
    Last Post: 12-08-2010, 04:23 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums