Dmax function not working 2

[monkeysee]

I have a field named SepticPermit in a table named Septic. The field will contain the actual permit numbers when issued. the number is formatted as such: 2004-001. 2004 represents the year issued, the 001 is the first number of a septic permit issued in 2004. I want the 001 to automatically increment by one each time a new permit is entered. I have this in the property, default value of the permit text box: =DMax("[SepticPermit]","Septic")+1

I just get a blank box when going to a new record. What am I doing wrong?

Also, I want to be able to use a different number (but using the same format) if needed. (In other words, when adding older information, I will be able to put in a permit number that was not issued in 2004, ie: 1989-141.

I have read all other posts regarding dmax incrementation but have not yet been able to find what is wrong.

 

[scriverb]

Let's first deal with just incrementing the number by one during the current year. We will have to figure out the change over to the new year later. Put the following in the Default Value of your field:

=Year(Date()) & "-" & Format(CInt(Mid$(DMax("[SepticPermit]","[Septic]"),6,3))+1,"000")

This should increment the permit number by one when a new record is initiated.

Post back with the results.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

I think the following will handle the new year starting and "001" situation.

=IIF(Year(Date())=CInt(Mid$(DMax("[SepticPermit]","[Septic]"),1,4)),Year(Date()) & "-" & Format(CInt(Mid$(DMax("[SepticPermit]","[Septic]"),6,3))+1,"000"), Year(Date()) & "-001")

Let me know the rsults.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[monkeysee]

Bob,

The first code, WORKED!!!!
and so did the second!!!!

Thanks a bunch! You're the greatest! I have pondered over this now for 2 days! I am planning on taking a VB class this fall, so perhaps, I will be able to figure more of this stuff out on my own. But I'm glad you are there helping me and others!

 

[scriverb]

Great!!! Good luck with classes in VB.

Thanks for the Star. It is appreciated.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[stillwillyboy]

Bob, I couldn't get this to work. I put the code in the default value property for the invoicenumber field in the ar--invoicenumberlist table. I've checked my spelling.

I tried the following:

=DMax("[InvoiceNumber]","[ar--InvoiceNumberList]")+1
and
=Format(DMax("[InvoiceNumber]","[ar--InvoiceNumberList]"))+1

I keep getting the messages: "Unknown function 'DMax' in validation expression on 'ar--invoicenumberlist.invoicenumber'.

TIA. Bill

 

[scriverb]

I don't see anything wrong with your code with the info that I have at my disposal. Why don't you Pkg up your db and email it to me for a look. Just include the table with a small number of records and the form. See my email in my profile. I will take a quick look and see if we can resolve this. it should work that same as the one listed above in this thread.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

I have received you db and the following was modified. You had your expression in the wrong property. Actually you had it listed under Status Bar text. The expression should go in the Default value property. Also, the domain name was misspelled. The following was put in the Default value property:

=DMax("[InvoiceNumber]","[InvoiceNumberList]")+1

YOU HAD THE FOLLOWING
=DMax("[InvoiceNumber]","[[red]ar--[/red]InvoiceNumberList]")+1[/code

The table/domain name of the file was improperly identified.  By removing the red code above the correct Invoice number now populates the field/control when a new record is started.  I also set the Locked property to Yes and the TabStop property to False.  This keeps the user from having the ability to enter or modify the computer generated invoice#.

This should work for you now. 

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[scriverb]

Sorry about the code above. Here it is:

=DMax("[InvoiceNumber]","[InvoiceNumberList]")+1

YOU HAD THE FOLLOWING
=DMax("[InvoiceNumber]","[[red]ar--[/red]InvoiceNumberList]")+1

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[stillwillyboy]

Bob,

I copied and pasted your code into the Default Value property of the InvoiceNumber Field of the InvoiceNumberList table. Data type of the the field is Number, Integer. I get the same error message as before.

I created a new table and put in a field also named InvoiceNumber. It caused the error message also.

Is there a circular reference type of problem going on here?

I don't see the property "Satus Bar Text". Maybe I'm in the wrong area?

Thanks.

 

[scriverb]

The code above is included in the sample database that I have returned to you. You should look at the Default Value property of the Invoice Number control on the form. Also the Status Bar Text property is just down from the Default value 5-6 slots. No need to put an equation there. That is just a text string that shows up when the focus in that control.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]