vba code for a query

  • Thread starter Thread starter Bob sg
  • Start date Start date
B

Bob sg

I need a VBA code to put an A, B, C, etc, after the invoice number for each
of the invoice numbers that are the same number in a query. The format for
the invoice number under the table is text. All the other data in the table
would be the same data for each line. This is because we sold more than one
of the same product and is referenced on a separate line. I only want this to
work on queries that I specify and not all of them (because some invoice
numbers don't need to have the letter after it). I will also need to know
were to input this vba code in Access since this is new to me. Hope you can
help me out on this.
Thanks
 
A possible solution is to count the number of records which have the same
orderID, but which have a lowest value of the primary key (not orderID since
orderID has possible duplicated occurrence), and then, if that count > 0,
change that count to a letter using CHR: CHR( theCount + 64)


SELECT PrimaryKey,
orderID,
DCount("*", "tableNameHere", "orderID=" & orderID & " AND primaryKey<"
& primaryKey) as theCount,
iif( theCount =0, orderID & "", order & Chr(64 + theCount) )
FROM tableNameHere



I assumed primaryKey and orderID fields are both numerical (not
alphanumerical).


In Northwind, you can try and see the result with:

SELECT [Order Details].OrderID,
[Order Details].ProductID,
DCount("*","Order Details", "OrderID=" & [OrderID] & " AND ProductID<"
& [ProductID]) AS theCount,
iif(theCount=0, orderID, orderID & CHR(64+theCount)) As theOrderABC
FROM [Order Details];



and while productID is NOT a primary key, it works since, in fact, it is
enough that orderID AND the other field, here ProductID, makes, together,
something without duplicated occurrence.



Vanderghast, Access MVP
 
Back
Top