Dcount criteria fails to compare dates 1

[GaborH]

I am attempting to get a dcount command to work, but despite all my research I do not see the problem. Can someone throw me a hint?


Expr1: DCount("[ID_calendar]","tbl_calendar","[HolidayDate]<" & [wk_End])

HolidayDate and wk_End are dates.

Anything is well appreciated!

GaborH

 

[Edski]

GaborH,
Where are you putting this expression (in a query, form, report)? And where is the [wk_End] field coming from?

 

[Steve101]

Try:

Expr1: DCount("[ID_calendar]","tbl_calendar","[HolidayDate]<" & [red][b]CVDate([/b][/red][wk_End][red][b])[/b][/red])

You need to force the function to do a date based comparison as I think the constraint part of the DCOunt is doing a string based comparison

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[GaborH]

Steve,
Thank you for the idea. However, it did not solve my problem.

I think what I am trying to do is rather simple, but perhaps this is not how it should be done. So I will take your hint about corners, and explain my situation. I have two tables as below.

tbl_weeks
ID_weeks autonumber
week txt
Start_dt date
End_dt date

tbl_calendar which lists the firms holidays
ID_calendar autonumber
HolidayDate date field
ID_Holiday number (actually a lookup field to another table that list all possible holidays)

I am trying to schedule the number of standard work hours (45 hrs, typically) for the next 18 weeks. The standard hours would be adjusted for any holidays that fall on a work day. As the two tables have no shared (foreign) fields, I thought that a DLookup function would work best. I just can not find any good examples of this function. But then again, perhaps there is a better way of doing this?

GaborH

 

[Edski]

GaborH,
Would the Weekday Function help at all? Also, I think you may have meant to use a <> instead of < in your expression above, since you say you want to know if the holiday falls on a weekend or not.
HTH

 

[Steve101]

GaborH,

Check out thread thread701-786429 which I think is in line with your requirement. In particular, note MichaelRed's reference in the last post of this thread to a Frequently Asked Question on the topic in question.



Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)

 

[GaborH]

Steve101,

Thank you. Your link is right on the mark.

GaborH