IDENTIFY RECORD BY number and year

[jmsjazz]

I have a form linked to table tblINCIDENTS
When I create a new incident, I select DATE_LOGGED in a text box. This updates the field in tblINCIDENTS.

I would like to show on the form (and store in the table), e.g.

"Record 5 in Year 2013"

I already had the new record number as an autonumber, but now I created 2 extra fields in the table - recordno and year.
I have a hidden text box which, on DATE_LOGGED.CHANGE, finds the Year() from DATE_LOGGED. I have tried using a Query to find the MAX RECORDNO of YEAR. I managed to display the Max (having manually populated the fields!) in a textbox, but am now going round in circles! Could anyone please help? Thanks.

 

[Steve R.]

I already had the new record number as an autonumber, ...

Your post is not clear. Basically, an autonumber field would not be be used to tract the number of records. Read-up on DCOUNT. Second, in Access, the record number is relative. Meaning that it could be the 1st, 3rd, or last record depending on how the the data-set is sorted and/or filtered.

I have tried using a Query to find the MAX RECORDNO of YEAR.

Use DCOUNT. See Access HELP.

 

[Galaxiom]

I already had the new record number as an autonumber, but now I created 2 extra fields in the table - recordno and year.

Neither of these fields should be stored in the table because their values are derived. They should be calculated as required for display using the DATE_LOGGED field and DCount.

 

[jmsjazz]

Thanks both for the replies - I realise the autonumber is no use; it was the way I originally set the structure of the table. I will try DCOUNT, but am unclear as to how this will also link with the YEAR, unless I store it in the table? Sorry it is unclear. I mean it to do the following, e.g.

By 31 Dec 2013 I have 25 records in the table and create a new one:
[DATE_LOGGED] is 31-Dec-2013
The form displays: Record 26 in Year 2013

I create a new record on 1st January 2014, and I would like it to say
[DATE_LOGGED] is 1-Jan-2014
The form displays: Record 1 in Year 2014

I believe a query must determine a) how many records have the year 2013 and b) which particular record is, say, record 14 of that year. Would that not need to be stored with the record?

Thanks again.

 

[Galaxiom]

The year can be returned with Year(DATE_LOGGED).

Try this ControlSource for RecordNumber (untested code):

=DCount("*", "tablename", "DATE_LOGGED >= #1/1/" & Year(DATE_LOGGED) & "# And DATE_LOGGED < " & Format(DATE_LOGGED, "\#mm\/dd\/yyyy\#")) + 1

If more than one record can be logged on the same day you would need to include Time in the date logged. (Note you would not need to disply the time.)

It will show two records with the same record number if two are submitted in the same second.

 

[jmsjazz]

This almost works, but always displays record number 1.
To try to figure out the syntax of the DCOUNT function, I changed it to this:
=DCount("*","tblINCIDENTS","DATE_LOGGED >= #1/1/2012#" And "DATE_LOGGED< #1/1/2013#")
It now always shows the total number of records. Could you let me know where I am going wrong?
I would like to populate a concatenated record ID something like: 1/2013, or 34/2012 etc.. I guess I will concatenate the two textboxes into a string and send this to a field. Is that really a bad idea?
If, of course, I can get the DCOUNT working!

 

[Galaxiom]

Too many double quote marks. Try this:

=DCount("*","tblINCIDENTS","DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013#")

Should return the count of records for 2012.

BTW. I would avoid using this for the primary key.

 

[jmsjazz]

Thanks again Galaxiom, but it still shows the total number of records (including 2011 and 2013). Is this something to do with the Format?

 

[CJ_London]

If I may interject!

I think rather than using the dcount function, try using a subquery

I haven't seen your query in full but assume it is not grouped. I'm also assuming that the autonumber is not random and is called ID and it contains the tblIncidents table

The subquery is as follows:

(SELECT Count(1) FROM tblIncidents as tmp WHERE DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=tblIncidents.ID) AS IncidentCount

Just realised this should also work as a DCount, but may be a lot slower

=DCount("*","tblINCIDENTS","DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=" & tblIncidents.ID)

 

[jmsjazz]

Again, thanks to all who offer help; I am still unable to make these work. I do have an ID field (Autonumber) in tblINCIDENTS, but the subquery doesn't work, nor does the DCOUNT.

 

