Cross tab query not recognising field name. (1 Viewer)

[swell]

Based on the information following, is there some way to select the date range?
This procedure works in other reports that do not use a cross tab query.
I am trying to generate a report based on a cross tab query, select a date range of the field [Date of Activity}

SQL from Xtab query

PARAMETERS [forms]![frmReportAnnualDateSelection]![txtDateFrom] Text ( 255 ), [forms]![frmReportAnnualDateSelection]![txtDateTo] Text ( 255 );

TRANSFORM Sum([Member Activity].Distance) AS SumOfDistance1

SELECT [Activity Type].[Activity ID], [Member Activity].Activity, [Activity Type].ActivityDesc, Sum([Member Activity].Distance) AS SumOfDistance, Sum([Member Activity].Hours) AS SumOfHours

FROM [Activity Type] INNER JOIN [Member Activity] ON [Activity Type].[Activity ID] = [Member Activity].Activity

WHERE ((((DatePart("m",[Date of Activity])))>=[forms]![frmReportAnnualDateSelection]![txtDateFrom] And ((DatePart("m",[Date of Activity])))<=[forms]![frmReportAnnualDateSelection]![txtDateTo]))

GROUP BY [Activity Type].[Activity ID], [Member Activity].Activity, [Activity Type].ActivityDesc

ORDER BY [Activity Type].[Activity ID], [Member Activity].Activity, (DatePart("m",[Date of Activity]))

PIVOT (DatePart("m",[Date of Activity]));

Originally I got this error message,

The Microsoft Access database engine does not recognize '[Date of Activity]' as a valid field name or expression.

After reviewing comments in the forum on this subject I modified the Xtab query and included parameters.

I now get this error message

In the debug Immediate window I have these following lines

This text in strWhere at the time of the error

([Date of Activity] >= #01/01/2020#) AND ([Date of Activity] < #12/31/2020#)

And this is the error message

The Microsoft Access database engine does not recognize '' as a valid field name or expression.



The module where the report is generated. This works with a non Xtab query

Private Sub cmdDatesForAnnualKMs_Click()

    Dim stDocName As String

    Dim stPathName As String

    Dim strDateField As String

     stDocName = "rptAnnualMonthlyDistance"

    stPathName = "C:\LogBook App\LogBook files\Reports\"

    stRptName = stDocName

        stRptName = Mid(stRptName, 4)

   'fileName = "C:\tmp\AnnualMonthlyDistance.pdf"

        filename = stPathName & stRptName & ".pdf"

   'Check values are entered into Date From and Date To text boxes

       If Len(Me.txtdatefrom & vbNullString) = 0 Or Len(Me.txtDateTo & vbNullString) = 0 Then

        MsgBox "Please ensure that a report date range is entered into the form", _

               vbInformation, "Required Data..."

        Exit Sub

        Else

   '  if so run report or cancel reques

   '  Build the filter string.

        If IsDate(Me.txtdatefrom) Then

       strDateField = "[Date of Activity]" 'Put your field name in the square brackets in these quotes.

       strWhere = "(" & strDateField & " >= " & Format(Me.txtdatefrom, strcJetDate) & ")"

     End If

    If IsDate(Me.txtDateTo) Then

         If strWhere <> vbNullString Then

             strWhere = strWhere & " AND "

         End If

         strWhere = strWhere & "(" & strDateField & " < " & Format(Me.txtDateTo + 1, strcJetDate) & ")"

     End If

    '   Next line is to create prepare the report ready to convert to PDF

    ' THIS next line gives an error

     DoCmd.OpenReport stDocName, lngView, , strWhere, acHidden

        DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, filename, False

        DoCmd.Close acReport, stDocName, acSaveNo

    End If

Exit_cmdReport_Click:

    Exit Sub

I don't understand where the (looks to me like two) single quotes come from. (probably a null)
Can someone point me in the correct direction how to correct the error, or achieve what I am trying to do.

 

[Gasman]

What happens if you hard code "Date of Activity"?
I see no point assigning to a variable if it cannot change?. It would be different if you were passing in a fieldname?

 

[isladogs]

This doesn't answer your question but the first two fields AcivityID & Activity will be identical as they are used in the query join. Remove one of them from the SELECT, GROUP BY and ORDER BY clauses.

Your parameters should both be DATETIME not TEXT.

Please can you edit your post to put the code in code tags. This will make it easier for members to read and edit it to assist you.

 

[Galaxiom]

WHERE ((((DatePart("m",[Date of Activity])))>=[forms]![frmReportAnnualDateSelection]![txtDateFrom] And ((DatePart("m",[Date of Activity])))<=[forms]![frmReportAnnualDateSelection]![txtDateTo]))

You appear to be comparing the month of the date in the records with dates in the texboxes/parameters.
BTW It is a lot tidier to use BETWEEN rather than > and <.

WHERE [Date of Activity] BETWEEN [forms]![frmReportAnnualDateSelection]![txtDateFrom] AND [forms]![frmReportAnnualDateSelection]![txtDateTo]))

I don't understand where the (looks to me like two) single quotes come from. (probably a null)

Might not be the case this time but I've seen empty strings in error messages where a procedure has the same name as a module or database.

 

[Galaxiom]

I've rarely used Crosstab in Access and never really got the hang of them.
However I'm guessing you need to derive the Month from the records as a field in the SELECT then PIVOT on that fieldname.

