I need help with a IIf Statement!

[Stho176]

I am needing help with how to create a statement. I would like to bucket items that should be shipped on a particular date. I created a separate table with the targeted dates: a start date, end date and the Saturday or target date. Say if a item was entered to on 10/8/13 and it will be shipped on 10/12/13; it should appear with a "targeted" Saturday's date. Please review the statement that I have created: Saturday Date: IIf([sch_del_date]>[Start date] And [sch_del_date]<[end date],[saturday],"")

[NTC]

The syntax is correct. Can't comment on the logic or the field info of course....

[Stho176]

The syntax is correct. Can't comment on the logic or the field info of course....

NTC, thanks for that information! I have attached a screen shot so, maybe this will provide more insight as to what I am trying to do. It works but it is duplicating the information.

[June7]

There is no join between the tables. This results in a cartesian relation, every record of each table joins to every record of other table.

Consider:
SELECT *, DLookup("saturday", "[tbl Saturday Week Days]", "#" & sch_del_date & "# BETWEEN [Start Date] AND [End Date]") As TargSat FROM [qryOpen Order by District by wk1];

BETWEEN AND is inclusive for the range ends.

This could probably be accomplished without the date table. What are the rules that set the start date and end date range as well as selecting the saturday target?