run a counter on records meeting a criteria
programmers (and relatively new to access!). I have an issue which I'm stuck on at the moment - and I cant seem to find a resolution!
I would like to run a counter on records within a query which meet a certain criteria. And if record meets a different criteria, then I would like to restart counting from zero.
For example, I would like the counter to increment by 1 for every record that is a 'Pass', then when a 'Fail' recorded the counter reverts back to zero. I'm basically trying to record how many 'Pass' marks an idividual gets in a row.
Im not sure if I'm barking up the wrong tree, but I have the idea that a loop statement would be the most suitable - however I'm not sure on the syntax
Database Usage Counter in Main Form
I am using Access 2010 and I have a small database in our company network, which is using by many of my collegues. I wish to put a counter on the Main Menu Form, which gives how many times this database is opened. I mean, every time I close the database and re-open it again, the counter should increment by one. My form name is "Main Menu" where I want to put the counter on the top right corner to see how many times this database has been opened for use
Can you run through the titles in listbox?
So here is what I have. I have a section of list boxes labeled txtcard0 through txtcard19. Instead of having to do the same line over and over to assign a new variable to each listbox, is there a way to increment the number at the end? I've tried using & and assigning a counter variable and incrementing but neither works. Heres what I have:
db1.Form_PriceForm.txtCard0.value = "test"
db1.Form_PriceForm.txtCard1.value = "test"
db1.Form_PriceForm.txtCard2.value = "test"
db1.Form_PriceForm.txtCard3.value = "test"
db1.Form_PriceForm.txtCard4.value = "test"
db1.Form_PriceForm.txtCard5.value = "test"
Is there a way to do something like
Counter = 0
db1.Form_PriceForm.txtCard & Counter.value = "test"
? If its not possible then no big deal
Calculation based on results of 2 expressions
I have a field called Reoffense Counter and a field called Counter, where Counter is the total count of all the records, and Reoffense Counter is the total of only those records where a new offense has taken place. Each of these fields contains a "1" where appropriate, so they can be counted. I want to find out what percentage of the records contain a Reoffense, so I think I would want to divide Reoffense Counter by Counter. I haven't been able to come up with the correct expression to use in my report.
I am trying to add a feature to my database that counts the number of times that the main menu is opened (I modified this form to always stay open in the background in order to get an accurate count) just out of curiosity.
Right now I have a table called "Count Table" with a field called "Counter" on it. This table acts as the control source on my main menu. The main menu also has a text box called "Page Counter". The "On Open" code looks as follows:
[Page Counter] = [Counter] + 1
'This line seems to work, page counter displays 1 with counter set at 0
[Counter] = [Page Count]
'This is where I run into trouble. The database does not want to overwrite the Counter value in the Count Table.
Any ideas of another way to do this? I tried doing a similar setup as shown above using the Dmax function but had the same outcome
Loop in VBA
I just starting using VBA and have little to no idea as to what I am doing. I need to write code that uses a counter. The counter goes by 1. However if the string in column C does not match the row in column C before it then the counter resets inself to
Add a column to an existing query that is a counter
I want to add a column to an existing query that is a counter. Example of data and desired result below. The criteria is: If Name, Symbol and Date are different, counter changes; if Name, Symbol and Date are same, same counter as line above.
I read about DCount but was unsuccessful.
Update a field in a table after ordering data in two other fields
I have a table with one PatientNumber and TestDate fields among others. Each PatientNumber has several records with different TestDate. I added a new field called TestNumber and wanted to sort the table by PatientNumber and TestDate then update the TestNumber field to increment test numbers for each patient by test date. I was able to create the function to increment the numbers for each patient and reset the counter for the next patient, but the problem I'm having is to write an UPDATE SQL statement that does the update after sorting the data by PatientNumber then TestDate. ORDER BY or GROUP BY don't work with UPDATE queries. What's the best way to handle this?
I'm running the below code:
Dim db As Database
Dim rst As Recordset
Dim Counter As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT [Field] FROM tbl_Imports WHERE [Use] Is Not Null", dbOpenDynaset)
Counter = rst.RecordCount
& the MsgBox is coming back with a result of 1. The SQL returns 7 records so this is what I'm trying to set the counter to. Can anyone see where I'm going wrong? Hugely perplexed
Display records number X to Y
What query or SQL statement would I have to use as a multiple form's recordsource to have it display record numbers 100 to 200 for example?
A solution I thought off
Adding an extra field to the table, and doing this before opening the form
Dim tb as DAO.recordset, counter as int set tb = currentdb.openrecordset("myTable") counter = 0 do while tb.eof = false tb.edit counter = counter + 1 tb!myRecordNumber = counter tb.update tb.movenext loop tb.close
then setting the forms recordsource to
select * from myTable where myRecordNumber between 100 and 200
Is there a quicker way to do this? My backend is sitting on a server online, and updating all 8712639 records everytime the form is opened might not be the smartest or quickest approach
Can I customize an identity increment?
I have an ASP website that a user enters their information on. I setup my table with the appropriate columns and set one of them as the primary key.
When the user clicks the submit button, I want the primary key auto increment to include the date and the number.
Is there anyway to customize the identity increment?
Increment value for sorting
I need to show an increment value on a report but I need to create it in the underlying query for the report.
The report shows OT hours offered and I need to sort the report by group code and then from lowest to highest but I need to sort the query by lowest to highest first. I need to show an increment # for the supervisor to offer additional OT.
Data should look like this:
Emp# OT Offered | Group | Rank
1234 | 0.0 | A | 1
3241 | 1.0 | A | 3
3438 | 0.0 | B | 2
4532 | 2.0 | B | 4
Is there a way to create an increment number in the query? I have done it in the report but I do not get the desired results.
is there a macro that clear field counter to 0??
I made an easy software in access.now when I go to the search form from the switchboard, the previously saved records are already shown in the fields. now m trying to make a macro that clear the counter or make the counter=0 so that whenever I come on the form, I see the fields blank but I cant find any such option in macro or is there any other way out
Forms/Subforms and Auto Seq numbers followup
In my sub-forms that use the increment number, how do I get the next new record to automatically show the next available number? What's happening now is that when I enter a new record, the increment is correct on that record. But, on the data entry form, the NEXT (blank) record shows the same increment number. I have stored the formula in the Default section of the control, and I have tried to set up a requery function on the form in several different areas to see which would update it. None of them seem to work quite right. The closest I've gotten is to enter a character in a new entry, then press ESC and the increment will then change to the correct number. I hope this makes sense. Thanks, and hope you have a great day.
Trouble With Variable holding Value
I have a table, TblCounter, that I am using to setup a counter that is incremented by one each time it is accessed. I store the counter in a variable (myCntVar) which I want to store in a record that already exist in one table (Table A). I also need to create a new record in a different table (Table B) and store the counter in the new record. I am able to store the counter in (myCntVar) in one module, which I tested by putting the variable in a Msgbox (myCntVar) statement which displayed in a form. When (myCntVar) is placed in another module, it does not hold the value place in it. I defined the (myCntVar) as Public at the beginning of a module before the first Procedure so I am able to use it. My Problem is it does not hold the value placed in it.
I need a counter in my database that can do the following
I nput the number of laps the car has done . The next day I put in the number of laps the car has done and it displays the total number for the 2 days . Same for day 3 day 4 etc .
at the end of the week or 2 weeks or 10 days etc I want to reset the total counter to zero .
This sound simple and probably is but it is causing me a lot of grief .
I know access isn't the best program to do this but I want to avoid ( and need to ) using something like Excel .
How to develop increment function
I am using following function to get increment values
THe input value is the colume of a query. if sr is divisible by 2 then increase otherwise old value of increment variable
Sr Output required
Function IncrementValues(i, Optional myBase As Long) As Integer
Dim IncrementVariable As Long
Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("queryName")
IncrementVariable = 0
Do Until .EOF
IncrementVariable = IncrementVariable + 1
IncrementValues = IncrementVariable
Developing Increment Variable
can any body help me in developing following output of a query
First declaring a global variable incrementVariable
developing a function for increment value (one Hour) but first count the number of records in the output of a query. For your ease I have developed following requred outcome of a query.
Is it possible to do this. Std_id and Status are part of my Table while increment variable is not a part of my table.
For your understanding I have attached output in the form of picture
Navigating Through a Recordset (Adding a Record Counter)
I have the following code:
However, I would like to find out how to add a record counter to the form ie.:
Record [X] of [Y]
I have a field as primary and unique. I try to INSERT INTO [table1] VALUES (null,.). It will not automatically jump to the next available number. Is there a Auto-Increment feature in Access and how do I use it?