Solved

SQL to select unpopulated date

Posted on 2007-03-31
10
292 Views
Last Modified: 2012-05-05
In my Access 2000 app I need to build a query with a compound condition.  The first two conditions are numeric conditions but the last condition is that I want to pull any record that does not have its date field populated.
Here is what I have so far to satidfy my first two conditions.  What would I need where I have ??????? for the third condition?  The date feild that I want to query is named 'EndStatusDate' and I want the ones that have not been populated.
'
selectString = "Select EndStatusDate From " & wkTable
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and [EndStatusDate] = " & ??????????
'
selectString = selectString & whereCond
0
Comment
Question by:mlcktmguy
  • 3
  • 3
  • 2
  • +1
10 Comments
 
LVL 26

Expert Comment

by:jerryb30
ID: 18829996
isnull([endStatusDate]
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 125 total points
ID: 18829999
forgot )
isnull([endStatusDate])
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 18830050
Thanks for your response.  It seems correct but when I run the following:
'
wkTable = " tblDealHistory"
'
selectString = "Select *  From " & wkTable
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and IsNull([EndStatusDate]) "
'
selectString = selectString & whereCond
'
Dim rsOldHist As ADODB.Recordset
Set rsOldHist = New ADODB.Recordset
rsOldHist.Open selectString, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

processing stops on the last statment (rsOldHist.Open...) and I get an error

'No value given for one or more required parameters.'
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 33

Assisted Solution

by:Mike Eghtebas
Mike Eghtebas earned 125 total points
ID: 18830272
With null/empty fields in a SQL with WHERE statement, use field names not *.

Like:

     'EndStatusDate may have null/empty values--v
selectString = "Select DealD, OldStatus, nz(EndStatusDate) As StDate From " & wkTable
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and IsNull([EndStatusDate]) "
'
selectString = selectString & whereCond

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 18830292
Alternates:

selectString = "Select DealD, OldStatus, nz(EndStatusDate,#1/1/111#) As StDate From " & wkTable
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and nz(EndStatusDate,#1/1/111#)=#1/1/1111#"
'
selectString = selectString & whereCond

or maybe just (maybe):

selectString = "Select DealD, OldStatus, nz(EndStatusDate,#1/1/111#) As StDate From " & wkTable
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and StDate = #1/1/1111#"
'
selectString = selectString & whereCond

Mike
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 18830770
Once again thanks for responding.  One at a time I cut and pasted all three of your suggestions into the module.  I get exactly the same error as before.

This is the table definition of the table that I am querying.  Do you see anything else that might be wrong?

         RecordID                              Long Integer                             4
         DealID                                   Long Integer                             4
         OldStatus                              Long Integer                             4
         NewStatus                            Long Integer                             4
         StartStatusDate                     Date/Time                                8
         EndStatusDate                       Date/Time                                8
         Comments                              Memo                                     -
         ActualDate                             Date/Time                                8
         UserID                                     Text                                    20
0
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 125 total points
ID: 18830776
try

selectString = "Select *  From ["& wkTable &"]
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and IsNull([EndStatusDate]) "
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 18830788
is wkTable a variable?

selectString = "Select *  From ["& wkTable &"]
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and IsNull([EndStatusDate]) "

selectString= selectString & whereCond

if not

selectString = "Select *  From  wkTable"
whereCond = " Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and IsNull([EndStatusDate]) "

selectString= selectString & whereCond
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 18830813
I missed the brackets around the table name. If still needed, try:

selectString = "Select DealD, OldStatus, nz(EndStatusDate,#1/1/111#) As StDate From [" & wkTable
whereCond = "] Where [DealD] = " & gDealID
whereCond = whereCond & " and [OldStatus] = " & gPriorStatus
whereCond = whereCond & " and nz(EndStatusDate,#1/1/111#)=#1/1/1111#"
'
selectString = selectString & whereCond
0
 
LVL 1

Author Comment

by:mlcktmguy
ID: 18831024
I am embarrased.  jerryb30 supplied a response that was correct.  I kept getting the error because I had used the data name "DealD" instead of "DealID".  By rights I have to give the point to the first correct answer but I appreciate the answers supplied by the other responders.  They gave me some creative ways of handling date queries.  How do I resolve this?  I am going to triple the points and give the original 125 to all responders.
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

774 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question