Repost - Subform Problem

[Guest]

Please help. In an Access 2003 Asset database (based on Microsoft’s
template) I have the following tables - tblAssets, tblAssetDescription,
tblDepartments,
tblEmployees, and tblAssetAssignment that contains the fields
AssetAssignmentID (autonum,pk), AssetID (num,fk), DepartmentID (num,fk),
EmployeeID (num,fk), AssignmentDate (date/time) and AssignmentDescription
(txt).

My forms are - frmAssetAssignment is a subform of frmAssets and records the
history of the assignment of each asset by date, department and employee.
These forms are working well.

My problem is that I am having difficulty getting frmEmployeesAsset which is
a subform of frmEmployees to accurately show the latest recorded asset(s) for
each employee. The record source for the form contains the following query -

SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
DMax("AssignmentDate","tblAssetAssignment","[EmployeeID]=" &
Nz([EmployeeID],0)) AS Expr1, tblAssetDescription.AssetDescription,
Assets.SerialNumber
FROM tblAssetDescription LEFT JOIN (Assets LEFT JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID;

When the query is run the Expr1 field contains error# and some of the assets
are allocated to more than one person because the latest date is not being
selected by the query. I have tried changing the criteria element of the
statement to AssetID and AssignID and still get the same error message. I
can’t see where I've gone worng and would greatly appreciate any guidance.

Thanks,

Joe

 

[Guest]

Hi Joe

You're using the dmax in the select part of the SQL so, this is going to
pick ALL asset assignments and try to label them with the latest date per
employee.

I think what you want to do is only pick the asset assignments with the
latest date per employee.

I've rewritten the query below and have moved the dmax into the where clause.
Also, note that your query refer to TblAssetAssignments (ends in 's') but
your dmax function refers to TblAssetAssignment (no 's').
And, I have used inner joins as you don't need the left joins.


SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
tblAssetDescription.AssetDescription, Assets.SerialNumber
FROM tblAssetDescription INNER JOIN (Assets INNER JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID
WHERE AssignmentDate = DMax("AssignmentDate", "tblAssetAssignments",
"[EmployeeID] = " & [EmployeeID] AS MaxAssignmentDate


hth

Andy Hull

 

[Guest]

Hi Andy,

Many thanks for your suggestions. I'm still a baby when it comes to code
but I'm trying to learn. Sorry about the typo.

I cut and pasted your code and ran the query however I got the following
error...

Syntax error (missing operator) in query expression 'AssignmentDate = DMax
("AssignmentDate", "tblAssetAssignments", "[EmployeeID] = " & [EmployeeID] AS
MaxAssignmentDate' ... The debug highlighted the AS. I'm not sure what to do
next.

Joe

Hi Joe

You're using the dmax in the select part of the SQL so, this is going to
pick ALL asset assignments and try to label them with the latest date per
employee.

I think what you want to do is only pick the asset assignments with the
latest date per employee.

I've rewritten the query below and have moved the dmax into the where clause.
Also, note that your query refer to TblAssetAssignments (ends in 's') but
your dmax function refers to TblAssetAssignment (no 's').
And, I have used inner joins as you don't need the left joins.


SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
tblAssetDescription.AssetDescription, Assets.SerialNumber
FROM tblAssetDescription INNER JOIN (Assets INNER JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID
WHERE AssignmentDate = DMax("AssignmentDate", "tblAssetAssignments",
"[EmployeeID] = " & [EmployeeID] AS MaxAssignmentDate


hth

Andy Hull

Please help. In an Access 2003 Asset database (based on Microsoft’s
template) I have the following tables - tblAssets, tblAssetDescription,
tblDepartments,
tblEmployees, and tblAssetAssignment that contains the fields
AssetAssignmentID (autonum,pk), AssetID (num,fk), DepartmentID (num,fk),
EmployeeID (num,fk), AssignmentDate (date/time) and AssignmentDescription
(txt).

My forms are - frmAssetAssignment is a subform of frmAssets and records the
history of the assignment of each asset by date, department and employee.
These forms are working well.

My problem is that I am having difficulty getting frmEmployeesAsset which is
a subform of frmEmployees to accurately show the latest recorded asset(s) for
each employee. The record source for the form contains the following query -

SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
DMax("AssignmentDate","tblAssetAssignment","[EmployeeID]=" &
Nz([EmployeeID],0)) AS Expr1, tblAssetDescription.AssetDescription,
Assets.SerialNumber
FROM tblAssetDescription LEFT JOIN (Assets LEFT JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID;

When the query is run the Expr1 field contains error# and some of the assets
are allocated to more than one person because the latest date is not being
selected by the query. I have tried changing the criteria element of the
statement to AssetID and AssignID and still get the same error message. I
can’t see where I've gone worng and would greatly appreciate any guidance.

Thanks,

Joe

 

[John W. Vinson]

Syntax error (missing operator) in query expression 'AssignmentDate = DMax
("AssignmentDate", "tblAssetAssignments", "[EmployeeID] = " & [EmployeeID] AS
MaxAssignmentDate' ... The debug highlighted the AS. I'm not sure what to do
next.

You need a closing parenthesis before the AS.

John W. Vinson [MVP]

 

[Guest]

Hi John,

Many thanks for your reply. I really appreciate the help you are offering.
I replaced the missing parenthesis but I'm still getting the same syntax
error. What next?

Joe

Syntax error (missing operator) in query expression 'AssignmentDate = DMax
("AssignmentDate", "tblAssetAssignments", "[EmployeeID] = " & [EmployeeID] AS
MaxAssignmentDate' ... The debug highlighted the AS. I'm not sure what to do
next.

You need a closing parenthesis before the AS.

John W. Vinson [MVP]

 

[Guest]

Hi again Joe

Sorry, that's my fault. As John has pointed out you need a closing
parenthesis before the AS.
Also, there should, in fact, be nothing else after the parenthesis! (so
remove the AS and everything following it).

(The dmax used to be in the select clause where the AS is valid - I just
copied and pasted from there forgetting to remove the AS...)

Here i the whole SQL again (corrected!)...

SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
tblAssetDescription.AssetDescription, Assets.SerialNumber
FROM tblAssetDescription INNER JOIN (Assets INNER JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID
WHERE AssignmentDate = DMax("AssignmentDate", "tblAssetAssignments",
"[EmployeeID] = " & [EmployeeID])

Hi John,

Many thanks for your reply. I really appreciate the help you are offering.
I replaced the missing parenthesis but I'm still getting the same syntax
error. What next?

Joe

Syntax error (missing operator) in query expression 'AssignmentDate = DMax
("AssignmentDate", "tblAssetAssignments", "[EmployeeID] = " & [EmployeeID] AS
MaxAssignmentDate' ... The debug highlighted the AS. I'm not sure what to do
next.

You need a closing parenthesis before the AS.

John W. Vinson [MVP]

 

[Guest]

Thanks so much Andy and John. I am very grateful for your help!

The query works great and I can now get on with the next bit.

Would it be possible for you to tell me if and where you would put a "WHERE"
statement to limit records in a report query selecting random records for
auditing or would it be better to filter using a query then randomise the
results of that query. I am using Allen Browne's excellent code posted in
2005 and it is working really well. I would like to filter records before
the randomisation takes place.

Here is the code as it stands in the report I am using at present...

Private Sub Report_Open(Cancel As Integer)
Dim StrSql As String
Dim strPercentage As String
Dim strMsg As String

strPercentage = InputBox("How many percent?")
If IsNumeric(strPercentage) Then
If strPercentage >= 1 And strPercentage <= 100 Then
StrSql = "SELECT TOP " & strPercentage & " PERCENT Assets.* FROM [Assets]
ORDER BY Rnd([AssetID]);"
Else
strMsg = "Percentage must be between 1 and 100."
End If
Else
strMsg = "No percentage entered."
End If

If Len(strMsg) > 0 Then
MsgBox strMsg, vbExclamation, "Report not opened."
Else
Randomize
Me.RecordSource = StrSql
End If
End Sub

Thanks again,

Regards,

Joe

Hi again Joe

Sorry, that's my fault. As John has pointed out you need a closing
parenthesis before the AS.
Also, there should, in fact, be nothing else after the parenthesis! (so
remove the AS and everything following it).

(The dmax used to be in the select clause where the AS is valid - I just
copied and pasted from there forgetting to remove the AS...)

Here i the whole SQL again (corrected!)...

SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
tblAssetDescription.AssetDescription, Assets.SerialNumber
FROM tblAssetDescription INNER JOIN (Assets INNER JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID
WHERE AssignmentDate = DMax("AssignmentDate", "tblAssetAssignments",
"[EmployeeID] = " & [EmployeeID])

Hi John,

Many thanks for your reply. I really appreciate the help you are offering.
I replaced the missing parenthesis but I'm still getting the same syntax
error. What next?

Joe

Syntax error (missing operator) in query expression 'AssignmentDate = DMax
("AssignmentDate", "tblAssetAssignments", "[EmployeeID] = " & [EmployeeID] AS
MaxAssignmentDate' ... The debug highlighted the AS. I'm not sure what to do
next.

You need a closing parenthesis before the AS.

John W. Vinson [MVP]