Solved

SQL to select unpopulated date

Posted on 2007-03-31
10
282 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
 
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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 
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 119

Assisted Solution

by:Rey Obrero
Rey Obrero 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 119

Expert Comment

by:Rey Obrero
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now