[CJ_London]

Going back to your original post you were asking for

"Record 5 in Year 2013" to appear in a control.

The subquery solution should be included in your recordsource for the form (and then the controlsouce of your control would be ="Record " & CalcFld & " in Year 2013") where CalcFld is the name of the subquery in your recordsource

whilst the dcount solution would be

="Record " & DCount("*","tblINCIDENTS","DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=" & tblIncidents.ID) & " in Year 2013")

When you say neither works, do you get an error, does it produce the wrong result or what?

It is a bit like going onto a car forum and saying 'my car doesn't work' and expecting someone to tell you why

 

[jmsjazz]

Thanks for your continued support... I am wondering if the solution involves placing the dcount in a subform? When I look on the properties of the textboxes "txtRecordno" and "txtYear", I do not see RECORDSOURCE - only CONTROL SOURCE. The RECORDSOURCE for my current form (FrmIncidents) is QRYIncidents.

When I use the DCOUNT statement in CONTROL SOURCE for txtRecordno, I get the error #Name?

txtYear, by the way, correctly shows the year when I change the DATE_LOGGED field on the form.

Hope this helps clarify my confusion!

 

[CJ_London]

OK, so you would put the subquery solution in your qryincidents query and as you say, the dcount solution could go in your txtRecordno controlsource (or you can create a new text control).

with regards the error, the control source should be

="Record " & DCount("*","[COLOR=red]tblINCIDENTS[/COLOR]","[COLOR=red]DATE_LOGGED[/COLOR] >= #1/1/2012# And [COLOR=red]DATE_LOGGED[/COLOR]< #1/1/2013# AND [COLOR=red]ID[/COLOR]<=" & [COLOR=red]tblIncidents.ID[/COLOR]) & " in Year 2013")

or you could try

="Record " & DCount("*","[COLOR=red]tblINCIDENTS[/COLOR]","Year([COLOR=red]DATE_LOGGED)[/COLOR]=2013 AND [COLOR=red]ID[/COLOR]<=" & [COLOR=red]tblIncidents.ID[/COLOR]) & " in Year 2013")

But you need to check that all the variables (highlighted above in red) match what you actually have in your tables because unless you tell us what they are - we don't know

If this is still a problem, please post the code you are actually using by cutting and pasting - maybe you are missing a comma or a quotation mark

 

[jmsjazz]

I am still having problems.. I tried using the DCOUNT expression in a control but still got the #Name? error - I am sure I used the correct field and txtbox names.
I am now trying to check in the command button what variables are being passed: I create a new record with today's date and want to see how many records have been created in 2013 -
On Command Click:
currentyr = CStr(Format([DATE_LOGGED], "yyyy"))
yrrecs = DCount([DATE_LOGGED], "tblINCIDENTS", [YEAR] = currentyr)
MsgBox currentyr & " " & yrrecs

currentyr shows 2013
yrrecs shows all the records in the table, not just the ones for 2013

DATE_LOGGED is a bound textbox on the form which updates the table on data entry. YEAR is a field I have added back into the table (despite advice not to!) to see if I can easily count the 2013 records. I understand I can later use an expression to find the year from DATE_LOGGED.

I would appreciate any more help!

 

[Galaxiom]

I tried using the DCOUNT expression in a control but still got the #Name? error - I am sure I used the correct field and txtbox names.

Did you start it with an equal sign?

 

[CJ_London]

jmsjazz

I struggle to understand why you don't use the solution I provided and if it doesn't work, simply post the code that doesn't work with the relevant error message

With regards your solution
Year is a reserved word and can cause problems
Cstr produces a string value, while [Year] is either a date or number data type
DCount has missing quotation marks

I'm not having a go, but I'm not prepared to 'solve' what you have posted because, based on performance so far, you will not copy it correctly, or use it in a different way resulting in a different problem - clearly from your construct of the DCount function you have not followed the construct in my solution or alternatively have copied what you do have incorrectly into your post

 

[jmsjazz]

Yes... I am having problems trying to use the subquery: I have tried pasting this into the SQL of the query qryIncidents as suggested by CJ_LONDON:

OK, so you would put the subquery solution in your qryincidents query and as you say, the dcount solution could go in your txtRecordno controlsource (or you can create a new text control).

