Querying Null Date Value

[Ice Rhino]

I have a form that provides information to a query utilising Like statements, as displayed below

SELECT tblArchive.DateTo, tblArchive.IndividualsName, tblArchive.BoxNo, tblArchive.Reference, tblArchive.TenantorDescription, tblArchive.ToBeDestroyed, tblArchive.DateDestroyed, tblArchive.DateFrom
FROM tblArchive
WHERE (((tblArchive.IndividualsName)="Acquisitions") AND ((tblArchive.BoxNo) Like "*" & [Forms]![frmarchivesearch]![txtinvboxno] & "*") AND ((tblArchive.Reference) Like "*" & [Forms]![frmarchivesearch]![txtinvref] & "*") AND ((tblArchive.TenantorDescription) Like "*" & [Forms]![frmarchivesearch]![txtinvdes] & "*") AND ((tblArchive.DateFrom) Like "*" & [Forms]![frmarchivesearch]![txtinvdatefrom] & "*"))
ORDER BY tblArchive.IndividualsName, tblArchive.BoxNo;

The problem I have is that the query works great as long as there is not a null value in the datefrom field. I have a field that contains individual's names. Only one of these names results in a value being placed in the date from field.

What options do I have? I was condering a zero value date (00/00/0000) and then just hiding that value through code and formatting. Not sure that MS Access will allow me to do that.

Anybody got any ideas. I have tried a second line in the query, basically an OR statment revolving around blah blah datefrom is null, but then that screws up the search set and gives the wrong results.

Anybody got any ideas?

 

[Samoan]

Something like this may work for you. Replace

AND (tblArchive.DateFrom) Like "*" & [Forms]![frmarchivesearch]![txtinvdatefrom] & "*"

with

AND IIF([Forms]![frmarchivesearch]![txtinvdatefrom] Is Null, (tblArchive.DateFrom) Is Null, (tblArchive.DateFrom) Like "*" & [Forms]![frmarchivesearch]![txtinvdatefrom] & "*"

This will look to see if your textbox is null. If it is, the query will only select those records from the table where the DateFrom is null. If it is not null, it will select those records whose DateFrom is like the text in the txtinvdatefrom field.

I do wonder if you should use a "Like" statement on the date field. With dates, it is usually better to use =, <, or > for evaluations. However, you may have a valid reason to use Like.

I hope this is what you are looking for.

 

[Ice Rhino]

It does not appear to like that line of code, it keeps saying that there is a missing parenthasis in the expression. Damned if I can find it.

Well obviously I can't find it because it is missing, but can find where it is supposed to go

Regards

 

[Brianwarnock]

Have you put one right at the end?

Brian

 

[Ice Rhino]

OK, I found that the bracket needed to go at the end, however it still does not work.

Any ideas?

 

[Brianwarnock]

I thought that all you needed was to add a test for is not null,as per below

AND ((tblArchive.DateFrom) Like "*" & [Forms]![frmarchivesearch]![txtinvdatefrom] & "*") And (tbleArchive.DateFrom) Is Not Null)

Brian

 

[Ice Rhino]

Ok, understood, what is the value Is Null as part of the search parameter.

10% of the entries in that field only contain a value other than Null. The user will select either a date value, or by default, unless otherwise selected, the value will be null in the date field

Regards

 

[Ice Rhino]

The IIF statement feels like it should be right. Logically in my head it sounds like

If the value of the search box is Null then search for Null, if the value of the box is not null, then search for the date listed

Just can't understand why it does not work

Regards

 

[Brianwarnock]

I'm puzzled , last week you wanted to do this

If so, what is the solution to the above line where if nothing is present in the box search for everything within that particular date field, however if there is a value that is not null then search for that

.

Now if the box is null you want to search for null , is this the same query?

I coded a simple query using code like Samoan's extracting records where datefield is null if form field is null else the particular date as coded.

Since you are now looking for Null or specific dates why do you now need Like ?

Brian

 

[Ice Rhino]

