A date query

[Andrias14]

Ok so I am making a database for a dental practice.



What I want to find out, is what patients haven't been to the dentist for five months, so that I letter can be sent out to them, So I would just like to be able to run the query and shows me everyone you visited the practice 5 months ago.

(I have a field which is called 'Last Visit Date' where coincidently is when the last visited)

Thank You
Andrias

[June7]

SELECT * FROM tablename WHERE [Last Visit Date] < DateAdd("m", -5, Date())

[Andrias14]

Thank you, but unfortunately i'm getting the sub query syntax error in this expressin is incorrect ???

[June7]

There is no subquery in my example. Show your exact SQL statement.

[Andrias14]

SQL for what, if it is for that query I don'r have one yet

[June7]

Then what is generating error message if you have not built a query?

[Andrias14]

when i have added the Patients Id and the last date seen, and then in the criteria put what you said it comes with that message i told you about

[June7]

Don't know how to help you. I tested that expression in criteria and it works.

The error msg occurs when you type the expression or when you run the query? Can you switch to SQL view and see the statement?

Show statement or provide more info. Give sample data. Provide the project for direct analysis.

[Andrias14]

Ok

So my two main tables are:
Tbl_Patients which in has (Patient ID,Salutation,Surname,Name,Contact Number,E-Mail,Scheme,Last Date Seen, Address 1.2.3.4)

The next is my Tbl_Dentist which has mostly the same
I have a few minor tables just so that my table is relational.(Tbl_Salutation,Scheme, Price listing)

I have already made some very basic open queries and a couple of parameter queries.

I havent added any dates into my database yet, thinking about it now do i need atleast a few or not.

And i have 1 form opening tbl_Staff

Thats all the Info i can give you as you can see still a very basic table

Thank You
Andrias

[June7]

You have date field with no dates? Entering values probably won't resolve the error you get but always helpful to test with data.

You will have to provide the project so can analyze. Make copy, remove confidential data, run Compact & Repair, zip if still large, attach to post.

That date field should probably be in another table of Visits if you care about patient history.

BTW, advise to avoid spaces, special characters, punctuation (underscore is exception) in names, also avoid reserved words as full name.

Name would be better as FirstName, E-Mail as eMail, Patient ID as PatientID

[Andrias14]

Hopefully I've attached its a rar file if thats ok, and i completely forgot to add a couple of dates

Thank You
Andrias

[Andrias14]

oh yh and dont worry all of the dentists are made up and i haven't got round to entering some fake patients sorry

[June7]

Here is query I tested and it works.

SELECT Tbl_Patients.* FROM Tbl_Patients
WHERE (((Tbl_Patients.[Last Date Seen])<DateAdd("m",-5,Date())));

[Andrias14]

Now im getting the same syntax error, and when i run the query without the patient i get a message saying undifiened function WHERE in expression, could you just possibly send the copy you have and i can just work of that ??

Thank You
Andrias

[June7]

Okay, I named the query Qry_FiveMonthNotice

EDIT: Purpose Served, file removed.