Date format in DLookUp

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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
 
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
Click to expand...
Click to expand...

--
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


KentAE said:
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
Click to expand...
Click to expand...
 
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


KentAE said:
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
Click to expand...
Click to expand...

--
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


KentAE said:
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
Click to expand...
Click to expand...
Click to expand...
 
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\#"))
 
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!)


KentAE said:
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
Click to expand...
Click to expand...
 
minDag = DLookup("Sum", "WorkDays", _
"[Date] = #" & Format(Day, "mm,dd,yyyy") & "#")
Click to expand...

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
 
Back
Top