I have no need to use like now. I have rewritten that part.

basically I want this

If that search parameter box Is Null, then as part of the query search for Null in that field amongst the other search criteria. If the value of the Box Is Not Null, then use the date that is in there for the search parameter in amongst the other parameters.

I am sure this is easy, perhaps I am just not explaining it properly. I am confused myself so heaven only knows how you guys must be feeling about this problem

Regards

 

[Samoan]

It might help if you give a couple lines of data, and what data you expect to be returned when the date box is null, and what data would be returned when the date box is not null.

 

[Ice Rhino]

Perhaps there is another way around this. I remember once writing a peice of code that created the query definition on the fly in VB. Perhaps rather than using wildcard search routines, it may be easier to recreate the query based on a

If the value in this box Is Null then drop and recreate the definition using this SQL statement

If the value in this box <>Null then drop and recreate the definition using this SQL statement

How about that? DOes that sound easier?

 

[Ice Rhino]

Ok, attached to this is a sample of the data i am querying

If on the form the user has selected from individualname the value of 'Tenant', they may also select a DateFrom value or leave it blank to show all the records that are listed as Tenant.

if they select any other value other than that of 'Tenant', then they have no option to select a DateFrom as the value for the date will always be Is Null'

So, to sum up

1. If 'Tenant' selected then either search for all dates or only those dates if an entry has been made inthe DateFrom box

2. If <> 'Tenant' then search for all records using the fact that DateFrom Is Null

There are a number of other boxes that the user has the option to filll in, but it is only this one that is causing the problem. Works great apart from that but I guess that is because they are not date fields

Is that any clearer?

Regards

 

[Samoan]

There are certainly multiple ways to solve this problem, and creating a query definition on the fly may be one of them.

I sometimes will store SQL in a string, and then use that string as a record source for the form that will return the data to the user.

For instance:

strSQL = "SELECT * from Table1"
Form1.RecordSource = strSQL

You could set up strSQL to be different, depending on the values of your text boxes.

There is also the QueryDef object, but I have not used that method before.

I'm not sure which is the best way to go about it though, and I don't want to lead you down the wrong path.

 

[Samoan]

All right, in looking at your data and reading your post I think I understand.

Try this in your query:

