Using The Like function with data from a form

[Aaron Greatbanks]

Hello

I have a ICT exam on databases in January for A2 ICT. It is based on a sixth form college

I need to add new units for the courses via a form, with the unit being uniquely identified using a Unit Code

The Unit Code consists of the first 3 letters of the Subject Code, the first 2 letters of the Description of the unit and a number.

Eg Subject ID : ARA001 and Description: Culture will have a Unit code of ARACU1

I'm trying to find a way to automatically create the unit code on the form.

The first two bits have been solved fine,however, i have had difficulty of putting the number of the end.

It seems easy in practice as i need to find a Unit Code like the one i have on the form but find the maximum number of a identical code and add one to it

I thought if i did

Like "[Forms]![frmaddnewunit]![txtUnitCode]*" as the criteria on a query with the field being Unit Code from the Unit table it would find data like the one on the form but it doensn't. I thought i would use the data from the query and use Max(Right([UnitCode],1))+1 to get the number and add this to the code in the unit

This formula on the unit code text box on the form solves the first two bits of the problem

=Left([cboSubjectID],3) & UCase(Left([txtDescription],2))

but i need to find a way to find unit codes thsat exist similar to the ones that are created and then find the maximum number and add one to it so i have a valid unit code

e.g

if on the form i select Subject ID ARA001 and Description Culture

The unit code will display ARACU.

I then need to find unit codes similar to this (There is one that already exists ARACU1) and find the max number on the end and add one to it

So ideally the code that will be automatically generated in this case would be ARACU2

How do i do this?

Thanks

 

[Catalina]

Duplicate post, also in Queries.

 

[Vassago]

Please don't double post.