Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

Problems with dlookup criteria

I am not sure what I am doing wrong, but not able to get data based on date and logical fields...

Your help is appreciated.


 
Private Sub TestDLOOKUP_Click()
Dim intDTSJob As String
Dim strDate As Date
strDate = Now()
intDTSJob = DLookup("[DtsNameOfFrmQryRpt]", "[Q_CpsiProcessByPriority]", "[DtsNextDateTime] >= #" & strDate & "#" And [DtsActive] = True)

If IsNull(intDTSJob) Then
  'do nothing
    MsgBox "TEST -- NOTHING TO RUN AT THIS TIME"
Else
  MsgBox "TEST -- READY TO RUN THIS PROCESS..." & intDTSJob
End If

Open in new window

0
epicazo
Asked:
epicazo
  • 6
  • 4
  • 4
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Try this:

intDTSJob = DLookup("[DtsNameOfFrmQryRpt]", "[Q_CpsiProcessByPriority]", "[DtsNextDateTime] >= #" & strDate & "# And [DtsActive] = True")

mx
0
 
danishaniCommented:
Try this;
Private Sub TestDLOOKUP_Click()
Dim intDTSJob As String
Dim strDate As Date
strDate = Now()
intDTSJob = DLookup("[DtsNameOfFrmQryRpt]", "[Q_CpsiProcessByPriority]", "[DtsNextDateTime] >= #" & strDate & "#" & " And [DtsActive] = True")

If IsNull(intDTSJob) Then
  'do nothing
    MsgBox "TEST -- NOTHING TO RUN AT THIS TIME"
Else
  MsgBox "TEST -- READY TO RUN THIS PROCESS..." & intDTSJob
End If

HTH,
Daniel
0
 
epicazoAuthor Commented:
for both I get an invalid use of null, but I do show 2 recs
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.

 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Change

Dim intDTSJob As String

to

Dim intDTSJob As Variant  ' to handle the Null case

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
A variable Dimmed as a String cannot be set to Null, other and error will occur.

mx
0
 
danishaniCommented:
Try this, dim intDTSJob As Variant instead of String;
Private Sub TestDLOOKUP_Click()
Dim intDTSJob As Variant
Dim strDate As Date
strDate = Now()
intDTSJob = DLookup("[DtsNameOfFrmQryRpt]", "[Q_CpsiProcessByPriority]", "[DtsNextDateTime] >= #" & strDate & "#" & " And [DtsActive] = True")

If IsNull(intDTSJob) Then
  'do nothing
    MsgBox "TEST -- NOTHING TO RUN AT THIS TIME"
Else
  MsgBox "TEST -- READY TO RUN THIS PROCESS..." & intDTSJob
End If

HTH,
Daniel
0
 
epicazoAuthor Commented:
Still no pulling any data...  

Not extracting desired outcome...   It's seeign the intDTSjob as NULL
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Have you checked you data ?

Does DtsNextDateTime contain Time?  If not, try:

strDate = Date()  ' instead of Now()

mx
0
 
danishaniCommented:
So the desired result should be 2 records?

Try then intDTSJob as String;

If  intDTSJob = "" Then
  'do nothing
    MsgBox "TEST -- NOTHING TO RUN AT THIS TIME"
Else
  MsgBox "TEST -- READY TO RUN THIS PROCESS..." & intDTSJob
End If

HTH,
Daniel
0
 
epicazoAuthor Commented:
Here is the data...
DtsNameOfFrmQryRpt      DtsActive      DtsNextDateTime
Frm_Someformx      Yes      11/2/2010 2:15:00 PM
Frm_CpsiRadBillingExport      Yes      11/2/2010 2:15:00 PM
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Is DtsActive  a Boolean Data type or Text (I see 'Yes') ?

0
 
epicazoAuthor Commented:
ah.... my bad... I had the <  and >   transposed.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ahh so ...

mx
0
 
danishaniCommented:
So your problem is solved now?

HTH,
Daniel
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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