Fist Through Screen: ERROR 3071 Again...

[Xiaxei]

Hi,

I've been working on Access databases for a while, and I can adapt very well to the program to solve many issues... thus I have been creating bespoke databases for companies. Since switching to MS Office 2007 and 2010, I have had nothing but problems.

New problem: ERROR 3071 (Expression to complex)

Erm... I don't think so, its only 950 character long.

SELECT [Patient Info].Surname, [Patient Info].Forename, [Patient Info].Title, [Patient Info].[Address 1], [Patient Info].[Address 2], [Patient Info].[Town/City], [Patient Info].County, [Patient Info].[Post Code], [Patient Info].[Next Eye Exam], [Patient Info].[Email Reminders], [Patient Info].[Last Eye Exam], [Patient Info].[Date of Birth]
FROM [Patient Info]

WHERE ((([Patient Info].[Next Eye Exam]) Between [Enter Earliest Date] And [Enter Latest Date]))

ORDER BY [Patient Info].Surname, [Patient Info].Forename;

I can rectify this simply by removing the Order By paramters. As soon as I add them again, same error. Can anybody help me with this? I've tried creating completly new queries and everything, nothing seems to work.

Thanks,

Jim.

 

[Galaxiom]

Since you are only selecting from one table the tablename can be ommitted.

SELECT Surname etc
FROM [Patient Info]

There are many other ways to shorten sql.
For example:

If you are retreiving all the fields from [Patient Info] use:

SELECT [Patient Info].*

Alias the tablename

SELECT A.Surname, etc
FROM [Patient Info] AS A
WHERE A.[Next Eye Exam] Between etc

I would also recommend you stop using spaces and special characters such as slash in names. It saves typing because you can leave out the brackets.

PatientInfo rather than [Patient Info] etc


Names displayed on forms and reports can be set separately in the label captions.

 

[ByteMyzer]

Have you tried this for the WHERE clause?:

WHERE ((([Patient Info].[Next Eye Exam]) Between CDate([Enter Earliest Date]) And CDate([Enter Latest Date])))

 

[Xiaxei]

Thanks GalaxiomAtHome & ByteMyzer,

No such luck with either of your solutions, i've removed all my spaces in any names in the database, which took bloody ages to restore all the relationships and data sources. Didn't work though, also tried the other options you said. I can't think what to do apart from tell the company... you just gotta deal with them being unsorted.

Haha.

Any other ideas,

Jim.

 

[Galaxiom]

Sorry. I meant removing spaces as a general comment about design rahter than a solution. "I ...recommend you STOP using spaces" rather than change what you had done.

I can see you are clearly desperate to solve this and it must be frustrating. Like John I also pasted the code into my Access 2007 and it worked perfectly. There is nothing wrong with your query.

The database itself must be corrupted. Have you tried exporting everything to a new database?