Solved

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

Posted on 2006-07-05
6
1,447 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

914 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now