• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 394
  • Last Modified:

Is Null

Experts,

I have a report with a form to query the records.
I thought that my criteria would include records that have a null for [CurrencyName] but if the [CurrencyName] is null in the tblCurrencyExchange then it will exclude those records.  If I select a [CurrencyName] from the cbo then it will include them.  

I have this on [CurrrencyName] Like "*" & Nz([forms]![frmLCIssuedQryType]![cboCurrency],"") & "*"
if I remove the Like statement then the ones with Null for CurrencyName will appear.  

do you see anything wrong here:
thank you

SELECT tblLetterOfCredit.DateOfIssueSB, tblLCTypeDropbox.LCType, tblCurrencyExchange.CurrencyName, tblEndUser.[End User], tblCurrencyExchange.Currency, tblLetterOfCredit.BankGTIssueDate, tblLetterOfCredit.Amount, Projects.ID, tblLetterOfCredit.LetterOfCreditID, tblCountry.Country, tblCurrencyExchange.CurrencyID, tblLetterOfCredit.EndUserID, tblCurrencyExchange.ExchangeRate, Projects.contractingEntity, tblLetterOfCredit.AdvisingBank, tblLetterOfCredit.LocalBankName, tblLetterOfCredit.lcno, Projects.[project name], tblBanks.BankName, Projects.Status2, IIf(IsNull([tblLetterOfCredit].[DateOfIssueSB]),"Not Issued (No Issue Date Entered)","Issued LC's") AS MyGroup FROM tblCurrencyExchange RIGHT JOIN (Projects RIGHT JOIN ((((tblLetterOfCredit LEFT JOIN tblCountry ON tblLetterOfCredit.Country = tblCountry.CntryID) LEFT JOIN tblBanks ON tblLetterOfCredit.BankID = tblBanks.BankID) LEFT JOIN tblEndUser ON tblLetterOfCredit.EndUserID = tblEndUser.EndUserID) LEFT JOIN tblLCTypeDropbox ON tblLetterOfCredit.LCType = tblLCTypeDropbox.ID) ON Projects.ID = tblLetterOfCredit.ProjectID)

ON tblCurrencyExchange.CurrencyID = tblLetterOfCredit.Currency WHERE (((tblLCTypeDropbox.LCType) Like "*" & Nz([forms]![frmLCIssuedQryType]![txtLCtype],"") & "*")

AND ((tblCurrencyExchange.CurrencyName) Like "*" & Nz([forms]![frmLCIssuedQryType]![cboCurrency],"") & "*") AND ((tblEndUser.[End User]) Like "*" & Nz([forms]![frmLCIssuedQryType]![txtEndUser],"") & "*"));
0
pdvsa
Asked:
pdvsa
  • 2
  • 2
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Here is the general idea:

SELECT Table1.*
FROM Table1
WHERE (((Table1.CurrrencyName)=[Forms]![Form1]![combo32])) OR (((IsNull([CurrrencyName]))=True));

mx
0
 
mmr159Commented:
> do you see anything wrong here

Your query is very difficult to interpret.  So much so that I went and cleaned it up a little.


SELECT loc.DateOfIssueSB, 
	lct.LCType, 
	ce.CurrencyName, 
	eu.[End User], 
	ce.Currency, 
	loc.BankGTIssueDate, 
	loc.Amount, 
	proj.ID, 
	loc.LetterOfCreditID, 
	c.Country, 
	ce.CurrencyID, 
	loc.EndUserID, 
	ce.ExchangeRate, 
	proj.contractingEntity, 
	loc.AdvisingBank, 
	loc.LocalBankName, 
	loc.lcno, 
	proj.[project name], 
	b.BankName, 
	proj.Status2, 
	IIf(
		IsNull(loc.DateOfIssueSB),
		"Not Issued (No Issue Date Entered)",
		"Issued LC's"
		) AS MyGroup
FROM tblCurrencyExchange AS ce RIGHT JOIN (
	Projects AS proj RIGHT JOIN (
	(((
	tblLetterOfCredit AS loc LEFT JOIN tblCountry AS c
		ON loc.Country = c.CntryID
	) LEFT JOIN tblBanks AS b
		ON loc.BankID = b.BankID
	) LEFT JOIN tblEndUser AS eu
		ON loc.EndUserID = eu.EndUserID
	) LEFT JOIN tblLCTypeDropbox AS lct
		ON loc.LCType = lct.ID
	) ON proj.ID = loc.ProjectID
	) ON ce.CurrencyID = loc.Currency
WHERE (((lct.LCType) Like "*" & Nz([forms]![frmLCIssuedQryType]![txtLCtype],"") & "*")
	AND ((ce.CurrencyName) Like "*" & Nz([forms]![frmLCIssuedQryType]![cboCurrency],"") & "*")
	AND ((eu.[End User]) Like "*" & Nz([forms]![frmLCIssuedQryType]![txtEndUser],"") & "*"));

Open in new window

0
 
pdvsaAuthor Commented:
I had to change [CurrencyName] to ((IsNull([forms]![frmLCIssuedQryType]![cboCurrency]))=True));

because if I left it as
WHERE (((Table1.CurrrencyName)=[Forms]![Form1]![combo32])) OR (((IsNull([CurrrencyName]))=True));
A msgbox appeared asking for [CurrencyName]

It seems to work but I am a little confused because I woudl think that I woudl need the [CurrencyName] with criteria as Null and not on cboCurrency.

What do you think?
0
 
pdvsaAuthor Commented:
Thanks...
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
You are welcome ...

mx
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

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