Syntax problem with dates

[hardyd44]

Hi,

This is something simple, and I think it is my syntax that is wrong

I am building a holiday database that will be used for more than 1 year and want to produce reports only for the current Holiday year.
My method of doing this is to have a table that contains ALL holiday data and use a make table query to produce the current holiday years data for the reports to be diven from.

The problem is I want to be able to allow users to change the current year by updating 2 fields in a table with only 1 record - so have used:

Between ([Tbl_Holiday_Year]![Holiday_Year_ Start_Date]) And ([Tbl_Holiday_Year]![Holiday_ Year_Finish_Date])

as the select criteria for the query to filter only this year holidays, now the problem - when the query is run I get pop ups asking to input the start and finish dates instead reading the dates from the table, if I replace with actual dates in the criteria it does what I want it to.

I need it this way as I don't want users having to amend queries for each new holiday year.

Any ideas???

 

[Brianwarnock]

I don't think you can refer to tables in the query criteria , if you could you would have to put Tables!etc, but I don't think it works.

You would need to do something like

FROM Table1, Table2
WHERE (((IIf([table1].[flddate]>=[table2].[flddate] And [table1].[flddate]<=[table2].[flddate2],True))=True));

I will let you type your own field and table names

Brian

 

[hardyd44]

Thank you

Brianwarnock,

Thank you - again this forum, has got me out of the s**t

Did not know you could not call a table in the criteria - so would have never got the syntax right!!

Your solution worked fine once I removed my typo's

Regards

Dean