Dlookup Date

[tractorvix]

Hi,

I can't figure out why the below code won't bring back the right information. I think it may be a formatting issue, but I just can't seem to get it to work

curdate = Me.Text0
CurHours = Nz(DLookup("[sumofhours]", "qrysumweeks", "[wkdate] = " & curdate), 0)

The DLookup is recognising the correct date for curdate, but doesn't seem to be able to match it in the qrysumweeks, even though I know there is data there. I've set both curdate and wkdate to short date but it still brings back a 0 for sum of hours rather than the 42 it should be pulling back.

Any ideas?

TV

 

[SkipVought]

Hi,

Try,,,

CurHours = Nz(DLookup("[sumofhours]", "qrysumweeks", "Format([wkdate],'yyyy/mm/dd') = " & Format(curdate,'yyyy/mm/dd')), 0)

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[tractorvix]

Thanks for the response skip, but it doesn't like that at all. I think probably something to do with the single and double quotes.

who knew that dlookups were so painful!

TV

 

[tractorvix]

Have changed your bit of code skip to read
curdate = Format(curdate, "yyyy/mm/dd")

CurHours = Nz(DLookup("[sumofhours]", "qrysumweeks", "Format([weekdate],'yyyy/mm/dd') = " & curdate), 0)

but it's still coming back with a zero value

TV

 

[SkipVought]

It was a TIP. Play with it.

Try this...

CurHours = Nz(DLookup("[sumofhours]", "qrysumweeks", "Format([wkdate],'yyyy/mm/dd') = " & Format(curdate, "yyyy/mm/dd")), 0)

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}

 

[tractorvix]

Thanks for the reply Skip. It must be something that I'm missing as I've tried putting in the actual date rather than the curdate field and it still brings back a zero value

CurHours = Nz(DLookup("[SumofHours]", "qrysumweeks", "format([weekdate],'yyyy/mm/dd') = " & Format(27 / 3 / 2006, "yyyy/mm/dd")), 0)

Vicky

 

[tractorvix]

for some reason the date it seems to be passing through if text0 is set to 27/03/2006 is #00:06:28#

myweek = Format(week(Forms![form1]![Text0]), "short date")
CurHours = DLookup("[sumofhours]", "qrysumweeks", "[weekdate]= week(" & myweek & ")")

myweek sets itself to #27/03/2006#
but then when it goes through the Dlookup it does something bizarre and changes to #00:06:28#

Function week referred to is below:

Public Function week(dateupdate As Date) As Date
Dim myweek As Date
mydate = Format(dateupdate, "Short Date")

Select Case Weekday(mydate)
Case Is = 2
myweek = mydate
Case Is = 3
myweek = DateAdd("d", -1, mydate)
Case Is = 4
myweek = DateAdd("d", -2, mydate)
Case Is = 5
myweek = DateAdd("d", -3, mydate)
Case Is = 6
myweek = DateAdd("d", -4, mydate)
Case Is = 7
myweek = DateAdd("d", -5, mydate)
Case Is = 1
myweek = DateAdd("d", -6, mydate)
End Select

week = myweek
End Function

Any guidance would be great.

Vicky

 

[SkipVought]

Where is text0 coming from?

Is this control formatted properly for date?

This is probably the crux of the prolem.

Skip,

_{[red]Be Advised![/red] A chicken, who would drag a wagon across the road for 2 cents, is…
Poultry in motion to pullet for a paltry amount!}