Learn how to a build a cloud-first strategyRegister Now

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

Query Criteria set as Is Null

I have a form where the user may select a check box. I have a combo box which I want based on the check box.

Basically I want to set a field criteria to Is Null if the check box is selected. I thought about an IIF statement but cannot get it to set Is Null.
0
Doi7722
Asked:
Doi7722
  • 9
  • 6
  • 5
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Example:

If Me.YourCheckBoxName = True Then
   Me.YourComboBoxName = Null
End If

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
btw ... IsNull() is used to test to see if something is Null.

mx
0
 
Doi7722Author Commented:
Is it not possible to do this in a query rather than use vb. The query has other criteria set aswell. I actually want to set the criteria in the query as IS NULL so that the query will return all records equal to Null.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
HainKurtSr. System AnalystCommented:
or use this:

If Me.chkMyCheckBox then
  Me.YourComboBoxName.Value = null
Endif

or

If Me.chkMyCheckBox.Value = True then
  Me.YourComboBoxName.Value = null
Endif
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
HainKurt ... isn't that exactly what I posted ?

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You mean this:

SELECT Table1.*
FROM Table1
WHERE (((Table1.FIELD1)=IsNull([Forms].[YourFormName].[YourComboBoxName])));

mx
0
 
Doi7722Author Commented:
Here is my SQL as you can see I have an IIF statement and I want to [Share Register].[VerifiedDateTime] to be set to IS NULL if the ([Forms]![Deal Recall]![UnverifiedDeals] <> True.
SELECT [Share Register].ID, [Share Register].TransactionDate, [Share Register].Consideration, [Share Register].VerifiedDateTime, [Share Register].CompanyID, [Share Register].InvestorID
FROM [Share Register]
WHERE ((([Share Register].VerifiedDateTime)=IIf([Forms]![Deal Recall]![UnverifiedDeals]<>True,"",([Share Register].[VerifiedDateTime]) = Is Null)) AND (([Share Register].CompanyID)=[Forms]![Deal Recall]![CompanyID]) AND (([Share Register].InvestorID)=[Forms]![Deal Recall]![InvestorID]));
0
 
HainKurtSr. System AnalystCommented:
not exactly same as yours, slightly different version ;)
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
This part is confusing:

IIf([Forms]![Deal Recall]![UnverifiedDeals]<>True,"",([Share Register].[VerifiedDateTime]) = Is Null)

Not clear on what you are trying to do ?

mx

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How is it different ?

mx
0
 
HainKurtSr. System AnalystCommented:
try this

((([Share Register].VerifiedDateTime)=IIf([Forms]![Deal Recall]![UnverifiedDeals]<>True,"",([Share Register].[VerifiedDateTime]) = Is Null))
-->
((not [Forms]![Deal Recall]![UnverifiedDeals].Value) and ([Share Register].[VerifiedDateTime] = Is Null))

but I am not sure whether you can use form elemnts in the query...
0
 
HainKurtSr. System AnalystCommented:
this is yours:
Me.YourCheckBoxName = True

these are mine ;)
Me.chkMyCheckBox
Me.chkMyCheckBox.Value = True
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
lol. Right. Functionally exactly the same.  .Value is unnecessary since it's the Default property and is implied.

mx
0
 
Doi7722Author Commented:
mx
What I am trying to do is set the selection criteria on query field VerifiedDateTime to IS NULL when UnverifiedDeals Form is not ticked.
The SQL would then read.

SELECT [Share Register].ID, [Share Register].TransactionDate, [Share Register].Consideration, [Share Register].VerifiedDateTime, [Share Register].CompanyID, [Share Register].InvestorID
FROM [Share Register]
WHERE ((([Share Register].VerifiedDateTime) Is Null) AND (([Share Register].CompanyID)=[Forms]![Deal Recall]![CompanyID]) AND (([Share Register].InvestorID)=[Forms]![Deal Recall]![InvestorID]));
 
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
ok ... then maybe this for the WHERE clause:

WHERE ((([Share Register].VerifiedDateTime)=IIf([Forms]![Deal Recall]![UnverifiedDeals] =False, Is Null, [Share Register].[VerifiedDateTime]) AND (([Share Register].CompanyID)=[Forms]![Deal Recall]![CompanyID]) AND (([Share Register].InvestorID)=[Forms]![Deal Recall]![InvestorID]));
0
 
Doi7722Author Commented:
mx
it doesn't like the syntax on the Is Null statement. It says a missing operator.
doi
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sorry ... try this:

WHERE ((([Share Register].VerifiedDateTime)=IIf([Forms]![Deal Recall]![UnverifiedDeals] =False,  ([Share Register].[VerifiedDateTime]) Is Null, [Share Register].[VerifiedDateTime]) AND (([Share Register].CompanyID)=[Forms]![Deal Recall]![CompanyID]) AND (([Share Register].InvestorID)=[Forms]![Deal Recall]![InvestorID]));
0
 
HainKurtSr. System AnalystCommented:
can you please post your full code... I guess you cannot use form elemnt directly in your sql...

you should try something like this:

if [Forms]![Deal Recall]![UnverifiedDeals] then
  sql = "select ... from ... where (([Share Register].[VerifiedDateTime]) Is Null) and ..."
else
  sql = "select ... from ... where (([Share Register].[VerifiedDateTime]) Is not Null) and ..."
  ' or just ignore date part all together as
  ' sql = "select ... from ... where ..."
end if
0
 
Doi7722Author Commented:
Hainkurt
I really need to do this in sql.
0
 
Doi7722Author Commented:
I gave up in SQL and went back to VBA to set the SQL statements.

Private Sub UnverifiedDeals_Click()
Dim qdf As DAO.QueryDef
If Me.UnverifiedDeals = True Then
strSQL = "SELECT [Share Register].ID, [Share Register].TransactionDate, [Share Register].Consideration FROM [Share Register] WHERE ((([Share Register].VerifiedDateTime) Is Null) AND (([Share Register].CompanyID)=[Forms]![Deal Recall]![CompanyID]) AND (([Share Register].InvestorID)=[Forms]![Deal Recall]![InvestorID]));"
Else
strSQL = "SELECT [Share Register].ID, [Share Register].TransactionDate, [Share Register].Consideration FROM [Share Register] WHERE ((([Share Register].CompanyID)=[Forms]![Deal Recall]![CompanyID]) AND (([Share Register].InvestorID)=[Forms]![Deal Recall]![InvestorID]));"
End If
Set qdf = CurrentDb.QueryDefs("UnverifiedDealsQuery")
qdf.SQL = strSQL
Forms![Deal Recall].Refresh
End Sub
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.

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