468,272 Members | 2,202 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

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

2
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:
Expand|Select|Wrap|Line Numbers
  1. 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!
Apr 8 '07 #1
4 3761
Rabbit
12,511 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. DMax("Val([InvoiceID])", "Invoice")
Apr 8 '07 #2
gps
2
that's the ticket, thanks!
Apr 8 '07 #3
Rabbit
12,511 Expert Mod 8TB
that's the ticket, thanks!
Not a problem.
Apr 8 '07 #4
nico5038
3,079 Expert 2GB
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)
Apr 8 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by nrtyme | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.