Dlookup driving me nuts!

[Mike]

Hi All,

I reckon I've tried every combination of single quotes, double quotes and
brackets available, and not one is working. Here's the code:

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate =" & BUDate)

This is supposed to return a (long integer) number from the "CycleDay" field
after matching the "CycleDate" (a date field) to BUDate. BUdate has the
correct value in it, but me.DOC is continually showing null.

Is there something wrong with my syntax or is there more likely to be
something wrong on the form? I'm using Dlookup elsewhere in my code and it's
performing as it should...

Thanks in advance,
Mike

 

[Mike]

Doh!!!

Answering my own question, but it was helpful to me

I forgot the hashes around the date field:

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate = #" & BUDate & "#")

 

[Rick Brandt]

Hi All,

I reckon I've tried every combination of single quotes, double quotes
and brackets available, and not one is working. Here's the code:

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate =" & BUDate)

This is supposed to return a (long integer) number from the
"CycleDay" field after matching the "CycleDate" (a date field) to
BUDate. BUdate has the correct value in it, but me.DOC is continually
showing null.

Is there something wrong with my syntax or is there more likely to be
something wrong on the form? I'm using Dlookup elsewhere in my code
and it's performing as it should...

Thanks in advance,
Mike

Date literals in Access are delimited with #.

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate =#" & BUDate & "#")

 

[Peter]

