Re: Formatting Expression Box Formula's

adamrharding — Thu, 04 Oct 2007 14:22:23 GMT

I have just tried this by putting values of 00001 A 1/1/07; 00002 B 2/1/07; 00003 C 3/1/07 and 00004 D 4/1/07 into a three column database as you suggested

I added a query field, a submit button, and an expression box with your function. The problem lies with you treating @Claim_Number as an integer when you max() it or even when you + 1, or even + 00001 as such it displays just that, it as a number.

Take off the max() and the + 1 and the correct text value is displayed in full.

If, as I am guessing this is to find the next available number then maybe make @Claim_Number the primary key and have it auto-increment. Just asking but why does the field need to be formatted with the leading "0000" ?

Cheers Adam

Formatting Expression Box Formula's

Monte Cristo — Fri, 07 Sep 2007 13:11:27 GMT

Hi,

I have a database with one table, it has three coloumns, (1) a simple number format 0001, 0002, 0003 & so on....[text format in access] (2) Name [text format in access] (3) date [date format in access]

So I have connected this database as my main connection and this all works.

I have an expression box which queries the database coloumn (1) but the expression box returs the value without the 0's so all I get is 3, 4, 5 etc.

This is the formula in the expression box: concat(Year, "-", ProductLine, "-", max(@Claim_Number) + 1)

BTW - @Claim_Number is the one querying the database coloumn (1)

Can anyone please tell me how I can return the values of coloumn (1) with the leading zeros? I have tried to change the format of the coloumn in access to a number (long integer) still not returning the values with the leading zero's.

Thanks,

SQL Database Integration

Re: Formatting Expression Box Formula's

Formatting Expression Box Formula's