(SELECT Count(1) FROM tblIncidents as tmp WHERE DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=tblIncidents.ID) AS IncidentCount

but this is my existing query:
SELECT tblPOI.Userid, tblPOI.Operation, tblPOI.POIName, tblIncidents.IC, tblPOI.email, tblIncidents.ID, tblIncidents.[DATE LOGGED], tblIncidents.CATEGORY, tblIncidents.[OPERATION TYPE], tblIncidents.OPERATOR, tblIncidents.LOCATION, tblIncidents.[INCIDENT TYPE], tblIncidents.[INCIDENT DATE], tblIncidents.DESCRIPTION, tblIncidents.[REMEDIAL ACTION], tblIncidents.POI, tblIncidents.STATUS, tblIncidents.[Date closed], tblIncidents.[Closed By],
FROM tblIncidents LEFT JOIN tblPOI ON tblIncidents.POI = tblPOI.Userid;

I cannot see how to paste the SELECT COUNT statement into it.

I then took the following:
="Record " & DCount("*","tblINCIDENTS","DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=" & tblIncidents.ID) & " in Year 2013")
and pasted into the txtRECORDNO control.


I attach screenshot of the form design; the =DCOUNT was copied and pasted as you can see, but on running it shows #Name? By the way, on copying the the DCOUNT statement into the control, I am advised there are too many parentheses, so I simply delete the trailing one.

I apologise if I am being dense, but thanks anyway.

 

[CJ_London]

You need to paste it like this (see the bit in red)

SELECT tblPOI.Userid, tblPOI.Operation, tblPOI.POIName, tblIncidents.IC, tblPOI.email, tblIncidents.ID, tblIncidents.[DATE LOGGED], tblIncidents.CATEGORY, tblIncidents.[OPERATION TYPE], tblIncidents.OPERATOR, tblIncidents.LOCATION, tblIncidents.[INCIDENT TYPE], tblIncidents.[INCIDENT DATE], tblIncidents.DESCRIPTION, tblIncidents.[REMEDIAL ACTION], tblIncidents.POI, tblIncidents.STATUS, tblIncidents.[Date closed], tblIncidents.[Closed By], [COLOR=red](SELECT Count(1) FROM tblIncidents as tmp WHERE DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=tblIncidents.ID) AS IncidentCount[/COLOR]
FROM tblIncidents LEFT JOIN tblPOI ON tblIncidents.POI = tblPOI.Userid;

alternatively, replace the bit in red with the following for the full text version

"Record " & (SELECT Count(1) FROM tblIncidents as tmp WHERE DATE_LOGGED >= #1/1/2012# And DATE_LOGGED< #1/1/2013# AND ID<=tblIncidents.ID) & " in Year 2013" AS IncidentCount

Please note that from the beginning you have been saying

[DATE_LOGGED]

Whereas in your query you have

[DATE LOGGED]

In a very early post I said

But you need to check that all the variables (highlighted above in red) match what you actually have in your tables because unless you tell us what they are - we don't know

Which you clearly have not done and is the reason this is taking so long to resolve.

I have not corrected my solution for the above - you will need to do this yourself.

In an earlier post you said

I do have an ID field (Autonumber) in tblINCIDENTS

Check that you do and if not, modify the code accordingly!

 

[jmsjazz]

Many thanks - I finally understand it. Also apologies for not noticing the DATE_LOGGED error.
I do notice that, as I move into a new year (i.e. a year that does not fit the condition in the code >=2012 and < 2013), all records have the same "count number" i.e. after the last record of 2012 (number 27), all records in 2013 have number 27.
Could one code the condition against YEAR(DATE LOGGED) so that it works for any year?

 

[CJ_London]

You can:

A better formula would be Year(Date_logged)=2012

The challenge is to replace 2012 with a formula.

If you are running in the current year you could use

Year(Date_logged)=year(Date())

Better would be to have a control on your form where you specify the year - e.g. 2012 and the formula would then be

Year(Date_Logged)=form!yourForm!YearCtrl

change the bits in red to to the name of your form and control - I don't want you coming back and saying there is an error

Recommend you change the formula on the first line - Year(Date_logged)=2012 - first and make sure it works then move on to the next bit