DLOOKUP IF STATEMENTS

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a function which I use to detrmine when an email should be sent

Dim Emp As Variant
Dim Emp_2 As Variant
Dim Stats As String

Stats = DLookup("[STATUS]", "[TEST]", "[EMAIL_STATUS]= 'To be sent'")
Emp = DLookup("[EMP_NAME]", "[TEST1]", "[Emp_name] ='Test ER'")
Emp_2 = DLookup("[EMP_NAME]", "[TEST1]", "[Emp_name] ='TEST ER2'")

the above seems ok however when i use an if statement ie
IF emp = "test ER" and Sats = "To Be Sent"
Run emailfunction
end if

however this method seems to generate emails even if the stats is not set as
"To Be Sent", Have I inadvertenly set stats as "To Be Sent" which is why it
sends emails out already?

need some pointers as to were I have gone wrong, any help would be appreciated

Cheers
 
NO. DLookup() as well as the other Domain functions - DCount(), DLast(),
etc. only retrieve data. They do no modify it. Not certain what's going
on, however, I did notice that the IF statement is missing a THEN at the
end AND that that you are checking Sats = "To Be Sent" as opposed to
Stats = "To Be Sent" note the omission of the 'T'. Also, I don't fully
understand the first DLookup(). What information are you trying to get
from the function? Based on the field [STATUS] and the wherecriteria
[EMAIL Status] = 'To Be Sent', it looks as if you'd ALWAYS be returning
'TO BE SENT' to begin with. It is possible to use the assorted operators
(AND, OR, etc) in a DLookup() as in

Emp = DLookup("Emp_name, "test1", "emp_name = 'Test ER' AND status = 'to
be sent'")

Of course, if you already have the emp_name 'TEST ER' why do you need to
look it up at all? It appears that you're looking up information that
you already have. DLookup() is best used when you have some information,
but need other information. An example would be looking up an email
address for an employee.

David H
 
Songoku said:
I have a function which I use to detrmine when an email should be sent

Dim Emp As Variant
Dim Emp_2 As Variant
Dim Stats As String

Stats = DLookup("[STATUS]", "[TEST]", "[EMAIL_STATUS]= 'To be sent'")
Emp = DLookup("[EMP_NAME]", "[TEST1]", "[Emp_name] ='Test ER'")
Emp_2 = DLookup("[EMP_NAME]", "[TEST1]", "[Emp_name] ='TEST ER2'")

the above seems ok however when i use an if statement ie
IF emp = "test ER" and Sats = "To Be Sent"
Run emailfunction
end if

however this method seems to generate emails even if the stats is not set as
"To Be Sent", Have I inadvertenly set stats as "To Be Sent" which is why it
sends emails out already?

need some pointers as to were I have gone wrong, any help would be appreciated
Click to expand...


When asking people to help debug code, it is important that
you post a Copy/Paste of the real code you are using. It is
a gross waste of time for people to debug the typos
introduced by you retyping a synopsis of your code.

Your general logic seems fine, except that you probably
should check the results of a DLookup for Null. I don't see
how that would cause the code you posted to fail in the way
you describe, so I think there may be something else going
on.
 
I see 2 things you should have a look at.
1.
In this line, you are return [STATUS] to Stats. Will is always be the same
as [EMAIL_STATUS] ?
Stats = DLookup("[STATUS]", "[TEST]", "[EMAIL_STATUS]= 'To be sent'")
If not, change it to:
Stats = DLookup("[EMAIL_STATUS]", "[TEST]", "[EMAIL_STATUS]= 'To be sent'")
2.
In this line, you are referring to a variable nameed Sats, which is not the
same as Stats:
IF emp = "test ER" and Sats = "To Be Sent"

If you are not using Option Explicit in your modules, shame on you. Option
Explicit prevents this kind of problem.
 
Back
Top