Dlookup Problem

[geoffcodd]

I have the following formula which I just can't get to work the Point_Id is a Numeric field and the Date field is a Date Field

Dlookup("[M1_Present]","DataElectricity","[Point_Id] = '" & [Points].[Id] & "' And [Date] = #" & [Date] & "#"))

Any help would be greatly appreciated

Thanks
Geoff

 

[DBL]

Something to be aware of, if you're using DLookup for a date, it will look for it in the US format so it could be that you're syntax is okay.

I get round it by using this function. In a new module:

Function MakeUSDate(X As Variant)
On Error GoTo ErrorHandler

If Not IsDate(X) Then Exit Function
MakeUSDate = "#" & Month(X) & "/" & Day(X) & "/" & Year(X) & "#"

Errorhandlerexit:
Exit Function

ErrorHandler:
MsgBox Err.Description
Resume Errorhandlerexit

End Function

and then specify the Date field at

UKDate = MakeUSDate([DateField])

Dlookup("[M1_Present]","DataElectricity","[Point_Id] = '" & [Points].[Id] & "' And [Date] = #" & [UKDate] & "#"))

Something else to be aware of the word "Date" is a reserved word within Access so you shouldn't really use it as a field name. Search the forum for "reserved" for a bucket load on the stuff

 

[Jon K]

>
Dlookup("[M1_Present]","DataElectricity","[Point_Id] = '" & [Points].[Id] & "' And [Date] = #" & [Date] & "#"))
<


Since Point_ID is a numeric field, remove the two single quotes (the single quotes are required only if Point_ID is a text field). There is also an extra ) at the end of your DLookup() function. So the function should be:

Dlookup("[M1_Present]","DataElectricity","[Point_Id] = " & [Points].[Id] & " And [Date] = #" & [Date] & "#")

 

[Jon K]

I think [Points].[Id] and the [Date] in #" & [Date] & "#" each refers to a field in table Points. Probably Geoff is using the function in the Select clause or the Where clause in a query. For example:

SELECT *, Dlookup("[M1_Present]","DataElectricity","[Point_Id] = " & [Points].[Id] & " And [Date] = #" & [Date] & "#")
FROM Points;

Or:

SELECT *
FROM Points
WHERE aField= Dlookup("[M1_Present]","DataElectricity","[Point_Id] = " & [Points].[Id] & " And [Date] = #" & [Date] & "#");

Of course, without the necessary information, it is all guesswork and I might guess wrong, but given the structure and data in tables Points and DataElectricity, the function in the above two Select statements works.

DBL gave good advice. Though [Date] works in the above function, I personally avoid using reserved words as field names and table names.


Edit:

DBL is absolutely correct. DLookup() looks for the date only in US format. So if the Windows' setting is not in US format, DBL's MakeUSDate() function should be used.