Report not working from a cross tab query (with dates)

[bailey11]

I have a report that is based off a query with dates. It is a weekly time card for employees. The dates are actual dates. I have set the query properties to run off a parameter form and it works on the query.

It does not however work on the report. I get the error.

"The Microsoft Jet database engine does not recognize " as a valid field name or expression."

I tried to set the query column headers to "Wday1", "Wday2", etc. but then the query won't run. It says it is too complex.

On the report, I would like to see the actual dates, but I can also just see Monday, Tuesday, Wednesday, etc. if someone can help me get there.

Thanks, Bailey11

 

[mp9]

Please post the SQL of your query.

http://www22.brinkster.com/accessory/

 

[bailey11]

PARAMETERS [forms]![reportparameters].[txtEndDate] DateTime;
TRANSFORM Sum(qrytbltimeentryfields.Units) AS SumOfUnits
SELECT qrytbltimeentryfields.EmployeeNumber, [LastName] & ", " & [FirstName] AS NAME, qrytbltimeentryfields.Description, qrytbltimeentryfields.ProjectNo, qrytbltimeentryfields.Phase, qrytbltimeentryfields.ActivityCode, qrytbltimeentryfields.GetWeekEndDate, qrytbltimeentryfields.PERDIEM, qrytbltimeentryfields.TRAVELTIME, qrytbltimeentryfields.SAFETYJACKPOT, Sum(qrytbltimeentryfields.Units) AS [Total Of Units]
FROM qrytbltimeentryfields
WHERE (((qrytbltimeentryfields.GetWeekEndDate)=[forms]![reportparameters].[txtEndDate]))
GROUP BY qrytbltimeentryfields.EmployeeNumber, qrytbltimeentryfields.LastName, qrytbltimeentryfields.FirstName, [LastName] & ", " & [FirstName], qrytbltimeentryfields.Description, qrytbltimeentryfields.ProjectNo, qrytbltimeentryfields.Phase, qrytbltimeentryfields.ActivityCode, qrytbltimeentryfields.GetWeekEndDate, qrytbltimeentryfields.PERDIEM, qrytbltimeentryfields.TRAVELTIME, qrytbltimeentryfields.SAFETYJACKPOT
ORDER BY [LastName] & ", " & [FirstName], qrytbltimeentryfields.ProjectNo, qrytbltimeentryfields.GetWeekEndDate, qrytbltimeentryfields.LastName, qrytbltimeentryfields.FirstName, qrytbltimeentryfields.WorkDate
PIVOT qrytbltimeentryfields.WorkDate;

 

[dhookom]

You might want to modify the solution faq703-5466 from month to day columns.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

I put in:
ColHead: "D" & DateDiff("d",[WorkDate],[Forms]![reportparameters]![txtEndDate])

I was able to add the D1, D2, D3 this time, but now I get the error "You tried to execute a query that does not include the specified expression 'qrytbltimeentryfields.workdate' as part ofan aggregate function.

How do I use this field, I tried to put it in as a column header, but I can only have the one above.

I have not worked much with crosstab queries.

Thanks Bailey11

 

[dhookom]

Remove WorkDate from the SQL as it is only needed in the ColHead expression. You only need one column heading expression.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

I tried that, but then I get the error below.

"You tried to execute a query that does not include the specified expression 'qrytbltimeentryfields.workdate' as part of an aggregate function.

 

[dhookom]

I am having trouble seeing your SQL view. Could you share it so someone can help you?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

tHIS IS HOW IT IS NOW:

PARAMETERS [forms]![reportparameters].[txtEndDate] DateTime;
TRANSFORM Sum(qrytbltimeentryfields.Units) AS SumOfUnits
SELECT qrytbltimeentryfields.EmployeeNumber, [LastName] & ", " & [FirstName] AS NAME, qrytbltimeentryfields.Description, qrytbltimeentryfields.ProjectNo, qrytbltimeentryfields.Phase, qrytbltimeentryfields.ActivityCode, qrytbltimeentryfields.GetWeekEndDate, qrytbltimeentryfields.PERDIEM, qrytbltimeentryfields.TRAVELTIME, qrytbltimeentryfields.SAFETYJACKPOT, Sum(qrytbltimeentryfields.Units) AS [Total Of Units]
FROM qrytbltimeentryfields
WHERE (((qrytbltimeentryfields.GetWeekEndDate)=[forms]![reportparameters].[txtEndDate]))
GROUP BY qrytbltimeentryfields.EmployeeNumber, qrytbltimeentryfields.LastName, qrytbltimeentryfields.FirstName, [LastName] & ", " & [FirstName], qrytbltimeentryfields.Description, qrytbltimeentryfields.ProjectNo, qrytbltimeentryfields.Phase, qrytbltimeentryfields.ActivityCode, qrytbltimeentryfields.GetWeekEndDate, qrytbltimeentryfields.PERDIEM, qrytbltimeentryfields.TRAVELTIME, qrytbltimeentryfields.SAFETYJACKPOT
ORDER BY [LastName] & ", " & [FirstName], qrytbltimeentryfields.ProjectNo, qrytbltimeentryfields.GetWeekEndDate, qrytbltimeentryfields.LastName, qrytbltimeentryfields.FirstName, qrytbltimeentryfields.WorkDate
PIVOT "D" & DateDiff("d",[WorkDate],Forms!reportparameters!txtEndDate) In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

