A problem with access I'm not sure i can solve.

[BMan111]

Ok, bear with me, I've never posted to one of these boards before.

What I'm doing is consolidating/normalizing a list of project where I work. My boss is wanting it to bring up the new project number automaticaly. It is comprissed as follows ###-##-##, where the first ### is a sequentialy assigned number, the second ## is the month, and the last ## is the year.

Is there a way to make this happen? I'm kinda a summer intern type thing and would really like to keep this job after the summer is over, so getting this done is important to me (not uber-important, but important none the less). Any help anyone can provide will be an assett.

Thanks

B Man

[BMan111]

One thing i did try was to enter the following into the default value on the data entry form, but for some reason its not holding onto it when i switch from the properties pane to the form view:
= ###/ -mm( [Todays_date]![Todays_date] )/ -yy( [Todays_date]![Todays_date] )

The todays_date field it is referencing is a one field table that uses the date function to get todays date. Does it sound like i might be onto the right track and if so where do i need to go, or is this system hopeless?

Thanks for any help in advance.

B Man

[andi_g69]

Try this:
1) Create the Function below in any VBAmodule
2) Set the default value of your field to = getPNumber()
3) The Function assumes that your project table is called tblProjects, and the field which holds the project number is called pNumber; Adjust as required

Public Function getPNumber() As String

Dim oDB As Database
Dim oQDef As QueryDef
Dim oRS As Recordset
Dim sSQL, sMonth, sYear, sDatePart, sPnum As String
Dim dDate As Date

dDate = Date ' Get Current Data into a variable
sMonth = Right(100 + DatePart("m", dDate), 2) ' Get Month with leading zero
sYear = Right(DatePart("yyyy", dDate), 2) ' Get 2-Digit year
sDatePart = "-" & sMonth & "-" & sYear ' Construct the datepart of the Project Number
sPnum = "001" ' Initialize Project Number

' Build a SQL String to select the highest Project Number which exists for the current month
sSQL = "SELECT MAX (pNumber) AS MaxPNumbernumber FROM tblProjects WHERE pNumber LIKE ""???" & sDatePart & """"
Set oDB = CurrentDb
Set oQDef = oDB.CreateQueryDef("", sSQL)

Set oRS = oQDef.OpenRecordset()

If Not oRS.EOF Then ' If any record returned
If Not IsNull(oRS.Fields("MaxPNumbernumber")) Then ' If there is a record for the current month
sPnum = Right(Left(oRS.Fields("MaxPNumbernumber"), 3) + 1001, 3) 'Get the ### part of the Project Number and increase by 1
End If
End If

sPnum = sPnum & sDatePart ' Add the current datepart onto Project Number

getPNumber = sPnum

' Cleanup
oRS.Close
oQDef.Close
Set oRS = Nothing
Set oQDef = Nothing
Set oDB = Nothing

End Function

[BMan111]

I tried to do that, or somethign similiar, unfortunatly though, the machine I am forced to work on *shakes fist in anger at the POS he's stuck with* does not have the abaility to use the VB script that that solution requires, sorry, I forgot to mention that origionaly. I did find a way to calculate the number with a query, and believe me, it was a lot of fun. Now my problem is that, since the query isnt linked to the input form, I'm having major difficulty linking the default value field to the field with the number in it. Any hints or tips?

Brandon-'not-to-sure-how-he-got-his-job'

[BMan111]

Just for anyone who is interested, he is how I solved my problem w/o having access to VBA:

I started a query that found the most recent(MAX) project number. Then, in a new field named New Project Number, I used a VERY big, VERY annoying equation to just figure everything in one single field. It basicly just bumps the 3rd character in the string up by one if the year is the same, but if the year has gone up, which is when we reset our project numbers, then it reset the first part of it to 001. Regardless of which of these two it did, it then added the "mmyy" stamp on to get the last 4 digits of the number.

Here is the equation if any of you happen to be interested:

New_Project_Number: Max(IIf(Format(Date(),"yy")>Right([Project_Number],2),(Format(1,"000") & Format(Date(),"mmyy")),(Format(Left([Project_Number],3)+1,"000") & Format(Date(),"mmyy"))))

I hope at some point someone may find that usefull, though I sincerly doubt it :P .

Brandon