Dear Richard,

Below is an example. I have a simple Patient Details table (PtDetailsT, which you often call CustomerT). It has five fields namely PatientID, PtName, Age, Sex and DateRxStarted (read the later as the date when the patient started treatment).
Now, each patient is expected to come back for medical review after one month. So i build a query as follows
SELECT PtDetailsT.PatientID, PtDetailsT.PtName, PtDetailsT.Age, PtDetailsT.Sex, PtDetailsT.DateRxStarted, DateAdd("d",30,[DateRxStarted]) AS FirstReviewDate FROM PtDetailsT;

If i use the above SQL statement to populate my listbox, it is rejected. The VBA editor highlights the "d" in the DateAdd function.

It gets even worse if i add more calculated fields in the query e.g., i may not want FirstReviewDate to fall on a weekend or i may also want to compute by how many days the patient is late for review or which patients will be coming for review soon. See below;
SELECT PtDetailsT.PatientID, PtDetailsT.PtName, PtDetailsT.Age, PtDetailsT.Sex, PtDetailsT.DateRxStarted, DateAdd("d",30,[DateRxStarted]) AS FirstReviewDate, Weekday([FirstReviewDate]) AS DayOfWeek, CDate(nz(IIf([Dayofweek]=7,DateAdd("d",2,[FirstReviewDate]),IIf([Dayofweek]=1,DateAdd("d",1,[FirstReviewDate]),IIf([Dayofweek] Between 2 And 6,DateAdd("d",0,[FirstReviewDate])))))) AS ExactFirstReviewDate, DateDiff("d",[ExactFirstReviewDate],Date()) AS DaysLate FROM PtDetailsT;

So question is, how -without building yet another query- do i use the an SQL statement from a query with calculated fields to populate a listbox or a report?

Best regards,

MICAH

---

Reply from Richard Rost:

Micah, I don't have that problem. DateAdd should work in your SQL statement. I just tested it with one of my sample databases, and it worked fine. I used this as the SQL rowsource for one of my list boxes:

SELECT ProductID, ProductName, LastUpdated, DateAdd("d",30,[LastUpdated]) AS mDate FROM ProductT;

No problems. It worked the first time. Now, I'm using Access 2010, but I also tested it in Access 2003. No problems with either version. You can use many Access functions in SQL statements like this just fine. I'd need to see your database to tell you what the problem is.

Try creating a blank new table with a couple of simple fields and make a new form with a list box and see if that works. If it works, perhaps it's an issue with your table or existing form.