Dmax criteria to limit only NUMBERS pulled from field that also may contain ALPHA

[gps]

I'm trying to use Dmax to dip into a my invoice header table and pull the maxium invoice number out of it.

I'm running Access 2007 on Vista Business, but had the same prob with Access 2003 on XPP.

Here's a snapshot of the table data:
InvoiceID
79571
79572
ACCT
ACCT
CREDIT
79572
79573

Here's the code I'm using:

MaxInvoiceID = DMax("[InvoiceID]", "Invoice")

MaxInvoiceID ends up being "CREDIT"

I would like Dmax to ignore alpha and only process numeric data.

I.E. MaxinvoiceID = 79573

Then I can add 1 to it and generate my new invoice number.

The SQL to do this seems to be:

SELECT Invoice.InvoiceID
FROM Invoice
WHERE (((Invoice.InvoiceID) Like "*#"));

The above SQL generates an alpha-free output:

InvoiceID
79568
79569
79570
79571
79572
79573

Sooooo, simply drop in the SQL "Where" criteria at the end of Dmax statement, right? Well, I can't seem to get it to work.

Here's what I'm running:

MaxInvoiceID = DMax("[InvoiceID]", "Invoice", "InvoiceID" Like "*#") 

<------wrong use of quotes or brackets?

When I run the code it complains of, "invaid use of Null."

Please help!

[Rabbit]

I'm trying to use Dmax to dip into a my invoice header table and pull the maxium invoice number out of it.

I'm running Access 2007 on Vista Business, but had the same prob with Access 2003 on XPP.

Here's a snapshot of the table data:
InvoiceID
79571
79572
ACCT
ACCT
CREDIT
79572
79573

Here's the code I'm using:

MaxInvoiceID = DMax("[InvoiceID]", "Invoice")

MaxInvoiceID ends up being "CREDIT"

I would like Dmax to ignore alpha and only process numeric data.

I.E. MaxinvoiceID = 79573

Then I can add 1 to it and generate my new invoice number.

The SQL to do this seems to be:

SELECT Invoice.InvoiceID
FROM Invoice
WHERE (((Invoice.InvoiceID) Like "*#"));

The above SQL generates an alpha-free output:

InvoiceID
79568
79569
79570
79571
79572
79573

Sooooo, simply drop in the SQL "Where" criteria at the end of Dmax statement, right? Well, I can't seem to get it to work.

Here's what I'm running:

MaxInvoiceID = DMax("[InvoiceID]", "Invoice", "InvoiceID" Like "*#") 

<------wrong use of quotes or brackets?

When I run the code it complains of, "invaid use of Null."

Please help!

Use

DMax("Val([InvoiceID])", "Invoice")

[gps]

that's the ticket, thanks!

[Rabbit]

that's the ticket, thanks!

Not a problem.

[nico5038]

Minor addition, when your alpha field contains a mixed value like:
1234ALSO
Then 1234 will be used by the VAL() function.
In such a case you can add an IIF() function to exclude these values, when you don't want them.

Success with your application !

Nic;o)