Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 45
  1. #16
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    A little change at the end...
    Code:
    WHERE (((StrComp(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1),UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)),0))<>0));

    The not installed error might be a missing reference. It works OK for me with these refs:



    Attachment 28356

  2. #17
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Interesting that my usual go to site for Access functions states the StrComp function is compatible with version 2003, which conflicts with M$oft web pages. It would seem to be not available to 2003. Too bad as it's an elegant solution should you ever migrate your db to a newer version. I shall have to keep that function in mind for the future as I've probably never used it. It did work for me - kudos to Arvilaanemets! Don't see why it couldn't work if you imported into a new db as you say. I will take a look at your table and post back.

  3. #18
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Where are you locating the References? I can only find this:

    And using the:

    SELECT TBICOUNT.GeoID, 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 this error: "Characters found after end of SQL statement"
    Attached Thumbnails Attached Thumbnails Ref.JPG  

  4. #19
    Join Date
    Apr 2017
    Posts
    1,673
    The end of SQL-srting is wrong - maybe this is cause for error message?

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

  5. #20
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Still getting the same error even with:

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

    Seems the () are matching up number wise with 8 ')' and 8 '('.

  6. #21
    davegri's Avatar
    davegri is offline Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Code:
    SELECT TBICOUNT.GeoID, TBICOUNT.CountLocation
    FROM TBICOUNT;
    WHERE (((StrComp(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1),UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)),0))<>0));
    Remove the semicolon after 'FROM TBICOUNT;'

  7. #22
    Micron is offline Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Here's what I got with (hopefully) enough notes to explain the code. You said you didn't want a form, so you have to run the code from the module window or create your own method of calling it. As noted therein, names like McMaster will be picked up - not much we can do about that. Your original post showed space between the name and colon, but your data does not so it was written to match the data. I can't see any issue at this point regardless since spaces are ignored in the analysis anyway. The code notes don't actually reflect my current thinking on that.
    TEST_TBICOUNTmicron.zip

    davegri's suggestion works if the correction noted is made, but then so did the original one as I previously noted.

  8. #23
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Click image for larger version. 

Name:	Access_Error.JPG 
Views:	12 
Size:	32.9 KB 
ID:	28393
    Well figured out the code works fine for a *accdb but it won't work for the *.mdb (2002-2004) which unfortunately is how this database is as ARCGIS doesn't support the *accdb format. So I we are stuck using the *.mdb.

    I looked at the test code, thanks for that and the explanations and it worked, however it doesn't seem to delete all the records from the tblUcase table when I run the qryLocationCase. So if I add in new records into the TBICOUNT that are in the incorrect format, the qryLocationCase still only brings up the same records it originally did and not the new ones.

    So is there any way to get something like this to work in the *.mdb (2002-2004) format?

  9. #24
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    Instead of StrCompare, InStr function may work!

    SELECT TBICOUNT.CountLocation
    FROM TBICOUNT
    WHERE ((Nz(Instr(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1),UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)),0),0)=0));

  10. #25
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Nope that SQL didn't seem to work in either database (2002-2004 *.mdb or the *.accdb). Keeps returning a "Data Mismatch in criteria expression" error.

    Though what is the Nz at the start?

    It's just too bad we are forced to use the old format as a *.mdb or else the original script would have world perfectly...

  11. #26
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    Nz converts returned Null value to desired value (in given SQL to 0), but I checked now syntax, and there is no need for it, as Instr() must return 0 (no match) or 1 (match).

    Try to run a query on your table where instead of where clause you get calculation results - starting from components and adding complexity step-by-step. Like:

    SELECT TBICOUNT.CountLocation, InStr([TBICOUNT].[CountLocation],":") AS X FROM TBICOUNT;

    When this is OK, then

    SELECT TBICOUNT.CountLocation, Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1) AS X FROM TBICOUNT;

    etc. So you can locate, where the problem is and you can correct it. When you get a working SQL returning 1 or 0 for match or no match respectively as X, use this expression in WHERE clause.


  12. #27
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Ok using just SELECT TBICOUNT.CountLocation, Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1) AS X FROM TBICOUNT just returns every single record, regardless if it is caps or not before the ":". So it at least returns something.

    Now adding in the UCase part is where it's not working at all with the same mismatch error and could be because I'm unsure where to put the brackets in and that might be why it's getting messed up...

  13. #28
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    SELECT TBICOUNT.CountLocation, UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)) AS X FROM TBICOUNT

    This query must return all records - there is no WHERE clause.
    The goal is to stepwise advance with X, until you get False for records where 1st part is uppercase and True for rest. Then you can use this expression as WHERE clause for actual query.

  14. #29
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    And that's where I can't get it to work is within the WHERE statement. But the SELECT TBICOUNT.CountLocation, UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)) AS X FROM TBICOUNT does return all the values as the did the SELECT TBICOUNT.CountLocation, Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1) AS X FROM TBICOUNT

  15. #30
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    So what about

    SELECT
    TBICOUNT.CountLocation,
    Instr(UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)),
    Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1),0) AS X
    FROM TBICOUNT

    And then

    SELECT
    TBICOUNT.CountLocation,
    (Instr(UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)),Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1),0)=0) AS X
    FROM TBICOUNT

    Thelast one must return True or False for X.

Page 2 of 3 FirstFirst 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