Date format in DLookUp

[KentAE]

Can anyone help to get this function to run.

Input day comes from a Qureie and has format Short Date (2006-01-27).
The field [Date] islinked from an exceltable and has same format.
The function runs correct in this case: =DayNo(Date())

Function DayNo(Day As Date) As Double

Dim myDay As Variant
Dim dmyDay As Double
minDag = DLookup("Sum", "WorkDays", "[Date] = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)
DayNo = myDay

End Function

Thank's in advance

 

[Ofer]

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

 

[KentAE]

Sorry, I missed to translate minDag to English.
minDag = myDay

However, the function doesn't work. I beleve there is some mishmash with the
format formula. ??
Here is my latest version:

Function DayNo(Day As Date) As Double

Dim myDay As Variant
Dim dmyDay As Double
myDay = DLookup("Sum", "WorkDays", "[Date] = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)
DayNo = myDay

End Function

--
Officebyggaren
Kent Älmegran


"Ofer" skrev:

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
--
\\// Live Long and Prosper \\//
BS"D

Can anyone help to get this function to run.

Input day comes from a Qureie and has format Short Date (2006-01-27).
The field [Date] islinked from an exceltable and has same format.
The function runs correct in this case: =DayNo(Date())

Function DayNo(Day As Date) As Double

Dim myDay As Variant
Dim dmyDay As Double
minDag = DLookup("Sum", "WorkDays", "[Date] = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)
DayNo = myDay

End Function

Thank's in advance

 

[Ofer]

When you run the function try and convert the field you are passing to it,
incase that field type is string

DayNo(CDate([DateFieldName]))
--
\\// Live Long and Prosper \\//
BS"D

Sorry, I missed to translate minDag to English.
minDag = myDay

However, the function doesn't work. I beleve there is some mishmash with the
format formula. ??
Here is my latest version:

Function DayNo(Day As Date) As Double

Dim myDay As Variant
Dim dmyDay As Double
myDay = DLookup("Sum", "WorkDays", "[Date] = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)
DayNo = myDay

End Function

--
Officebyggaren
Kent Älmegran


"Ofer" skrev:

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
--
\\// Live Long and Prosper \\//
BS"D

Can anyone help to get this function to run.

Input day comes from a Qureie and has format Short Date (2006-01-27).
The field [Date] islinked from an exceltable and has same format.
The function runs correct in this case: =DayNo(Date())

Function DayNo(Day As Date) As Double

Dim myDay As Variant
Dim dmyDay As Double
minDag = DLookup("Sum", "WorkDays", "[Date] = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)
DayNo = myDay

End Function

Thank's in advance

 

[Douglas J Steele]

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\#"))

 

[KentAE]

Hi, guys
Sorry Douglas your tips doesn't work.
To Ofer and all other helpful peoples:

I have not so much experiens of programming so, please, it should be nice if
you are kind to complete my function.
Thank's in advance
--
Officebyggaren
Kent Älmegran


"Douglas J Steele" skrev:

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\#"))


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Can anyone help to get this function to run.

Input day comes from a Qureie and has format Short Date (2006-01-27).
The field [Date] islinked from an exceltable and has same format.
The function runs correct in this case: =DayNo(Date())

Function DayNo(Day As Date) As Double

Dim myDay As Variant
Dim dmyDay As Double
minDag = DLookup("Sum", "WorkDays", "[Date] = #" & Format(Day,
"mm,dd,yyyy") & "#")

dmyDay = CDbl(myDay)
DayNo = myDay

End Function

Thank's in advance

 

[Tim Ferguson]

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

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