Access query to differentiate between alpha and numeric invoice #

[Guest]

I have a query that looks at the invoice field of a table, and based on the
data in that field, for each transaction type "DI", changes the transaction
type to "Monthly Billing @@", @@ being a number from 01 to 99. The invoice
field will either contain a number sequence, from 001 on, or it will contain
a 6 character text string denoting the first billing invoice. If it contains
the text string I want to change the transaction type to "Monthly Billing
01". I already have the query working as far as the numeric invoice numbers,
it takes the two right-most numbers in the invoice number and appends
"Monthly Billing " to the front of it, adding 1 to the number because the
billing statement 001 is actually the second billing, etc. But when the
invoice number is text, I get something like "Monthly Billing XJ" or whatever
the last two characters of the invoice are. How do I get Access to see the
text and update the information accordingly?

 

[PC Datasheet]

A start toward a solution might be to consider your statement:
"....being a number from 01 to 99"
01 to 09 ARE NOT numbers although both are digits. 01 to 09 are strings!!
Also consider your statement:
".... contain a number sequence, from 001 on...."
001 to 099 ARE NOT numbers although the three are digits. 001 to 099 are
strings!!

 

[Michel Walsh]

Hi,


I would have use two different fields, in the table, and merge them, for
report/display purposes: Field1 & " " & Field2

where, as example, Field1 is the company name, and field2 the numerical
"counter". 1+DMAX( "Field2", "tableName", "Field1=""" & CompanyName &
"""" ) would increment the previous number, by one.


Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

I didn't really think of it that way, although they are being treated as
strings already in my current query, so that wouldn't make a difference
anyway. That still doesn't solve the main problem however. How do I get
Access to see that if the invoice is a AA0AAA format it needs to update it to
read "Monthly Billing 01"? I would assume it would be a "Like aa0aaa"
function, but I haven't been able to make that work.