Solved

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

Posted on 2006-07-05
6
1,608 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
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…
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…

630 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