Search results

  1. A

    Using if statement to fill a field depending on other records in the table...

    you can use the DCOUNT() select [Name],[Date],[Hours],dcount('[Name]','yourTable','[Name]="'&[Name]&'" and ([Date]+1=#'&[Date]&'# OR [Date]-1=#'&[Date]&'#)')+1 newField from yourTable or you can standard SQL as select a.[Name],a.[Date],a.[Hours],count(b.[Name])+1 as newField from...
  2. A

    Query to remove unwanted spaces before and after after conversion

    for example, the tableA with column colC as below colC ------- aabcdf vbd v df then you can use the TRIM() to remove the both prefix- and -suffix space. update tableA set colC=trim(colC) best regards acmain
  3. A

    How do I open a form blank?

    when we set the form.AllowAdditions attribute, the form will not allow any new records insert from the FORM.
  4. A

    Formula on form to update table?

    maybe you can put the code into the button click event. currentproject.connection.execute "insert into table2(column1) values " & text1.value & "+" & text2.value
  5. A

    operation in data...

    try this Private Sub Command45_Click() tot_valtxt.Value = val(currency_ratetxt.Value) * val(Listbox12.Value) End Sub
  6. A

    DCount syntax

    yes, we can add more criteria in the Dxxx() function same as we used in the select where clause. Me.ScansOK = DCount("[Err_OK]", "TblLog", "[Err_OK] = 'OK' and Station='" & me.StationID.Value & "'") Me.ScansERR = DCount("[Err_OK]", "TblLog", "[Err_OK] = 'ERR' and Station='" &...
  7. A

    Field search and comparison

    one simply solution like below, you can also use the ucase(col) if not case sensitive. create table t ( col1 varchar(30),col2 varchar(20)) select col1,col2, iif(instr(col1,'A')>0,10)+iif(instr(col2,'A')>0,1) as flgA, iif(instr(col1,'B')>0,10)+iif(instr(col2,'B')>0,1) as flgA...
  8. A

    Running Sum Problems

    maybe you can try this SQL as below SELECT tblAnsweredQ.Score Sum(tblAnsweredQ.Score) AS Tot Format(DSum("Score","tblAnsweredQ" =" & [txtTotal] & "") AS RunTot FROM tblAnsweredQ
  9. A

    Front End Users and database window.

    you can refer to the user-level security concept in the ACCESS. by this way, the normal user (user id ADMIN) should not be grant the "Modify Design" permission for <New Reports> in the menu -> Tools -> Security -> User and Group Permissions for detail information, you can get from the...
  10. A

    Number Increment

    hi Xion.Hack, how about your Table structure ? we did not know wether there is already a auto-increment fields or other PK in your Table. usually you can add a column with AutoNumber datatype and it will start from 1. if you want to begin with 21, you need create a another table with same...
  11. A

    Question Help with security ... Admin user still gets in

    same experinece like me. the default account Admin belongs to 2 groups, Users & Admins. so if you want to revoke the permission for user account Admin, you need remove the permisson for groups Users & Admins as well. CAUTION, before you revoke all the permission from admin, admins, users...
  12. A

    Sequential number field in query

    hi tim419762 , as there is no ROW_NUM like other database system e.g. Oracle, so you can not directly get the ROW_NUM in ACCESS, but you can count how may records which before your current records if you have the order by fields. select count(*) as rownum, a.id,a.fd01,a.fd02 from yourTable...
  13. A

    Update Query Criteria

    hi Jerry, I am also wondered where is the [tblUpdater] come from , is it a FROM or another table/query? UPDATE tblContributors INNER JOIN tblContributions ON a.ContributorID = b.ContibutorID SET b.Category = [tblUpdater]![Category], b.Description = [tblUpdater]![Description] WHERE...
  14. A

    Update the backend while it is open

    if you want to alter the table structure, you must lock the table while there is no other user is accessing it. so the job has to be done at either mid-night or weekend. but you also can schedule a job which execute a script to alter the table structure. for example, you can schedule a VBA...
  15. A

    Find structural differences between 2 database

    solution 1. you can use the TOOL->Analyze->Documenter to get the DB structure in word, and then compare these two doc contents. solution 2. you can use the VBA to get all table structure. here is an example to get the table definition. Public Sub getTables() Dim rs As New...
  16. A

    Best way to do this?

    hi Singh400 , If the booked and to-be-booked has all the same column structure, it is recommended to keep them in the same table, and add a new column as status/flag to distinguish it. best regards ACMAIN
  17. A

    Question parameter value

    it is suggested to post your source code , otherwise all of us are only guessing where is the problem.
  18. A

    Question Users and others

    for 2nd question. to launch the application directly 1. as mentioned by Guus2005, use C#.NET, VB or even C to deveop the User Interface, and just use the mdb file as the database. or 2. you can use the access cammand line to launch the access.exe mydb.mdb, and create a shortcut. 3. just...
  19. A

    Question Users and others

    admin to be invisible and naturally protected by a password for this purpose, you can implement the "user-level security" in the ACCESS , you can create a new account in your own PC, for example 'myAdmin', and grant full permission and transfer the ownership to this account. then remove the...
  20. A

    SQL & Combo Box

    hi Jerry, you can either change the SQL like below cmb.RowSource = "SELECT TXT,VAL FROM S_V WHERE SID = 1;" or change the binding column and the columnwidth. cmb.BoundColumn = 1 cmb.ColumnWidths = "0;" 'to hide the first column of VAL, and show the TXT best regards ACMAIN
Back
Top Bottom