AND IIF([Forms]![frmarchivesearch]![txtinvdatefrom] Is Null, nz((tblArchive.DateFrom),"0") like "*", (tblArchive.DateFrom) Like "*" & [Forms]![frmarchivesearch]![txtinvdatefrom] & "*"

What this should do is:

If the date field on the form is null
replace any null DateFrom values with 0 (this is what the nz function does)
select all records where date like *, which should be all of them
Else the date field is not null
Only select those records whose dates match the date field on the form.

There may be a cleaner way to do this, but I think this may work for you.

 

[Ice Rhino]

here is the coding route I am going down, the problem is that I end up with the same logic problem if the user does not wish to specify a date. Fine if they do, screwed if they don't


Private Sub cmdsubqry_Click()
Dim myDB As Database, MyQuery As QueryDef, strSQL As String
Dim Response

Forms![frmarchivesearch]![qrysrchresset].Form.Visible = True
Forms![frmarchivesearch]![qrysrchresset].Form.Requery

Set myDB = CurrentDb()
If Me.cbxIndNam.Value <> "Tenant" Then GoTo nottenantsql
If Me.cbxIndNam.Value = "Tenant" Then GoTo tenantsql

nottenantsql:
DoCmd.DeleteObject acQuery, "qrysrchresset"
Set MyQuery = myDB.CreateQueryDef("qrysrchresset")
strSQL = "SELECT tblArchive.DateTo, tblArchive.IndividualsName, tblArchive.BoxNo, tblArchive.Reference, tblArchive.TenantorDescription, tblArchive.ToBeDestroyed, tblArchive.DateDestroyed, tblArchive.DateFrom"
strSQL = strSQL + " FROM tblArchive"
strSQL = strSQL + " WHERE (((tblArchive.IndividualsName) Like '*" & [Forms]![frmarchivesearch]![txtinvIndNam] & "*') AND ((tblArchive.BoxNo) Like '*" & [Forms]![frmarchivesearch]![txtinvBoxNo] & "*') AND ((tblArchive.Reference) Like '*" & [Forms]![frmarchivesearch]![txtinvref] & "*') AND ((tblArchive.TenantorDescription) Like '*" & [Forms]![frmarchivesearch]![txtinvdes] & "*') AND ((tblArchive.DateFrom) Is Null))"
strSQL = strSQL + " ORDER BY tblArchive.IndividualsName, tblArchive.BoxNo;"
MyQuery.SQL = strSQL
Exit Sub

tenantsql:
DoCmd.DeleteObject acQuery, "qrysrchresset"
Set MyQuery = myDB.CreateQueryDef("qrysrchresset")
strSQL = "SELECT tblArchive.DateTo, tblArchive.IndividualsName, tblArchive.BoxNo, tblArchive.Reference, tblArchive.TenantorDescription, tblArchive.ToBeDestroyed, tblArchive.DateDestroyed, tblArchive.DateFrom"
strSQL = strSQL + " FROM tblArchive"
strSQL = strSQL + " WHERE (((tblArchive.IndividualsName) Like '*" & [Forms]![frmarchivesearch]![txtinvIndNam] & "*') AND ((tblArchive.BoxNo) Like '*" & [Forms]![frmarchivesearch]![txtinvBoxNo] & "*') AND ((tblArchive.Reference) Like '*" & [Forms]![frmarchivesearch]![txtinvref] & "*') AND ((tblArchive.TenantorDescription) Like '*" & [Forms]![frmarchivesearch]![txtinvdes] & "*') AND ((tblArchive.DateFrom)=[Forms]![frmarchivesearch]![txtinvdatefrom]))"
strSQL = strSQL + " ORDER BY tblArchive.IndividualsName, tblArchive.BoxNo;"
MyQuery.SQL = strSQL
Exit Sub

End Sub


Regards

 

[Brianwarnock]

Had trouble getting on the forum and gotta go now but had tried to on a simple db of mine with this

SELECT Table1.status, Table1.date2, Table1.cost
FROM Table1
WHERE ([Table1].[cost]=4) AND ((IIf([forms]![form2]![text0] Is Null,[table1].[date2] Is Null,[Table1].[date2]=[forms]![form2]![text0]))<>False);

And it appeared to work if date quoted pulled all with that date and cost=4 if date on form null pulled where date in table null and cost=4

brian

 

[Ice Rhino]

Ok, why does the code go straight to the nottenantsql:
irespective of the values in the boxes it is checking on the form?

I am so damn close I can almost feel the solution to this problem


Private Sub cmdsubqry_Click()
Dim myDB As Database, MyQuery As QueryDef, strSQL As String
Dim Response

Forms![frmarchivesearch]![qrysrchresset].Form.Visible = True
Forms![frmarchivesearch]![qrysrchresset].Form.Requery
Exit Sub

Set myDB = CurrentDb()
If ([Forms]![frmarchivesearch]![txtinvIndNam].Value) <> "Tenant" Then GoTo nottenantsql
If ([Forms]![frmarchivesearch]![txtinvIndNam].Value) = "Tenant" And ([Forms]![frmarchivesearch]![txtinvDateFrom].Value) = Null Then GoTo tenantwithoutdatesql
If ([Forms]![frmarchivesearch]![txtinvIndNam].Value) = "Tenant" And ([Forms]![frmarchivesearch]![txtinvDateFrom].Value) <> Null Then GoTo tenantwithdatesql

nottenantsql:
DoCmd.DeleteObject acQuery, "qrysrchresset"
Set MyQuery = myDB.CreateQueryDef("qrysrchresset")
strSQL = "SELECT tblArchive.DateTo, tblArchive.IndividualsName, tblArchive.BoxNo, tblArchive.Reference, tblArchive.TenantorDescription, tblArchive.ToBeDestroyed, tblArchive.DateDestroyed, tblArchive.DateFrom"
strSQL = strSQL + " FROM tblArchive"
strSQL = strSQL + " WHERE (((tblArchive.IndividualsName) Like '*" & [Forms]![frmarchivesearch]![txtinvIndNam] & "*') AND ((tblArchive.BoxNo) Like '*" & [Forms]![frmarchivesearch]![txtinvBoxNo] & "*') AND ((tblArchive.Reference) Like '*" & [Forms]![frmarchivesearch]![txtinvref] & "*') AND ((tblArchive.TenantorDescription) Like '*" & [Forms]![frmarchivesearch]![txtinvdes] & "*') AND ((tblArchive.DateFrom) Is Null))"
strSQL = strSQL + " ORDER BY tblArchive.IndividualsName, tblArchive.BoxNo;"
MyQuery.SQL = strSQL
Exit Sub

tenantwithdatesql:
DoCmd.DeleteObject acQuery, "qrysrchresset"
Set MyQuery = myDB.CreateQueryDef("qrysrchresset")
strSQL = "SELECT tblArchive.DateTo, tblArchive.IndividualsName, tblArchive.BoxNo, tblArchive.Reference, tblArchive.TenantorDescription, tblArchive.ToBeDestroyed, tblArchive.DateDestroyed, tblArchive.DateFrom"
strSQL = strSQL + " FROM tblArchive"
strSQL = strSQL + " WHERE (((tblArchive.IndividualsName) Like '*" & [Forms]![frmarchivesearch]![txtinvIndNam] & "*') AND ((tblArchive.BoxNo) Like '*" & [Forms]![frmarchivesearch]![txtinvBoxNo] & "*') AND ((tblArchive.Reference) Like '*" & [Forms]![frmarchivesearch]![txtinvref] & "*') AND ((tblArchive.TenantorDescription) Like '*" & [Forms]![frmarchivesearch]![txtinvdes] & "*') AND ((tblArchive.DateFrom)=[Forms]![frmarchivesearch]![txtinvdatefrom]))"
strSQL = strSQL + " ORDER BY tblArchive.IndividualsName, tblArchive.BoxNo;"
MyQuery.SQL = strSQL
Exit Sub

tenantwithoutdatesql:
DoCmd.DeleteObject acQuery, "qrysrchresset"
Set MyQuery = myDB.CreateQueryDef("qrysrchresset")
strSQL = "SELECT tblArchive.DateFrom, tblArchive.DateTo, tblArchive.IndividualsName, tblArchive.BoxNo, tblArchive.Reference, tblArchive.TenantorDescription, tblArchive.ToBeDestroyed, tblArchive.DateDestroyed"
strSQL = strSQL + " FROM tblArchive"
strSQL = strSQL + " WHERE (((tblArchive.DateFrom) Like " * ") AND ((tblArchive.IndividualsName) Like '*Tenant*') AND ((tblArchive.BoxNo) Like '***') AND ((tblArchive.Reference) Like '***') AND ((tblArchive.TenantorDescription) Like '***'))"
strSQL = strSQL + " ORDER BY tblArchive.IndividualsName, tblArchive.BoxNo;"
Exit Sub

End Sub


Regards

 

[Ice Rhino]

I am fairly sure it is something to do with the use of the Is Null statement in the IF line that is causing the problem. When I go through logical elimination steps, it gives me a 424 Object require error as soon as I put the Is Null element in referencing the txtinvdatefrom box

ummmm?

 

[Brianwarnock]

Hi Toni

what I am suggesting is that you go back to using a query but your date test is as follows


AND (IIF([Forms]![frmarchivesearch]![txtinvdatefrom] Is Null,[tblArchive].[DateFrom] Is Null,[tblArchive].[DateFrom]=[Forms]![frmarchivesearch]![txtinvdatefrom])=True)

If txtinvdatefrom is null you will only select records where DateFrom is null else it will select according to the contents of txtinvdatefrom

Brian