Work week only 4 days

[aldeb]

I have two queries below that give me Number of Unique days from a date
range and number of gauges calibrated in that same date range. The totals
are skewed however due to the fact that the date range is covering 7 days
per week when in fact the work week is only 4 days, Monday-Thursday. I
somehow need to factor in this work week of 4 days. Does anyone have
any suggestions as to how I can accomplish this?

SELECT Sum(IIf(([Date Due] Is Not Null),1,0)) AS Gages
FROM [All gages]
WHERE ((([All gages].[DATE DUE]) Between [Forms]![DateRangeQueryForm]!
[StartDateTxt] And [Forms]![DateRangeQueryForm]![EndDateTxt]));

SELECT Count(UniqueDaysAll.[DATE DUE]) AS [CountOfDATE DUE]
FROM UniqueDaysAll;

 

[hawkpig]

Try this:

SELECT Count(UniqueDaysAll.[DATE DUE]) AS [CountOfDATE DUE]
FROM UniqueDaysAll
WHERE (Weekday(UniqueDaysAll.[DATE DUE])) >0 and (Weekday(UniqueDaysAll.[DATE DUE])) <6;

 

[aldeb]

Thanks for the SQL. Can you explain what it is actually doing if you have time.

What is the <6 mean? Etc.

 

[Brianwarnock]

The where clause is looking for weekday numbers between 1 and 5, ie Monday thru Thursday.

Brian

 

[raskew]

Brian -

Does the UK NLS API default to Monday (2) as the first day of the week? Might make a difference depending on the OP's location.

Bob