help with Dlookup

[Guest]

Thanks for any help.
I have some VB code that should get a value for a field from the table. But
it (Dlookup) is not working the way I think it should. If anyone could help,
I would much appreciate it. Here's the code:

Private Sub Command72_Click() 'runs off a button on the form
Dim LastDate As Date, DayAgoDate As Date
LastDate = DLast("[Date]", "DLY_FINL") 'gets the last date value in the table
LastRate1yrUSTsy = DLast("[1yr US Tsy]", "DLY_FINL") 'gets the last 1YRUSTsy
DayAgoDate = DateAdd("d", -1, LastDate) 'subtracts one from the day
'the next line causes problems
DayAgoRate1yrUSTsy = DLookup("[1yr US Tsy]", "DLY_FINL", "[Date]=" &
DayAgoDate) 'should return the value in the field "[1yr US Tsy]"

But dlookup returns the value of NULL for DayAgoRate1yrUSTsy .
But I checked the DLY_FINL table, and there is that date for that field in
the table.
And there is a numeric value for [1yr US Tsy] also.
I've tried changing how I declare the variable, to variant and string. And I
also tried:
DayAgoRate1yrUSTsy = DLookup("[1yr US Tsy]", "DLY_FINL", "[Date] =
#04/24/2006#")
And this works, it gets the value of that field.
If I try:
DayAgoRate1yrUSTsy = DLookup("[1yr US Tsy]", "DLY_FINL", "[Date] =
DayAgoDate")]
I get an error message saying I canceled the previous operation.

If I add an "#" before and after the string, then it works too:
DayAgoDate = "#" & DateAdd("d", -1, LastDate) & "#"
DayAgoRate1yrUSTsy = DLookup("[1yr US Tsy]", "DLY_FINL", "[Date]=" &
DayAgoDate)

But it seems like I shouldn't need to do this...

I looked at the help for Dlookup, and the way I am doing it should work,
here's a snippet from the help file:
{
The next example uses a variable, intSearch, to get the value.

Dim intSearch As Integer
Dim varX As Variant

intSearch = 1
varX = DLookup("[CompanyName]", "Shippers", _
"[ShipperID] = " & intSearch)
}

If anyone has an pointers, would like to hear them.
Thanks again.

 

[chris.nebinger]

Ian,

Your logic is so close:

Your problem is formatting the criteria for the DCount function (or any
of the domain functions). If you didn't use a variable, then you could
use:

DayAgoRate1yrUSTsy = DLookup("[1yr US Tsy]", "DLY_FINL", "[Date] =
#04/24/2006#")

However, you need to replace the date with a variable. It now becomes:

DayAgoRate1yrUSTsy = DLookup("[1yr US Tsy]", "DLY_FINL", "[Date] =
#" & DayAgoDate & "#")

When the code is executed, the criteria portion is changed to reflect
the correct syntax.

Note:
A few pointers.

The above example assumes a date type. If it was a string, you would
need to replace the # signs with apostrophes ('). So, it would look
like:



StringVariable= DLookup("Field", "DLY_FINL", "[StringField] =
'" & AnotherStringVariable & "'")


In order to get the previous day, you could write it two ways:
DayAgoDate = DateAdd("d", -1, LastDate)
OR
DayAgoDate = LastDate -1

It depends on your preference, really.


HTH,


Chris Nebinger

 

[Guest]

Thanks very much Chris!