SELECT 
     [Activity Type].[Activity ID]
     , [Member Activity].Activity
     , [Activity Type].ActivityDesc
     , Sum([Member Activity].Distance) AS SumOfDistance
     , Sum([Member Activity].Hours) AS SumOfHours 
     , DatePart("m",[Date of Activity]) AS ActivityMonth

etc 
GROUP BY 
     [Activity Type].[Activity ID]
     , [Member Activity].Activity
     , [Activity Type].ActivityDesc 
     , DatePart("m",[Date of Activity])

etc
PIVOT ActivityMonth

I use PIVOT in SQL Server and I don't think it is quite the same.
Good luck.

 

[swell]

Ok thought about this during the night!
Tested in the old code this morning where it produced the report originally.
That code didn't limit the date so it produced report of ALL records in the tables (this was an over sight in the original code), thats why I am trying to limit the date range which comes from a date input form.
I found when testing in old code this morning that the changes to the cross tab query I made in trying to make it work, created errors.
Thank you everyone for your responses, I will later today have another look.

 

[swell]

What happens if you hard code "Date of Activity"?
I see no point assigning to a variable if it cannot change?. It would be different if you were passing in a fieldname?

The date does change from the form where the date range is input , that form is used to select a number of reports.

 

[swell]

This doesn't answer your question but the first two fields AcivityID & Activity will be identical as they are used in the query join. Remove one of them from the SELECT, GROUP BY and ORDER BY clauses.

Your parameters should both be DATETIME not TEXT.

Please can you edit your post to put the code in code tags. This will make it easier for members to read and edit it to assist you.

Ref the parameters first time I have used them, accepted the default, thought it was strange but at the time accepted what thee system gave me.
The code I put in "in line code tags" .
Should I have posted the code in the window that is presented in the other option?

 

[isladogs]

I've changed your post #1 to use standard code tags in place of the unhelpful inline version.

Note my comments about the duplicate field.
Also try removing the Date of Activity field from the ORDER BY

 

[swell]

You appear to be comparing the month of the date in the records with dates in the texboxes/parameters.
BTW It is a lot tidier to use BETWEEN rather than > and <.

WHERE [Date of Activity] BETWEEN [forms]![frmReportAnnualDateSelection]![txtDateFrom] AND [forms]![frmReportAnnualDateSelection]![txtDateTo]))

Might not be the case this time but I've seen empty strings in error messages where a procedure has the same name as a module or database.

I was (when trying in old code) getting a message that was indicating that it was too complex . I have removed the parameters believe this approach is not what I want, would prefer using [Date of Activity] parameters being passed from the code if I can.

 

[swell]

I have had to go back to a backup, the changes I did to the Xtab query I couldn't recover from.
I thought it might help to post the output of the report.
For when I come back here for more assistance on this
I hope its ok to come back to the same thread I am going to have to take a break for a day or two.
No dates were passed in the original code an oversight.
The report is listing all activities in the DB, (early last year to date). where as I need to pass a date range.

 

[swell]

I've changed your post #1 to use standard code tags in place of the unhelpful inline version.

Note my comments about the duplicate field.
Also try removing the Date of Activity field from the ORDER BY

I didn't notice this response that you posted same time as I was replying.
I wasn't sure at the time of your original response if this had been caused by me trying to modify the Xtab query, but now that I have gone to a backup, I see that this is what the Xtab query wizard produced.
I am hesitant to modify that part of the cross tab as it does what I want.
It is just the date range I need to fix.

Also try removing the Date of Activity field from the ORDER BY

I can read the SQL code but I am hesitant to modify it without someone holding my hand .
I shall return. Thank you for your input.

 

[isladogs]

If you post a cut down version of your database with the relevant items and data, I'll be happy to look at it.
If the data is confidential, modify it first

 

[swell]

If you post a cut down version of your database with the relevant items and data, I'll be happy to look at it.
If the data is confidential, modify it first

Thank you Colin.

I have spent some hours today modifying the SQL code to incorporate the date range using [Date of Activity],was mainly using a different less complex Xtab query.
I can get it to run on the report but because access wants the [Date of Activity] to be in an aggregate, it produces an activity for every date instead of just producing a SUM for each activity.

I have a number of reports that do not use Xtab queries and they work fine passing the [Date of Activity] from VBA to the query. I was hoping to trim my DB and utilize the same code to generate all the reports.
I guess I am going to have to bight the bullet and attack the Xtab query reports differently.
Will the simple Xtab query be sufficient to do this or would you prefer to use the one I had posted?
I would think that all xtab queries would have the same problem.

 

[isladogs]

If you are sending it to me, why not send both versions of the crosstab ...together with any extra explanations I will need.
No rush. I'm busy all day today but may be able to look this evening (UK time)

 

[swell]

how do I post.. it is ready to go I think

 

[isladogs]

Compact the database and zip it then attach it using the Attach Files button

 

[swell]

If you are sending it to me, why not send both versions of the crosstab ...together with any extra explanations I will need.
No rush. I'm busy all day today but may be able to look this evening (UK time)

I am aware that the "rptAnnualBookHours" has incorrect totals "Activity total hours" this is just for testing the date range of the Xtab.
You will need to create a directory structure "C:\LogBook App\Reports" or modify the code where the reports get posted..

I have temporarily made report generation Buttons for the two reports " Annual Book Hours" and " Annual Monthly KM's" in both the date generation forms.

 

[isladogs]

That may explain why I couldn't get anywhere with the earlier upload!
Will look this evening if I have time

 

[isladogs]

Yes!