1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Access 2010 IIF and statement

Discussion in 'Business Applications' started by tecky07, Feb 2, 2013.

Thread Status:
Not open for further replies.
Advertisement
  1. tecky07

    tecky07 Thread Starter

    Joined:
    Feb 2, 2013
    Messages:
    3
    Okay here's my problem. On the attached DB on the form, when users enters one of the following Data type in 1st field I want access to show next number available for that group.

    U3-PCP-001
    U3-PCP-002
    U3-ASH-001
    U3-Ash-002

    For instance, if user enters U3-PCP (then access will populate the next number available numberical order. When user enters U3-ASH, then access will populate the next number available and so on:

    Botton line, I want access to show next number available by each group.

    Is this a iif and else statement or VBA? please help

    Thanks file attached

    terk07
     

    Attached Files:

  2. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    tecky07, welcome to the Forum.
    You may have noticed that your current numbering system does not display in alpha/numeric order, this can be fixed by formatting the numbers correctly.

    To answer your question I am pretty sure that it will takew VBA code to do what you want.
    I also need to know what you mean by "show next number available", does that mean just display the value or assign the next value to the record?
     
  3. tecky07

    tecky07 Thread Starter

    Joined:
    Feb 2, 2013
    Messages:
    3
    Hi there, Yes. I have make the numbers "00" filed to sort properly. What I want is for Access to show the next number available for that filtered section.

    During my research, I thought about this Idea, when the user clicks on the first field, a box comes up asking to enter SYSTEM Code then the user would type in U3-ASH then after hitting enter, access would automatically filter that group showing last number used. Do you think that would work? BTW I thank you for such a quick response
     
  4. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    I normally use VBA code to actually increment the number for the user completely automatically, I would have an updateable table of the prefixes and use a combo for the user to select system code. This is preferable to entering the code in case they make a typing error.
    However it can be doen as you suggest, where would you want the "next number" displayed?
     
  5. tecky07

    tecky07 Thread Starter

    Joined:
    Feb 2, 2013
    Messages:
    3
    Can my suggestion be done with a macro? or do I have to use VB.
     
  6. OBP

    OBP

    Joined:
    Mar 8, 2005
    Messages:
    19,895
    You may be able to do this with an Access 2010 Macro, but I am not familiar with them as I do not Access 2010 and I always use VBA code.
     
  7. Winne007

    Winne007

    Joined:
    Feb 8, 2013
    Messages:
    1
    I think you might solve your problem with a a couple of simple queries.

    First create a query which isolates the code from the number.

    Code:
    SELECT [U3-FIXED LADDERS MSTER].[Ladder ID-U3], InStr(1,[Ladder ID-U3],"-") 
    AS PosFirstDash, InStr([PosFirstDash]+1,[Ladder ID-U3],"-") AS PosSecondDash, 
    Left([Ladder ID-U3],[PosSecondDash]-1) AS Code, Right([Ladder ID-U3],Len([Ladder 
    ID-U3])-[PosSecondDash]) AS [Number]
    FROM [U3-FIXED LADDERS MSTER];
    
    Save it and name it qryIsolateU3

    Then create a query that returns you the highest +1 for each code:

    Code:
    SELECT qryIsolateU3.Code, Max(qryIsolateU3.Number) +1 AS NextNumber
    FROM qryIsolateU3
    GROUP BY qryIsolateU3.Code;
    
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1087966

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice