Page 3 of 3 FirstFirst 123
Results 31 to 45 of 45
  1. #31
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Both return all the records and an #error for all values of X in the X Field.



    The SELECT TBICOUNT.CountLocation, UCase(Left([TBICOUNT].[CountLocation],InStr([TBICOUNT].[CountLocation],":")-1)) AS X FROM TBICOUNT would actually have the values in the X-field as a the text before the ":" as it should. So something in the last code is erroring out in my query now. The query runs, it's just looking at the results is where the #error shows up for every record...

  2. #32
    Join Date
    Apr 2017
    Posts
    1,673
    Hi

    https://access-programmers.co.uk/for...d.php?t=116821

    Its fixed

    Many thanks to PDX_MAN.

    Problem: Microsoft Office Access 2003 error message when running a query:

    'Undefined function "UCase$" in expression'

    Solution: Went to VB/Tools/References. Got MS error message:

    "... missing or broken reference to circ3.ocx ...."

    I unchecked the Reference line for that file and hit OK.

    When I went back to the Access application, it worked fine.

    Thanks again, PDX_MAN, for the direction.

    Gary

  3. #33
    Join Date
    Apr 2017
    Posts
    1,673
    In general, in older versions of access many functions don't work by default - you have to activate right references.

    The problem is, often in Access help is nothing about those references, you have to search web for them. And with so many years past, you usually get info for newer versions only even when you ask for specific version.

    Today I searched for string comparision in Acess 2000 VBA - most of answers were about StrComp function.

  4. #34
    Join Date
    Apr 2017
    Posts
    1,673
    Another possible cause and solution I did find when googling:


    Rick Rothstein, MVP - Excel replied on December 31, 2010
    • MVP




    If other VB functions work, but that one doesn't (which I am assuming is the case), it usually means there is something out there somewhere (variable, subroutine name, etc.) with that same name. While it would be best to track that down and change it so it avoids the conflict, you should be able to circumvent the problem by changing that line of code to this...
    Target.Value = VBA.UCase(Target.Value)



  5. #35
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I'm getting behind - and lost.
    however it doesn't seem to delete all the records from the tblUcase table when I run the qryLocationCase.
    Because it's not a delete query.
    And you said you knew how to run the procedure without using a form (which you didn't want), which is what you have to do to update the table. What I posted WILL work in any version, although you might have to update references to suit the older version (and I think you need a reference to DAO as well). I presume you know how to do that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #36
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    I was thinking the not using the form was to not call on the function within the Form (which I don't at this time). I was just thinking it would be an independent query I would run (that wouldn't affect what the user typed at all) just to display the results, and not fix them. That's where I was getting confused.

    Anyways seems that wasn't what you were referring too. As I mentioned, this is getting over my comprehension of Access.

    As for the references, I was looking at anything possible, but wasn't finding anything. The Ucase seems to work in the one example, so that can't be the issue. But yes as you stated ArviLaanemets, it could be one of many different references that need to be switched on.

    Instead, I am going to try and get things updated here instead so that we can use the *.accdb with our GIS software instead. It seems I just need to get the Microsoft Office 12.0 Access Database Engine Provider and then I will hopefully get that to work in conjunction with the software and if so, then the original codes that you guys have helped me with previously should work just fine. I hadn't thought about that issue of this being on an older version as we don't use it that much and many things haven't been updated for a long time (which is what I'm doing with the user Form). And no I'm not the database guy here, I'm just a GIS user who knows how to do create simple/standard forms and such as I realized before people were entering in the data straight into tables by hand...

  7. #37
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Put a button on your form to run the code and it should fix the problem as long as you hsve set the required references. It seems you have, otherwise you would have reported an error in your other post. Old records will then get deleted.

  8. #38
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Ok thanks, for both of your help. I really do think getting Access update is something that needs to happen anyways and we won't have to all this work. But for the mean time I tried out your test database and called the module with a Form Button and it works perfectly.

    So I will just add this in for now and when I can this database (and others using *.mdb) to *.acccdb then that will make things easier and I can just use the previous codes.

    Thanks again. Didn't realize it was going to be this complex, but guess that's what happens using older formats...

  9. #39
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    With all due respect, it wasn't all that complicated as you had the answer back in about post #11. What I had would work in any db all the way back to 97 I'm sure - as long as the required references were set as previously noted. For some reason, this one just kept going off the rails. Good luck, and do a comparison on the features between what you have and what you'll end up with if you plan to update. It's not always a good decision AFAIC.

  10. #40
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Well it seemed a bit complex, probably because of everyone trying to help and not realizing the version I was on.

    Anyways tried to use it on the actual database now with the real TBICOUNT table (6400 records or so) and getting a "Error #94 Invalid Use of Null". So there's something in one of the other categories (I sent you the toned down version) that is causing this issue. So I'm just going to go through taking up field by field to see which one it is failing on and then see if I can take it from there...

  11. #41
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Update got it all fixed and working now. And looks like we'll be using that method instead as we would have to update a few users to Office 2016 64bit while the rest of the office was on the 32bit (due to stability). So I opted to just remain on the 32bit to make it of an issue.

    There were actually 2 errors in the data. Blank locations caused the Null error which I didn't realize it would cause in that script (but that's fine as it is a valid error that needs fixed) and the 2nd was an "argument" error and those were 2 records that were actually missing the ":".

    So I have a check before that in the form that will flag those instance now and everything is working and good to go. So thanks once again for walking me through all this and helping understand it a little better (the notes in the module helped a lot)...

  12. #42
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You're welcome. Just letting you know that I don't know what this means
    (I sent you the toned down version) that is causing this issue.

    Interesting comments, because GEOID is pk field in the table you provided, so not sure how you manage to have Nulls. The best solution is to prevent Nulls. One other option would be to include the ObjectID field (assuming that one is never Null) in the temp table and join on that instead, unless somehow you have nulls in that one too. Then you could place an alternate value in the temp table (such as "BAD DATA") in the GEOID field and find the record easily via the ObjectID. The error is caused by trying to subtract 1 from 0 (resulting in -1) and trying to use -1 on the Left function.

  13. #43
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Oh I only sent you 3 fields from the table. The actual table has about 20 other fields, but figured I only needed help with the one field. The actual Count Location has nulls sometimes if someone enters in a new GeoID on the form and then doesn't enter in any other data. Sometimes a person might start a new location and get side tracked by something else and once the GeoID gets entered, it's in the TBICOUNT table regardless.

    So that would be why there could be nulls, but very rarely. So not something that's an issue and it will now get caught by a previous check that I have so if run and fixed before this new check (UPPERCASE) is done then it will run without any issues...

  14. #44
    Join Date
    Apr 2017
    Posts
    1,673
    Quote Originally Posted by pjordan@drcog.org View Post
    Sometimes a person might start a new location and get side tracked by something else and once the GeoID gets entered, it's in the TBICOUNT table regardless.
    Create for form based on TBICOUNT table a BeforeUpdate event, with code

    Cancel = (Nz(GeoID,0)=0)

    The expression on right side returns True when no GeoID is entered or when it is 0. As result the operation triggering record saving is canceled. The user must enter some nonzero value for GeoID or press Esc and drop the new record altogether.

  15. #45
    pjordan@drcog.org is offline Advanced Beginner
    Windows XP Access 2003
    Join Date
    Dec 2011
    Location
    Denver, CO
    Posts
    71
    Sorry I wrote the above in reverse. They enter in a new GeoID and not a Count Location. GeoID has to be entered or it won't save the data into the TBICOUNT table. It's when the Count Location is left blank, not the other way around. Either way that is fixed anyways with my previous check I have in place...

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