Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-07-05
6
Medium Priority
?
1,677 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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 2000 total points
ID: 17052077
I think you would need to create two more parameters and specify that in your where clause


0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
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…
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…

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