Hi poeple, is there anyway to make a counter field that is automatically filled (1,2,3,...) rather than filling it manually and also it resets each new day?? I'm not talking about autonumber field because of its nature also because I use it as a uniqe field "VisitID",, please help..
Automatic counter resets each new day (1 Viewer)
- Thread starter docxyz
- Start date
MarkK
bit cruncher
- Local time
- Yesterday, 18:14
- Joined
- Mar 17, 2004
- Messages
- 8,179
I would calculate that by subtracting a fixed start date from the current date. Consider how this . . .
. . . always returns a count of days since a fixed point in time.
Code:
function MyCounter as long
const START_DATE as date = #1/1/2014#
MyCounter = date() - START_DATE
end functionSorry for this Stupid quesion, is result gonna be a date or a number? and how that field gonna count automatically (1,2,3...),if there is much code to write please help me with this, I'm not expert in vba,, thank you again.
BigHappyDaddy
Coding Monkey Wanna-Be
- Local time
- Yesterday, 18:14
- Joined
- Aug 22, 2012
- Messages
- 205
I think that maybe MarkK misunderstood the question. His provided code would give the numbers of days that have passed since 1/1/2014.
To attack your counter problem, I would use a DMax function on the VisitID field for all records with today's date.
Your code might look like:
The Nz function is needed when there a value doesn't exists for VisitID for today's date. DMax would return a NULL, but the Nz function will substitute a 0.
Now when the new record is created, use the value of lngNewVisitID as the value for VisitID.
To attack your counter problem, I would use a DMax function on the VisitID field for all records with today's date.
Your code might look like:
Code:
lngNewVisitID = Nz(DMax("VisitID","tblTableNameHere","DateField = #" & Int(Now()) & "#"),0) + 1The Nz function is needed when there a value doesn't exists for VisitID for today's date. DMax would return a NULL, but the Nz function will substitute a 0.
Now when the new record is created, use the value of lngNewVisitID as the value for VisitID.
MarkK
bit cruncher
- Local time
- Yesterday, 18:14
- Joined
- Mar 17, 2004
- Messages
- 8,179
The function returns a long integer, not a date. And how do you want to user it? In a query you could call the function, like . . .
In VBA code you could call the function like . . .
In the control source of a textbox on a form you could do . . .
Hope this helps,
Code:
SELECT Field1, MyCounter() As DayCount
FROM Table1
Code:
private sub cmdButton_Click()
msgbox MyCounter() & " days since count started!", vbinformation
end sub
Code:
=MyCounter()You are my super hero, this is so logic but I would never think of it, God bless you.
Is there any conflect or issue regarding using the app by many users over network, I mean recording visits at the same time.
BigHappyDaddy
Coding Monkey Wanna-Be
- Local time
- Yesterday, 18:14
- Joined
- Aug 22, 2012
- Messages
- 205
The potential for conflict does exist, but to reduce the risk, create the record immediately after getting the next VisitID. The risk increases if you ask for the next VisitID and do other operations before creating the record. That gives the next user to ask (and receive) the same VisitID.
Thank you for making this very clear, thank you.
