asked on

DLookup Syntax

Been forever since I used DLookup or MS Access VBA
Im trying to get the assignment number from the Assignments Table where [Lead ID] = Me.Parent.[Lead ID] AND [STE End Date] + 7 > today.

I'm just not sure if my code below is correct. Does the DLookup need the getdate() from sql server or the Access Syntax for today?

And is my code below correct?

    Dim AssignmentNumber As Variant
    AssignmentNumber = DLookup("[Assignment Number]", "Assignment", "[Lead ID]='" & Me.Parent.[Lead ID] & "' AND ([STE End Date] + 7) > getdate()")

Open in new window

Jim Dettman (EE MVE)

AssignmentNumber = DLookup("[Assignment Number]", "Assignment", "[Lead ID]=" & Me.Parent.[Lead ID] & " AND #" & [STE End Date] + 7 & "# > #" & getdate() & "#")

If Lead ID is a numeric...

Jim.

Rey Obrero (Capricorn1)

why not use Date()

if LEad ID is Text
AssignmentNumber = DLookup("[Assignment Number]", "Assignment", "[Lead ID]='" & Me.Parent.[Lead ID] & "' AND #" & [STE End Date] + 7 & "# > #" & date() & "#")

if LEad ID is Number

AssignmentNumber = DLookup("[Assignment Number]", "Assignment", "[Lead ID]=" & Me.Parent.[Lead ID] & " AND #" & [STE End Date] + 7 & "# > #" & date() & "#")

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Larry Brister

ASKER

Ok guys
Cactus data was closest.

Larry Brister

ASKER

The closest.

This was my final solution...
No single quoyes needed on the dateadd d

AssignmentNumber = DLookup("[Assignment Number]", "Assignment", "[Lead ID]=" & Me.Parent.[Lead_id] & " And [STE End Date] > DateAdd(d, -7, getdate())")

Gustav Brock

> No single quoyes needed on the dateadd d

So this is T-SQL? Or? It won't work in Access.

/gustav

Larry Brister

ASKER

Yeah...
The DLookup is looking at the SQL Tables.
My apologies for not being more precise in my original question

Gustav Brock

OK, thanks!

/gustav