Get the last record from a filtered table and display in a text box on form.

  • Thread starter Thread starter Les
  • Start date Start date
L

Les

I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
 
Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Dan Artuso said:
Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
 
Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Les said:
Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Dan Artuso said:
Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Thanks Dan that's working fine now.

Les


Dan Artuso said:
Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Dan Artuso said:
Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the
Click to expand...
last
year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Dan Artuso said:
Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Dan Artuso said:
Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the
Click to expand...
last
year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for linking
the data.

I have run a Last record query which will display this but I guess I need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Hi,
What are you using as criteria?
If you want the max month for for a particular year, you have to use
the year in your criteria. I have no idea what you're tables look like, so that's
all I can suggest for now.


--
HTH
Dan Artuso, Access MVP


Les said:
Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Dan Artuso said:
Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last
year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Dan Artuso said:
Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the last
year
data was entered for the current staff member. At the moment most staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Greg thanks I have tried the line below but I get a "Type Missmatch" error.
All the fields are Text fields so am have i miss interpreted your code?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =" &
Forms!frmAssociate_Details!id & " and numYear = " & endYear)


Greg Kraushaar said:
You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Dan Artuso said:
Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" & Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear"
Click to expand...
in
an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see
Click to expand...
the
last
year
data was entered for the current staff member. At the moment
Click to expand...
most
staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
If the criteria is text, then you have to delimit with single quotes.
Usually a num or int prefix indicates the variable is a number while a str
prefix indicates strings. These things do matter, hence the convention.
Are you saying your key field is text as well?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =' " & _
Forms!frmAssociate_Details!id & " ' and numYear = ' " & endYear & " ' ")

the spaces between quotes are for clarity.



--
HTH
Dan Artuso, Access MVP


Les said:
Greg thanks I have tried the line below but I get a "Type Missmatch" error.
All the fields are Text fields so am have i miss interpreted your code?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =" &
Forms!frmAssociate_Details!id & " and numYear = " & endYear)


Greg Kraushaar said:
You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" &
Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the
following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to
populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all the staff
records.

What I need to do is display the last year entered in "numYear" in
an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text
box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can see the
last
year
data was entered for the current staff member. At the moment most
staff
have records from 2003 & 2004. "frmProject" has a field "[id]" for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Dan Appologies for the confusion. What happened I changed the format in the
field and forgot to rename the prefix. The key and ID are numbers so I have
changed the formula and it works perfectly now.

Many thanks for your continued assistance with this.

Les


Dan Artuso said:
If the criteria is text, then you have to delimit with single quotes.
Usually a num or int prefix indicates the variable is a number while a str
prefix indicates strings. These things do matter, hence the convention.
Are you saying your key field is text as well?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =' " & _
Forms!frmAssociate_Details!id & " ' and numYear = ' " & endYear & " ' ")

the spaces between quotes are for clarity.



--
HTH
Dan Artuso, Access MVP


Greg thanks I have tried the line below but I get a "Type Missmatch" error.
All the fields are Text fields so am have i miss interpreted your code?

endMonth = DMax("[numMonth_Number]", "tblAssociate_Diary", "key =" &
Forms!frmAssociate_Details!id & " and numYear = " & endYear)


Greg Kraushaar said:
You need to filter ofr the YEar when you get the month
endYear = DMax("[Yr]","tbl","key =" & ID
EndMonth = DMax("Mth","tbl",Key="& ID&" and Yr= "&EndYear

Sorry Dan spoke to soon.

The year is working fine but if I use the code for the month then if the
year is 2005 and the month is 6 it shows the month as 12 as it's the
highest in the previous year. Any thoughts on this please.

Les


Hi Les,
Use DMax. It's easier than opening a recordset from a query.
endYear = DMax("[numYear]","tblAssociate_Diary","key =" &
Forms!frmAssociate_Details!id)

Do the same for endMonth

--
HTH
Dan Artuso, Access MVP


Dan thanks for below but what I really need is the code for the
following
SQL from my query. Sorry if I did not explain it clearly.

I need to populate a text box called "endYear". I now also need to
populate
"endMonth"

SELECT tblAssociate_Diary.key, Last(tblAssociate_Diary.numYear) AS
LastOfnumYear, Last(tblAssociate_Diary.numMonth_Number) AS
LastOfnumMonth_Number
FROM tblAssociate_Diary
GROUP BY tblAssociate_Diary.key
HAVING (((tblAssociate_Diary.key)=[Forms]![frmAssociate_Details]![id]));

Thanks

Les


Hi,
The domain lookup function you're looking for is DMax.


--
HTH
Dan Artuso, Access MVP


I have a table which has a fields "id" & "numYear" for all
Click to expand...
the
staff
records.

What I need to do is display the last year entered in
Click to expand...
"numYear"
in
an
unrelated form for the current filtered staff record .

My subform is "frmProject" and the full path to the sub form text
box is
Forms![frmStaffDetails]![frmProject].Form![endYear].

So in the text box "endYear" on subform "frmProject" I can
Click to expand...
see
the
last
year
data was entered for the current staff member. At the moment most
staff
have records from 2003 & 2004. "frmProject" has a field
Click to expand...
"[id]"
for
linking
the data.

I have run a Last record query which will display this but I guess I
need
some type of domain lookup. I can't find the exact command I need.

Thanks

Les
Click to expand...
Click to expand...
Click to expand...
 
Back
Top