Hi Mike
Have tried to put cDate(BUDate) in stead of BUDate in the critera part of
DLookup?
(Just to ensure that it's a Date data type.)
Is date format the same for BUDate as for CycleDate, need's to format BUDate?

What happends if you tries with a valid date right in your DLookup?
Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate = #17/03/07#")
Try with a date that exeist in a record you table "TblDayofCycle".

 

[Mike]

Thanks all,

I've made some progress, but I'm coming up with some unexpected results.

To give a clearer picture, what I'm trying to do is use a datepicker to
select a date (BUDate) - this is working ok . Then I'm trying to match this
to a date (CycleDate) in a table (TblDayOfCycle) (which has an associated
Day Of Cycle (CycleDay) which is a number 1 - 28. This is all working fine
so far.

I then use that number to match a day of cycle (DOC) in a table specific to
another parameter (BUsystem). This is where I'm encountering the problem.

It is working correctly for some/most dates but not all.

It either:

1. Gets the correct cycle number ;

2. Gets the wrong cycle number (for example 22 consistently returns for 12
March 2007, when this should be cycle day 8. 13 March 2007 correctly returns
cycle day 9 correctly as do all the following dates up to 31 March (cycle day
27);

3. Gives a null value. 1st April for example.

I've tried looking the data up from a query, with the same result. I've
tried rebuilding the table from fresh data (imported, apparently correctly,
from Excel).

Any ideas on what's wrong, or an alternative way of doing things?

Here's the full code - note that I'm opening tables purely to have a look at
what results I'm getting, and to confirm dlookup and findrecord are behaving
the same.

Private Sub BUDateUsed_Click()
InputDateField BUDate, "Select Date"

PolicyTbl = "TblPolicy" & Me.BUSystem

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate =#" & BUDate & "#")

DoCmd.OpenTable PolicyTbl
DoCmd.FindRecord Me.DOC, , True, , True, False

Me.BUOffsiteDuration.Value = DLookup("[DOCOffsite]", PolicyTbl, "DOC =" &
Me.DOC)

DoCmd.OpenTable "TblDayofCycle"
DoCmd.FindRecord BUDate, , True, , True, False

Me.BURetentionDuration.Value = DLookup("[DOCRetention]", PolicyTbl, "DOC ="
& Me.DOC)
Me.BUDateOnsite = DateAdd("d", (Me.BUOffsiteDuration) + 1, [BUDate])

End Sub

Thanks,
Mike

 

[Mike]

Talking to myself again I know

I think I've found the problem, but can't work out how to fix it...

The "valid" dates are coming through as dd/mm/yyyy (as they should be in my
part of the world), but the ones that are invalid are somehow coming out as
mm/dd/yyyy - therefore the date 12th March 2007 (12/03/2007) is being
interpreted as 3rd December 2007 (03/12/2007), which equates to cycle day 22.

It might explain why 13th March - 31st March are being interpreted ok (no
13-31 months), and I think it shows why 1st April (01/04) is null as it
can't interprate as 4th Jan since my query data starts on 5th March.

So how can I get the date coming out correctly in all cases? I've tried
format dd/mm/yyyy, and my table and query formats are set this way too...

Please help.

Mike

Thanks all,

I've made some progress, but I'm coming up with some unexpected results.

To give a clearer picture, what I'm trying to do is use a datepicker to
select a date (BUDate) - this is working ok . Then I'm trying to match this
to a date (CycleDate) in a table (TblDayOfCycle) (which has an associated
Day Of Cycle (CycleDay) which is a number 1 - 28. This is all working fine
so far.

I then use that number to match a day of cycle (DOC) in a table specific to
another parameter (BUsystem). This is where I'm encountering the problem.

It is working correctly for some/most dates but not all.

It either:

1. Gets the correct cycle number ;

2. Gets the wrong cycle number (for example 22 consistently returns for 12
March 2007, when this should be cycle day 8. 13 March 2007 correctly returns
cycle day 9 correctly as do all the following dates up to 31 March (cycle day
27);

3. Gives a null value. 1st April for example.

I've tried looking the data up from a query, with the same result. I've
tried rebuilding the table from fresh data (imported, apparently correctly,
from Excel).

Any ideas on what's wrong, or an alternative way of doing things?

Here's the full code - note that I'm opening tables purely to have a look at
what results I'm getting, and to confirm dlookup and findrecord are behaving
the same.

Private Sub BUDateUsed_Click()
InputDateField BUDate, "Select Date"

PolicyTbl = "TblPolicy" & Me.BUSystem

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate =#" & BUDate & "#")

DoCmd.OpenTable PolicyTbl
DoCmd.FindRecord Me.DOC, , True, , True, False

Me.BUOffsiteDuration.Value = DLookup("[DOCOffsite]", PolicyTbl, "DOC =" &
Me.DOC)

DoCmd.OpenTable "TblDayofCycle"
DoCmd.FindRecord BUDate, , True, , True, False

Me.BURetentionDuration.Value = DLookup("[DOCRetention]", PolicyTbl, "DOC ="
& Me.DOC)
Me.BUDateOnsite = DateAdd("d", (Me.BUOffsiteDuration) + 1, [BUDate])

End Sub

Thanks,
Mike

 

[John W. Vinson]

So how can I get the date coming out correctly in all cases? I've tried
format dd/mm/yyyy, and my table and query formats are set this way too...

Dates are ALWAYS stored with *no* formatting. A date is stored as a Double
Float count of days (and fractions of a day, for times) since midnight,
December 30, 1899.

Literal dates in a Query or a DLookUp must be entered in either the American
mm/dd/yyyy format, or in an unambiguous format such as 04-Apr-2007. The
programmers who developed Access were in America and chose to do it this way.
Maybe not ideal, but that's just the way it is!

You can use

Me.DOC = DLookup("CycleDay", "TblDayofCycle", "CycleDate =#" &
Format([BUDate]. "mm\/dd\/yyyy") & "#")

to coerce the date into a format that DLookUp can handle correctly.

John W. Vinson [MVP]

 

[Douglas J. Steele]

I recommend using an international format (ISO 8601) and, considering
'Date' values are in effect 'DATETIME' values (even in VBA), include
all elements i.e.

#yyyy-mm-dd hh:nn:ss#

I'm not sure whether or not the above satisfies your 'unambiguous'
rule but it certainly works fine.

You'd think so, but I've actually had people ask me how to represent dates
in yyyy-dd-mm format. <g>

 

[Mike]

Thanks guys,

I was fiddling around and on the point of either converting the dates to a
string or possibly julian, when all of a sudden it started working!?

I don't know how I did it, but things are great now. My gut feeling was that
it was able to be fixed, since *most *of the dates were ok - you'd expect
them either to be all right or all wrong. I've also worked with dates a bit
in the past (in this part of the world) and never had an insurmountable
problem.

Mike

 

[John W. Vinson]

I don't know how I did it, but things are great now. My gut feeling was that
it was able to be fixed, since *most *of the dates were ok - you'd expect
them either to be all right or all wrong. I've also worked with dates a bit
in the past (in this part of the world) and never had an insurmountable
problem.

The nub of the problem is that 04/09/2007 is a valid representation for April
9th *OR* for September 4th, depending on your regional settings; and (as often
happens) Microsoft is "too helpful". If you enter that date it will interpret
it in accord with your regional settings (which might or might not match the
user's intention!!!). Even worse, if the user enters something that makes no
sense in terms of the current regional setting, but does make sense in the
alternative, Access will actually SWITCH what the user typed - e.g. if you
have mm/dd/yyyy as your setting and type "30/04/2007" you'll get NO error, no
warning, and an entry of April 30 - displayed "04/30/2007"!

John W. Vinson [MVP]

 

[John W. Vinson]

SELECT DATEPART('M', #2007-30-04 00:00:00#)
;

will error, which would appear to be the behaviour you require.
Therefore, the recommendation for the ISO 8601 date representation
stands.

Thanks, Jamie! That's really good to know.

John W. Vinson [MVP]