ANY HELP I CAN GET IS GREATLY APPRECIATED.

 

[dhookom]

You had the WorkDate field in your ORDER BY. I took it out.

PARAMETERS [forms]![reportparameters].[txtEndDate] DateTime;
TRANSFORM Sum(qrytbltimeentryfields.Units) AS SumOfUnits
SELECT qrytbltimeentryfields.EmployeeNumber, 
  [LastName] & ", " & [FirstName] AS NAME, 
  qrytbltimeentryfields.Description, 
  qrytbltimeentryfields.ProjectNo, 
  qrytbltimeentryfields.Phase, 
  qrytbltimeentryfields.ActivityCode, 
  qrytbltimeentryfields.GetWeekEndDate, 
  qrytbltimeentryfields.PERDIEM, 
  qrytbltimeentryfields.TRAVELTIME, 
  qrytbltimeentryfields.SAFETYJACKPOT, 
  Sum(qrytbltimeentryfields.Units) AS [Total Of Units]
FROM qrytbltimeentryfields
WHERE (((qrytbltimeentryfields.GetWeekEndDate)=[forms]![reportparameters].[txtEndDate]))
GROUP BY qrytbltimeentryfields.EmployeeNumber, 
  qrytbltimeentryfields.LastName, 
  qrytbltimeentryfields.FirstName, 
  [LastName] & ", " & [FirstName], 
  qrytbltimeentryfields.Description, 
  qrytbltimeentryfields.ProjectNo, 
  qrytbltimeentryfields.Phase, 
  qrytbltimeentryfields.ActivityCode, 
  qrytbltimeentryfields.GetWeekEndDate, 
  qrytbltimeentryfields.PERDIEM, 
  qrytbltimeentryfields.TRAVELTIME, 
  qrytbltimeentryfields.SAFETYJACKPOT
ORDER BY [LastName] & ", " & [FirstName], 
  qrytbltimeentryfields.ProjectNo, 
  qrytbltimeentryfields.GetWeekEndDate, 
  qrytbltimeentryfields.LastName, 
  qrytbltimeentryfields.FirstName, 
PIVOT "D" & DateDiff("d",[WorkDate],Forms!reportparameters!txtEndDate) In ("Sunday","Monday","Tuesday","Wednesday","Thursday","Friday","Saturday");

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

It now says syntax error in ORDERBY.

Sorry to ask for help again, but like I said I am new to these and it looks okay to me.

Thanks, Bailey11

 

[bailey11]

I got the query to work. Now, here is my code on the report parameter button for the report

Private Sub Command23_Click()
Dim stDocName As String
Dim stLinkCriteria As String

Dim r As Form

stDocName = "Weekly Time card"
stLinkCriteria = " WorkDate BETWEEN #" & Me.txtStartDate & "# AND #" & Me.txtEndDate & "#"
DoCmd.OpenReport stDocName, acViewPreview, , stLinkCriteria, , Me.txtEndDate
'Reports!QryTImeEntryAudit!PayPdEndDate = Me.txtEndDate.Value

End Sub


When I try to run the report from the form, I get an "Enter Parameter Value" popup box for the Workdate. Can you help me with that also?

Thanks gain, Bailey11

 

[bailey11]

Also, it is not putting the values (hours) into the day fields in the query.

 

[dhookom]

You are already filtering your report results so don't attempt to use a Where clause:

DoCmd.OpenReport stDocName, acViewPreview

Your query Column Headings property should be like "D7","D6","D5",..."D0" not the days of the week. Refer back to the FAQ to review this.


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[bailey11]

Thank you. That worked.