Results 1 to 12 of 12
  1. #1
    Nip351 is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2017
    Posts
    6

    MS Access 2003 - Code, using Left(Field.Value,2) Partial Field Value

    I need to compare the 1st two characters of a vehicle VIN in a report. Basically, if it starts with "1F", then I want the images property visible to TRUE.



    So, the VIN is 17 characters, but I want to only evaluate the 1st two.

    This is what I have, but regardless if the VIN is 1F or not, it never shows. It's default visible property is false, so if the VIN starts off with 1F, set Visible to TRUE.

    Here's what I have:

    Private Sub Form_Current()
    If Left(VIN.Value, 2) = "1F" Then
    Me.Image149.Visible = True
    End Sub

    I also tried to set visible to true and added:
    Else
    Me.Image149.Visible = False

    I've done this before, but only with Yes/No type controls. Perhaps I have the function 'Left' incorrect? I received no errors.

  2. #2
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Code:
    Private Sub Form_Current()
    If Left(Me.VIN.Value, 2) = "1F" Then
    Me.Image149.Visible = True
    Else
    Me.Image149.Visible = False
    End If
    End Sub
    This works fine for me. Proof of concept attached

    Picture Visible Textbox Left Criteria.zip

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Put a breakpoint on the If statement and then step through to see if it getting to the me.Image49 line. If it does, then something is wrong with the Image49 line. Make sure your data does have a VIN with 1F.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    instead of

    If Left(Me.VIN.Value, 2) = "1F" Then
    Me.Image149.Visible = True
    Else
    Me.Image149.Visible = False
    End If

    slightly simpler code

    Me.Image149.Visible = Left(Me.VIN, 2) = "1F"

    note code will fail if the length of VIN is less than 3 characters. I assume this field is always populated, if not you will need to protect against it

  5. #5
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by Ajax View Post
    instead of

    If Left(Me.VIN.Value, 2) = "1F" Then
    Me.Image149.Visible = True
    Else
    Me.Image149.Visible = False
    End If

    slightly simpler code

    Me.Image149.Visible = Left(Me.VIN, 2) = "1F"

    note code will fail if the length of VIN is less than 3 characters. I assume this field is always populated, if not you will need to protect against it
    Never thought about coding it that way. Love those short 1 liners. To protect against zero length VINs

    If Len(Me.VIN) > 0 Then Me.Image149.Visible = Left(Me.VIN, 2) = "1F"

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    actually

    you would need

    If Len(nz(Me.VIN)) >= 2.....

    the nz to protect against nulls, and the 2 to protect against the field only having 1 character

  7. #7
    Nip351 is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2017
    Posts
    6
    Thanks! There are plenty of 1F's, I was using that to test because of their popularity.

    The VINs are always populated and correct as they are previously decoded.

    I used what you provided and works great. I like the one liner because there will be more to evaluate per record.

    Thank you everyone who replied.

  8. #8
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    Quote Originally Posted by Ajax View Post
    actually

    you would need

    If Len(nz(Me.VIN)) >= 2.....

    the nz to protect against nulls, and the 2 to protect against the field only having 1 character
    You win this one Ajax... Until next time!


  9. #9
    Nip351 is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2017
    Posts
    6
    I wanted to reply to this thread as this is a continuation. I have all the logos displaying correctly per manufacturer based off the partial VIN as previous shown. Now, while testing most records, they were all displaying images correctly. This report prompts for a partial VIN as we always just want to view a single record (VIN/vehicle), but now when I run it and enter a known partial VIN I get the correct record details but no image displays for any record. But if I again run it and don't enter a parm I get all records and all have correct images. My parm is Like "*" & [Partial VIN] & "*"

    I'm thinking I need to say something along the lines of IF EXIST, do this....stop evaluating, otherwise evaluate next (else)...would that be correct? I'm just not sure how that would be laid out.

    Here is a small snip of what I have...

    Private Sub Report_Page()
    Me.Ford.Visible = Left(Me.vin, 2) = "1F" Or Left(Me.vin, 2) = "2F" Or Left(Me.vin, 2) = "3F"
    Me.Dodge.Visible = Left(Me.vin, 2) = "1B" Or Left(Me.vin, 2) = "1D" Or Left(Me.vin, 2) = "1B"
    Me.Chrysler.Visible = Left(Me.vin, 2) = "1C" Or Left(Me.vin, 2) = "1A" Or Left(Me.vin, 2) = "2A"
    Me.GeneralMotors.Visible = Left(Me.vin, 3) = "1GT" Or Left(Me.vin, 3) = "1G8"
    Me.GoNutsOverAccess = Right(Me.InRiver,1)
    End Sub


    @Access Power
    Dr. Claw gives zero $h1t5...
    No pet carrier
    No rear view mirror
    No seat belt
    Wears same clothes every episode
    Probably has no legs and only consists of an arm as you would see legs if he really had legs.
    he'd be much cooler if he had dingo balls hanging from the mirror, or at the very least fuzzy dice.
    Finally, what does his people/employees think of him when he decided he needs a pet....cat.
    Sometimes I think cartoons aren't even real. At least I can always fall back on the Easter Bunny.

  10. #10
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    When you open your report, you could open it with a filter:

    DoCmd.OpenReport "rptVINs", acViewReport, , "[VIN] Like '*" & [Partial VIN] & "*'"

    That would bring up just the VINs that match your partial, and stop after that.

    Side Note: I agree. Dr. Claw... super creep.

  11. #11
    Nip351 is offline Novice
    Windows 10 Access 2003
    Join Date
    Feb 2017
    Posts
    6
    So...I'm retarded. I applied your suggestion, and I get the same results. Only this time no matter that I do, I have no pictures.

    Turns out, I had cleaned my desktop and deleted all images....hence no images in the report, which means it's working as (un)intended.

    I need to embed those images since I don't want them forever linked as they are permanent and do not change per associated partial VIN.

    It's always something stupid, and sometimes including the user...


    Thanks again!

    I blame the Dr.!

  12. #12
    AccessPower's Avatar
    AccessPower is offline Competent Performer
    Windows 10 Access 2013 64bit
    Join Date
    Oct 2016
    Posts
    165
    We can all blame the doctor.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 12-28-2016, 11:39 AM
  2. Use Partial field value as criteria
    By cplmckenzie in forum Reports
    Replies: 6
    Last Post: 02-06-2015, 01:14 AM
  3. Replies: 4
    Last Post: 12-14-2014, 01:41 PM
  4. Replies: 5
    Last Post: 08-18-2012, 03:52 PM
  5. Search for partial field
    By j_a_monk in forum Queries
    Replies: 3
    Last Post: 06-08-2011, 08:40 AM

Tags for this Thread

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