• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 842
  • Last Modified:

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

0
lrbrister
Asked:
lrbrister
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
AssignmentNumber = DLookup("[Assignment Number]", "Assignment", "[Lead ID]=" & Me.Parent.[Lead ID] & " AND #" & [STE End Date] + 7 & "#  > #" & getdate() & "#")


 If Lead ID is a numeric...

Jim.
0
 
Rey Obrero (Capricorn1)Commented:
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() & "#")
0
 
Gustav BrockCIOCommented:
GetDate() is T-SQL. In Access it is Date()

This is how to do it:

If Lead ID is Text:

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

If Lead ID is Number:

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

/gustav
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
lrbristerAuthor Commented:
Ok guys
Cactus data was closest.
0
 
lrbristerAuthor Commented:
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())")
0
 
Gustav BrockCIOCommented:
> No single quoyes needed on the dateadd d

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

/gustav
0
 
lrbristerAuthor Commented:
Yeah...
The DLookup is looking at the SQL Tables.
My apologies for not being more precise in my original question
0
 
Gustav BrockCIOCommented:
OK, thanks!

/gustav
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now