Update Query - Expression Needed

CharlesWhiteman

Registered User.
Local time
Today, 23:42
Joined
Feb 26, 2007
Messages
421
Hi All, In My Db I use the following code which generates a unique account number but i need to find a way to achieve the same result in an update query or find best practice to achieve. My current way does the job when manually entering one company at a time but now i need a automated way.

I can import my spreadsheet of companies into a new Tbl and then to an append query to merge them into the main Tbl but need the account number to be generated too.

Chars & Format(Replace(Nz(DMax("[AccountNumber]", _
"tblDatabase", "Left([AccountNumber],2)='" & _
Chars & "'"), 0), Chars, "") + 1, "##000")
 
Don't use a query, use VBA, then you can still use your code. Create a procedure that loops through a recordset based on the new table. For each record, you can put
Code:
set db = currentdb
set rs = db.openrecordset("newtable")

do until rs.eof
    newcode = Chars & Format(Replace(Nz(DMax("[AccountNumber]", _
         "tblDatabase", "Left([AccountNumber],2)='" & _
         Chars & "'"), 0), Chars, "") + 1, "##000")

    doCmd.runSQL "insert into tlbDatabase (CompCode, compName, whatever) VALUES '"& newcode & '", '" & rs!CompanyName & "', " & rs!whatever & "')"

    rs.movenext
loop
the above code only has the section relevant to you post

HTH,
Chris
 
Thanks Chris, In looking at other posts decided that VBA is the way. I've been looking at doing a SQL and Update and using an StrString in the code to generate the account number. I've not looked at you suggested method before and will investigate that first. Thanks.
 
Charles,

it is possible to do it with an SQL query but the syntax would be pretty comlicated, especially if you are not that experienced. It is simpler with VBA.

Chris
 
Hi Chris, I'm using the following code which is great except all the account numbers are 001

Whereas if the company name is UK Manufacturing then the account number should be UK001 except if there is a company called UKSL already in the main TblDatabase in which case the account number should be UK002

Private Sub CmdImportRoutine_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("SourceTbl")

Do Until rs.EOF
AccountNumber = Chars & Format(Replace(Nz(DMax("[AccountNumber]", _
"tblDatabase", "Left([AccountNumber],2)='" & _
Chars & "'"), 0), Chars, "") + 1, "##000")

DoCmd.RunSQL "insert into TblDatabaseTemp (AccountNumber, CompanyName) VALUES ('" & [AccountNumber] & "', '" & rs!CompanyName & "')"

rs.MoveNext
Loop
End Sub
 
hi Charles,
II see the problem. You have not given chars a value. I left that bit out because I did not know how you worked it out adn assumed you would add it yourself. I think if you add the following line just after Do until rs.EOF it should work:
Code:
chars =left(rs!CompanyName, 2)

I'm also assuming that "sourceTbl" is the table imported from Excel.
Another important thing. notice that you create the AccountNumber from the tblDatabase table, then you insert into the tblDatabaseTemp table. For the code to work you need to use the same table in both expressions. for safety's sake. copy your original tblDatabase to tblDatabaseTemp, and use tblDatabaseTemp in both expressions. If it works ok, then use tblDatabase in both (after backing them up, of course).

HTH,
Chris
 
Last edited:
To clear things up a bit. for the purpose of the following code, tblImport is the table imported from Excel, tbldatabase is the real company table, tbldatabaseTemp is a copy of tblDatabase:
Code:
Private Sub CmdImportRoutine_Click()
Set db = CurrentDb
Set rs = db.OpenRecordset("tblImport")

Do Until rs.EOF
chars = Left(rs!companyname,2)
AccountNumber = Chars & Format(Replace(Nz(DMax("[AccountNumber]", _
"tblDatabaseTemp", "Left([AccountNumber],2)='" & _
Chars & "'"), 0), Chars, "") + 1, "##000")

DoCmd.RunSQL "insert into TblDatabaseTemp (AccountNumber, CompanyName) VALUES ('" & [AccountNumber] & "', '" & rs!CompanyName & "')"

rs.MoveNext
Loop
End Sub
if this works, then replace tbldatabaseTemp with tbldatabase and you are up and running.

Also, if that works, would you humour me and try this code to work out the AccountNumber - if it does not work for you, then ditch it by all means:
Code:
AccountNumber = Chars & Format(DCount("*", _
"tblDatabaseTemp", "Left([AccountNumber],2)='" & _
Chars & "'") + 1, "000")


HTH again,
Chris
 
Last edited:
Hi Charles again,

I have nearly managed to create an SQL query that would do what you want. It works fine with the condition that tblDatabase (the table begin written to) is empty. Also, it assigns the Account Numbers in alphabetical order, i.e. ABC Ltd would get AA001 then AZZ Ltd would get AA002. Thus it is not perfect. You could tweak it to use an entry date or Id number as the criteria. Anyway, here is the code:
Code:
INSERT INTO tblDatabase
SELECT t2.CompName, UCase(Left(t2.compname,2)) & Format((select count(*) from tblImport as t1 WHERE 
t1.compname <= t2.compname and left(t1.compname,2) = left(t2.compname,2)),"000") AS AccountNumber
FROM tblImport AS t2;

or if tblImport had an incrementing Id number, you could use that instead:
Code:
INSERT INTO tblDBNew ( CompName, AccNo )
SELECT t2.CompName, UCase(Left(t2.compname,2)) & Format((select count(*) from tbldatabase as t1 WHERE 
t1.id <= t2.id and left(t1.compname,2) = left(t2.compname,2)),"000") AS AccNo
FROM tblDatabase AS t2

I've posted this mainly in the hope that someone more experienced and better at SQL than me will be able to improve my code so that it will work if there are already entries in tbldatabase, i.e. if a company already has a acc no of AN001, then the import query take that into account and start at AN002.

HTH,
Chris
 
hi,

I've worked it out! this new query will take into account any old Account Numbers and continue the sequence accordingly:
Code:
INSERT INTO tblDatabase (Compname, AccountNumber)
SELECT t2.CompName, UCase(Left(t2.compname,2)) & Format((select count(*) from tblImport as t1 WHERE 
t1.compname <= t2.compname and left(t1.compname,2) = left(t2.compname,2)) + (select count(*) from tbldatabase as t3 where left(t3.compname,2) = left(t2.compname,2)),"000") AS AccountNumber
FROM tblImport AS t2

Do you see, though, what I mean about the query being complicated?

Chris
 

Users who are viewing this thread

Back
Top Bottom