Access 2010 IIF and statement

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

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
 

Attachments

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?
 

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
 

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?
 

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.
 
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;
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

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 807,865 other people just like you!

Latest posts

Staff online

Members online

Top