Print Form function

[fkotulak]

Trying to Print a record on a Form. It works for all records that have something in all three fields, but fails when one of the fields contains nothing. Is there someway without putting Null checks everywhere to have this query work with the existing displayed record on a form. Or is there a better way to do this in code!

sample SQL query
SELECT DIRECT.*
FROM DIRECT
WHERE (((Direct.[Last Name])=[Forms]![Directbus].[Last Name])) and (((Direct.[First Name])=[Forms]![Directbus].[First Name])) and (((Direct.[Business Name])=[Forms]![Directbus].[Business Name]))
ORDER BY DIRECT.[Last Name], DIRECT.[First Name];

DIRECT is the database, Directbus is the current active form being displayed. Any one of these three fields could be blank.

 

[isladogs]

Try using Nz to enclose each item in the WHERE clause

Your bracketing was incorrect - surprised it worked at all - hopefully its correct now but if not, my excuse is I'm typing on a tablet

BTW - as its all in one table , you could remove all 'DIRECT.' entries

SELECT DIRECT.*
FROM DIRECT
WHERE (((Direct.[Last Name])=Nz([Forms]![Directbus].[Last Name],'')) AND ((Direct.[First Name])=Nz([Forms]![Directbus].[First Name],'')) AND ((Direct.[Business Name])=Nz([Forms]![Directbus].[Business Name],'')))
ORDER BY DIRECT.[Last Name], DIRECT.[First Name];

 

[fkotulak]

Thanks for the reply but did not work. The field is "unused" so I don't know if that makes it null or empty or whatever. I used your code as provided (did not know about the NZ command thanks) regardless the new set value from that command does not match the field and it doesn't work?? Your SQL did work when the fields all contained something. Tried isNull with an 'OR' on each field and couldn't get that to work either. Gave up! Decided to use Record Number instead. Which had I thought about is much simpler, more efficient and probably more correct anyway.

 

[isladogs]

Glad you got it working
I did wonder whether the ORDER BY clause would cause issues if either name was blank & had considered adding Nz there as well
Anyway you have a solution ...

 

[Pat Hartman]

Assuming you want to treat the empty field as optional, try this:

SELECT DIRECT.*
FROM DIRECT
WHERE ((Direct.[Last Name]=[Forms]![Directbus].[Last Name] OR [Forms]![Directbus].[Last Name] Is Null)) and ((Direct.[First Name]=[Forms]![Directbus]![First Name] OR [Forms]![Directbus]![First Name] Is Null)) and ((Direct.[Business Name]=[Forms]![Directbus]![Business Name] OR [Forms]![Directbus]![Business Name] Is Null))
ORDER BY DIRECT.[Last Name], DIRECT.[First Name];