The Microsoft Access database engine does not recognize '[Forms]! [Form2]! [StartDate]' as a valid field name or expression. (1 Viewer)

[abrarm]

I am currently developing a Microsoft Access database for tracking employee attendance. However, I have encountered an issue while attempting to create a crosstab query to retrieve attendance records within a specified date range. When I utilize the formula "Between [Forms]![Form2]![StartDate] And [Forms]![Form2]![EndDate]," I encounter an error message stating, "The Microsoft Access database engine does not recognize '[Forms]![Form2]![StartDate]' as a valid field name or expression."

help please

SQL VIEW
TRANSFORM First(attendance.Status) AS FirstOfStatus
SELECT attendance.E_No, profile.name
FROM profile INNER JOIN attendance ON profile.E_No = attendance.E_No
GROUP BY attendance.E_No, profile.name
PIVOT Format([Date],"Short Date");

 

[Gasman]

Well Access does not lie.
Are those controls on the main form?

 

[CJ_London]

If you are using a crosstab you have to set parameters- see the parameter button on the ribbon when in query design

 

[Pat Hartman]

I don't see where you are using the expression specified in your subject line in the query you posted. Perhaps one of the tables in the Where clause is actually a query that has that form field as an argument. Arguments used in subqueries need to be declared in the crosstab query that calls the sub query. Apparently, the initial MS developer who wrote the code for processing a crosstab query, never got the memo about parameters. OR, the developers who wrote the code for all other query types didn't get the memo Can't say why the rules are inconsistent otherwise.

Also, if the controls are unbound, you will need to set their format to Short Date so that the database engine will recognize them as dates.

Please try one of the suggestions at a time and report back on which fixed the problem so we'll know for future reference.

 

[abrarm]

If you are using a crosstab you have to set parameters- see the parameter button on the ribbon when in query design

how to use parameters for forms ?
please fix this crosstab query name is "attendance_Crosstab"

 

[abrarm]

Well Access does not lie.
Are those controls on the main form?

this is my simple database please check .. fix if you can

 

[ebs17]

One of several possibilities: A public function can be evaluated by Jet/ACE.
Create two functions in a standard module that take over the values of the open form with valid values in the controls.

Public Function DateStart() As Date
    DateStart = Forms.Form2.StartDate
End Function

Public Function DateEnd() As Date
    DateEnd = Forms.Form2.EndDate
End Function

The query is then something like:

TRANSFORM
   FIRST(attendance.Status) AS FirstOfStatus
SELECT
   attendance.E_No,
   profile.name
FROM
   profile
      INNER JOIN attendance
      ON profile.E_No = attendance.E_No
WHERE
   attendance.[Date] BETWEEN DateStart()
      AND
   DateEnd()
GROUP BY
   attendance.E_No,
   profile.name
PIVOT
   Format(attendance.[Date], "Short Date")

A small addition to the very last line in the query. The QBE, valued by so many and for some the only means of generating queries, very often omits the qualification of a field with the table of origin for calculated expressions, which then looks like this:

 Format([Date], "Short Date")

If you want to understand a query, you have to know which table a field comes from. If it's not written, you have to look it up. This interruption in action and thought leadership is very cumbersome, and in the case of more complex tasks a catastrophe.

 

[CJ_London]

please fix this crosstab query name is "attendance_Crosstab"

I'm not going to fix it - that is for you to do.

Here is a link that asks much the same question

MS Access Crosstab Query parameters

Is there a way to reference a form's combo/text box within the query like a select query? I usually use something like this in a select query's criteria: like forms!frmMain.qTitleofSomething&...

stackoverflow.com

And there are similar answers in the ''similar threads' section at the bottom of this thread

 

[mike60smart]

this is my simple database please check .. fix if you can

Hi Abrarm

The following screenshot is a Crosstab that counts the number attending in a specific Month.
Is this what you are after?