Solved

The microsoft office access database engine does not recognize ' ' as a valid field name or expression.

Posted on 2006-07-05
6
1,479 Views
Last Modified: 2008-02-01
I have a SELECT QUERY that gets a criteria from a form.  This works perfectly.
SELECT DailyAttendanceDetails.TypeofVisit, DailyAttendanceVisits.VisitDate, DailyAttendanceDetails.StaffMember
FROM DailyAttendanceVisits INNER JOIN DailyAttendanceDetails ON DailyAttendanceVisits.AppointmentID = DailyAttendanceDetails.AppointmentID
WHERE (((DailyAttendanceVisits.VisitDate) Between [Forms]![frmStartAndEndDates]![Start_Date] And [Forms]![frmStartAndEndDates]![End_Date]));


Now when i base this crosstab query on that select query I get "The microsoft office access database engine does not recognize "[Forms]![frmStartAndEndDates]![Start_Date] " as a valid field name or expression."

Here is SQL of crosstab.
TRANSFORM First(QryAttendanceforReportpart1.TypeofVisit) AS FirstOfTypeofVisit
SELECT QryAttendanceforReportpart1.StaffMember, QryAttendanceforReportpart1.TypeofVisit AS TypeofVisit, QryAttendanceforReportpart1.VisitDate
FROM QryAttendanceforReportpart1
GROUP BY QryAttendanceforReportpart1.StaffMember, QryAttendanceforReportpart1.TypeofVisit, QryAttendanceforReportpart1.VisitDate
PIVOT Format([VisitDate],"dddd");

I need some way of either having a parameter (which I've not really gotten an understanding of) or something.

please hurry under a deadline
0
Comment
Question by:candg
  • 3
  • 2
6 Comments
 
LVL 5

Expert Comment

by:gbentley
ID: 17048164
Is that form open when you're running the query?
0
 

Author Comment

by:candg
ID: 17048222
yes
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 17048229
U need to specify a parameter on the top

try this
go into design view of the query

then select menu item Query/Parameters

enter your VisitDate as a parameter and try that

The SQL will have

PARAMETER ....
as the first line of the query
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 65

Expert Comment

by:rockiroads
ID: 17048235
If the parameter u define is of type datetime then it will be something like this


PARAMETERS VisitDate DateTime;
TRANSFORM First(QryAttendanceforReportpart1.TypeofVisit) AS FirstOfTypeofVisit
SELECT QryAttendanceforReportpart1.StaffMember, QryAttendanceforReportpart1.TypeofVisit AS TypeofVisit, QryAttendanceforReportpart1.VisitDate
FROM QryAttendanceforReportpart1
GROUP BY QryAttendanceforReportpart1.StaffMember, QryAttendanceforReportpart1.TypeofVisit, QryAttendanceforReportpart1.VisitDate
PIVOT Format([VisitDate],"dddd");


0
 

Author Comment

by:candg
ID: 17050672
is there any way to have it be a between[startdate] and [enddate]
0
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 17052077
I think you would need to create two more parameters and specify that in your where clause


0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question