Solved

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

Posted on 2006-07-05
6
1,528 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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

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.

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

730 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