Search results

CrystalSurfer, I think the following function should do what you wanted. ' ' Custom Rounding function. ' ' To always round down, make iRoundOpt less than zero ' To always roun up, make iRoundOpt more than zero ' To round to the nearest, make iRoundOpt exactly zero ' ' for negative numbers...

put something like this in the Afterupdate event of the combobox: If combobox.value = 'printers' then txtbox1.enabled = false txtbox2.enabled = false end if don't forget to re-enabled them when you need them. HTH, Chris

eoan, to find out the value of a excel constant, go to the VBa editor in Excel, activate the Immediate window (CTRL-G), then key in (including the question mark): ?xlLinkTypeExcelLinks then press enter. This will give you the value of the constant. (This technique is true for constants from...

hi CrystalSurfer, Microsoft say Access implements Banker's Rounding, so I'm sure they do. The problem is that VBA does not take into account the problems of internal binary representation of fractions. Your 4.725 is internally probably actually4.724999999999999. So even if you add 0.5 after...

Gemma, Microsoft say that VBA uses Banker's rounding but it does not always work! try: round(3.5,0) = 4 correct for Banker's rounding round(1.35,1) = 1.4 correct for Banker's rounding round(1.035,2) = 1.03 incorrect for Bankers' rounding round(0.035,2) = 1.04 correct for Banker's rounding. you...

I don't know where you got this function but gives some very wacky and incorrect answers, e.g. vbaRound(4,2,rUp) = 4.01 (incorrect) vbaRound(4.725, 1, rUp) = 4.8 (incorrect) also, what do you want to happen with negative answers, should -4.726 rounded up to 2 decimal places be -4.72 or -4.73? It...

instead of: objXLBook.ActiveWorkbook.SaveAs Filename:= _ use: objXLApp.ActiveWorkbook.SaveAs Filename:= _ A Excel workbook does not have an Activeworkbook property. Also, unless you have added a reference to Excel, then Access will not understand xlNormal as it is part of the Excel object...

I am assuming that the controls that have a prefix of 'txt' are textboxes. If so, then you need to txtwhatever.value instead of txtwhatever.text. HTH, Chris

I don't know enough about the format in which you hold your dates in the table but there is a little trick that certain works for the confusion between some date formats. try using CLng(Date()) instead of just Date(). This should work if startdate and enddate are defined as date datatype. HTH...

remove the following code: If Len(strCriteria) > 0 Then strCriteria = " Where " & Left$(strCriteria, Len(strCriteria) - 5) End If you do not need the 'where'. HTH, Chris

Does tblTemp already exist in the other database at the time you run the query? If it does then of course you will get the error, you could check if it exists first, then delete it if it does: Dim DB As Database dim tdf as tabledef Set DB = OpenDatabase("MyDataBase.mdb") on error resume next...

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

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...

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: Private Sub CmdImportRoutine_Click() Set db = CurrentDb Set rs = db.OpenRecordset("tblImport") Do Until...

Could you post the relevant part of the DB?

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: chars =left(rs!CompanyName, 2) I'm also...

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

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 set db = currentdb set rs = db.openrecordset("newtable") do until rs.eof newcode = Chars &...

you could import the spreadsheet of companies into a temporary table (File>Get External Date>import...) in Access, create a recordset from that, then loop through that recordset, running the same VB code on the company name that you used in the original form to create the company code. I'm...

if this is related to your other recent post, then it is because you were putting: strSql = "values ( ' " & [variable] & " ')" imagine [variable] was the string "hello", the way you wrote the code create a space on either side, e.g. " hello ". I think you may find that your records have a...