There is some mixed up with the name of the variable in your query

Function DayNo(Day As Date) As Double
   
Dim myDay As Variant
Dim dmyDay As Double
***********************
'  Didn't declare minDag
**********************
minDag = DLookup("Sum", "WorkDays", "[Date]  = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)   ' *******Never assign a value to myDay myb that
should be instead of minDag

DayNo = myDay

End Function

Try using / instead of , in your Format. I always use the format below (\
means that the next character will be included as-is):

minDag = DLookup("Sum", "WorkDays", "[Date]  = " & Format(Day,
"\#mm\/dd\/yyyy\#"))

=?Utf-8?B?S2VudEFF?= <kentae.hilmaa@donotspam> wrote in news:80FF31A4-
2497-4149-B33C-2F9A8908458D@microsoft.com:

The date format is pretty unlikely to be recognised. I would go with
Douglas's suggestion of changing it to a proper jet-compatible one like

 "\#yyyy\-mm\-dd\#" or
 "\#mm\/dd\/yyyy\#"

The other problem is using the reserved word "Sum" as a field name. At
least you need to hide  it in square brackets (as you have with the
equally reserved word "Date"), but it would be best to have a critical
look at your whole object-naming convention. "Workdays" too is very close
to the reserved word "Workday". "Day" is the name of a VBA function and
going to fail in this context. This function will be translated into a
command like

 SELECT Sum FROM Workdays
 WHERE [Date] = #01,09,2003#

which would obviously confuse any jet engine. Try something like this:

 dim someDateVariable as DateTime ' get value from somewhere...
 dim criterion As String
 const jetFormat As String = "\#yyyy\-mm\-dd\#"

 ' set up the criterion carefully
 criterion = "[Date] = " Format(someDateVariable, jetFormat)

 ' now get the value
 minDag = DLookup("[Sum]", "Workdays", criterion)

but I think you have other bugs in your way with object names like this.

Hope it helps

Tim F