Dateadd query problem

[horncastlejohn]

Hi all,

I am trying to create a query that will calculate the date for a follow up appointment based on a dateadd function which uses a starting date and an interval (both held in a table) and then only display certain of the results based on a parameter (another date) defined when the query is run. The SQL statement I have used is as follows;

SELECT Schedule.[Pt Name], Schedule.Regime, Schedule.[Recall Interval], DateAdd("d",[Recall Interval],[Appointment Date]) AS [Next Appointment]
FROM Schedule
WHERE (((DateAdd("d",[Recall Interval],[Appointment Date]))=[Enter Date]));

However when I run the query i get an error message "This expression is typed incorrectly, or it is too complex to be evaluated." The dateadd function works fine without the parameter query so I take it that this means access doesn't like working out a date and then filtering the results according to a parameter in the same query. I am clearly going about this in the wrong way so can anyone suggest a better way of achieving the same outcome.

Thanks.

 

[Guus2005]

With all the square brackets it is difficult to distinguish the actual fields from the parameters. Nevertheless:

SELECT Schedule.[Pt Name], Schedule.Regime, Schedule.[Recall Interval], DateAdd("d",[Recall Interval],[Appointment Date]) AS [Next Appointment]
FROM Schedule

You can split the query in two queries. When you save this in Query1 and create a Query2

SELECT Query1.[Pt Name], Query1.Regime, Query1.[Recall Interval], Query1.[Next Appointment]
FROM Query1
WHERE (Query1.[Next Appointment]=[Enter Date]);

 

[horncastlejohn]

Hi Guus2005 thanks for the reply, I tried the two query approach as you suggested however it still isn't working running query 2 now causes it to ask for values for "pt name" regimen etc. I suspect its square brackets causing the problem however taking them out for the values held in fields causes a syntax error of "Missing Operator"

The fields held in the table "Schedule" are "Pt Name", "Regime", "Appointment Date" and "Recall Interval". I then want to calculate a value "Next Appointment" based on the DATEADD function.

The parameter I need the user to enter is the date they want to search for ([Enter Date] in the SQL shown above.)
The query should then output a list of the records where the calculated "Next appointment" is equal to the date entered as a parameter.

Is this making any sense to anyone?

 

[Rabbie]

It is never a good idea to have spaces in table field names. Either remove the spaces or replace them with underscores. It make it easier when referring to them in queries or VBA code.

 

[Guus2005]

It is never a good idea to have spaces in table field names. Either remove the spaces or replace them with underscores. It make it easier when revering to them in queries or VBA code.

I second that!

 

[Brianwarnock]

The second query should be From Query1 not From Schedule

Brian

 

[Guus2005]

Wow. There are people actually reading this stuff!
I have changed the original post. Thx!

 

[horncastlejohn]

Hi thanks folks,

In my defence I inherited the work that someone else did on the database, so the oringinal field names where created by someone else! I shall remove the spaces when I get the chance.

Unfortunately the query still is not working, it now only asks for the date to serach on (as it supposed to) but access then complains that the expression is too compex or typed wrongly!

Could it be that because the "next appointment" value is not defined anywhere as a "date/time" (its created in query1 rather than being held in a table as I couldnt get the dateadd function to write to a table) I am confusing it by asking it to search for a date?

Thanks

 

[Guus2005]

is it possible to post the database or a subset showing the problem?

 

[horncastlejohn]

is it possible to post the database or a subset showing the problem?

Not sure, can I post a whole database to the forum?

 

[Rabbie]

Not sure, can I post a whole database to the forum?

Depends on its size. Firstly Compact and repair then Zip it. If necessary delete some records that arent needed for your problem and also delete unnecessary reports/forms.

 

[horncastlejohn]

Ok here is a cut down copy of the database. I am more than a little puzzled, the original problem now seems to have resolved, although I now get a different error message!

 

[Brianwarnock]

I've never used parameter prompts preferring to use a Form to drive my queries, but I tried to get you query to work and it produced no selections, so I used a form instead and it works perfectly on query1 which is what I would expect.

Brian

 

[horncastlejohn]

I've never used parameter prompts preferring to use a Form to drive my queries, but I tried to get you query to work and it produced no selections, so I used a form instead and it works perfectly on query1 which is what I would expect.

Brian

Thanks Brian,

i am still learning the ropes with access (as you can probably guess!) I have not yet had experience of using a form to drive a query. How does one go about doing that?

Thanks,
John.

 

[Guus2005]

There was an error opening table Schedule. Field Regime was defined as a text field. There was an entry in the table ID 5203: "cmf c1" which was not found in the "Regime Names" table.

I have changed the table. You should store the integer (RegimeID) and not the text. Table "Schedule2" is fixed.

Enjoy!

 

[Brianwarnock]

Like Gus I had a problems wiopening tables, but the attached shows what I did to run query1, I made no other corrections.

Brian

 

[horncastlejohn]

Hi,

Thanks Brian, I will have a look through what you have done. Much appreciated hopefully that will set me on the right track.

I had noticed the problems with the tables opening - something else to puzzle over!

Thanks again.

John

 

[Brianwarnock]

Hey Guys I just learnt something simple. The reason the date prompt returned no data was that I entered 21/06/07, but as the prompt has no formatting it didn't find 21/06/2007 which is what is needed, a Form which I always use has no such problems as the input box is formatted.

It's great when the penny drops.

Brian