A 2013 is null query

[Dick7Access]

I have a form (menu) that has different buttons (cmd’s) that will apply filters on the same form with the data. (about 8) All the buttons work find except the one that looks for missing cities. (Query address, has for criteria is null) works fine. Gives me all the records with missing addresses. City query has for criteria is null. Works fine from navigation panel, but when I execute it from a cmd on the form it gives me (0) zero records. I have made new queries and reduced the queries to minimum data, first name, last name, address, city, state. I have made temp button on another form, same results. I have searched for a coding error for over 3 hours. Any advice for what to look for???”

 

[jdraw]

Better to provide database or at least the code involved.

 

[Dick7Access]

Better to provide database or at least the code involved.

DB way to large to send, even zipped.

I can sent the code in the VBA for the cmd's

Do you thing if I send the SQL of the two queries, it would be of help to any one, trying to help, they are so long?

 

[Dick7Access]

Private Sub cmdtestCity_Click()
DoCmd.OpenForm "frmChurchesAllJen"
DoCmd.ApplyFilter "1aCity"
End Sub

Private Sub cmdtestADD_Click()
DoCmd.OpenForm "frmchurchesalljen"
DoCmd.ApplyFilter "1aadd"
End Sub

 

[JHB]

..
I can sent the code in the VBA for the cmd's

Do you thing if I send the SQL of the two queries, it would be of help to any one, trying to help, they are so long?

Show also the code from a button which work and also the queries from a working one and from which you've problem.

 

[CodeMonkey]

Sometimes it helps if you indicate Null AND "" criteria. Also, have you tried to assign macro with same function to the button?

Btw, i don't see any point in doing additional ApplyFilter here since OpenForm command has both Filter and WHERE condition as arguments like this:

Private Sub cmdtestADD_Click()
DoCmd.OpenForm "123", , "FILTER NAME", "[Form]![Cities] Is Null"
End Sub

 

[Dick7Access]

Sometimes it helps if you indicate Null AND "" criteria. Also, have you tried to assign macro with same function to the button?

Btw, i don't see any point in doing additional ApplyFilter here since OpenForm command has both Filter and WHERE condition as arguments like this:

Private Sub cmdtestADD_Click()
DoCmd.OpenForm "123", , "FILTER NAME", "[Form]![Cities] Is Null"
End Sub

[CODE]  Private Sub cmdtestCity_Click()
  DoCmd.OpenForm "frmChurchesAllJen", , "qryNeedCity", "[form]![city]is null"
  End Sub
  This code gives me a dialog box
  Enter Parameter Value  ?
  Form!Citiy

Original field is city
What did I do wrong?

 

[CodeMonkey]

Dick7Access,

I am not sure whether this is the point, but in my opinion Form is not a Recordset itself and does not contain any value in [Form]![City], it only reflects the data in the underlying table or query which is linked to the particular field. So, you either indicate [TABLENAME]![City] is null or just [City] is null.
Also, if the only function of the button is to find records with CITY = NULL then i see no point in applying "qryNeedCity" Filter, I would suggest to get rid of it to avoid confusion.
Try this code:

Private Sub cmdtestCity_Click()
  DoCmd.OpenForm "frmChurchesAllJen", , , "[city] is null"
  End Sub

 

[Dick7Access]

Dick7Access,

I am not sure whether this is the point, but in my opinion Form is not a Recordset itself and does not contain any value in [Form]![City], it only reflects the data in the underlying table or query which is linked to the particular field. So, you either indicate [TABLENAME]![City] is null or just [City] is null.
Also, if the only function of the button is to find records with CITY = NULL then i see no point in applying "qryNeedCity" Filter, I would suggest to get rid of it to avoid confusion.
Try this code:

Private Sub cmdtestCity_Click()
  DoCmd.OpenForm "frmChurchesAllJen", , , "[city] is null"
  End Sub

Thanks for trying to help
I tried the last code you gave me, but it still gives me a dialog box "Enter Parameter Value" city

 

[Dick7Access]

I know there is not much anybody can tell without seeing the whole DB, but this is driving me crazy. So if there is anything you can recommend for me to look for, it would be appreciated.

If this command and query works:

  Private Sub cmdNeedAdd_Click()
  DoCmd.OpenForm "frmChurchesAllJen"
  DoCmd.ApplyFilter "qryMissingAddr"
  End Sub

And this Command and query works:

  Private Sub cmdNeedState_Click()
  DoCmd.OpenForm "frmchurchesalljen"
  DoCmd.ApplyFilter "qryMissingstate"
  End Sub

And this one also works:

  Private Sub cmdTestZip_Click()
  DoCmd.OpenForm "frmchurchesAllJen"
  DoCmd.ApplyFilter "qryMissingZip"
  End Sub

Then with the same form or with a different form this little bugger doesn’t:

  Private Sub cmdNeedCity_Click()
  DoCmd.OpenForm "frmChurchesAllJen"
  DoCmd.ApplyFilter "qryNeedCity"
  End Sub

All quires are using "Is Null"

 

[JHB]

Show the SQL string for "qryNeedCity" and for one which work ex. "qryMissingZip"

 

[The_Doc_Man]

If something has a default value of an empty string, it is not null. I also am not sure that the syntax for "Is Null" is correct usage.

To me, you would have, say, an object that you passed into a subroutine (like, say, a control object). The declaration of the subroutine would allow you to say "OPTIONAL" on that control object. Then, to find out if the object was actually supplied, you would use the syntax of "If objControl Is Null" (and it would be null if the calling statement omitted it.)

To determine if a data field in a record is null, I might use syntax such as "If IsNull([fieldx])"

If the default value of the field is the empty string, I would instead use "If IsEmpty([fieldx])"

But then again, it has been a while since I played with either of these things.

 

[Dick7Access]

Show the SQL string for "qryNeedCity" and for one which work ex. "qryMissingZip"

In the process of putting together the SQL that JHB suggested that I sent, I had another though. I made a copy of [qryMissingAddr], which works find, and removed the (is null) in the address field, and put (is null) in the city field. Even from the Navigation panel, It brings up zero records. Would that suggest that something is wrong in the table?

  SELECT qryAlpha.txtAdd, qryAlpha.txtChurch, qryAlpha.txtCity, qryAlpha.txtNameF,
   qryAlpha.txtNameL, qryAlpha.YesNoSupportingCh, qryAlpha.bookmark, 
  qryAlpha.txtState, qryAlpha.county, qryAlpha.[2004], qryAlpha.trip04Dates,
   qryAlpha.trip04Day, qryAlpha.trip04Sort, qryAlpha.sortautorec, qryAlpha.sortc2,
   qryAlpha.sorte, qryAlpha.sortG, qryAlpha.Sortmeet, qryAlpha.sortSNonPastor,
   qryAlpha.sortpkg, qryAlpha.[sort r], qryAlpha.sorts, qryAlpha.sortT, qryAlpha.sort1,
   qryAlpha.sort2, qryAlpha.asstpastor, qryAlpha.[txtPh/Ch], qryAlpha.[texPh/Ho],
   qryAlpha.[txtPh/Other], qryAlpha.[txtPh/Cell], qryAlpha.txtZip, qryAlpha.Email,
   qryAlpha.quesindep, qryAlpha.by, qryAlpha.CHID, qryAlpha.hadmeet,
   qryAlpha.locationl, qryAlpha.datmeet, qryAlpha.[2ndsunsch], qryAlpha.sunsch,
   qryAlpha.Update, qryAlpha.Dir, qryAlpha.wor, qryAlpha.[2ndeve],
   qryAlpha.txtSunEve, qryAlpha.wed, qryAlpha.thur, qryAlpha.apt,
   qryAlpha.support, qryAlpha.quicksource, qryAlpha.schmeet, qryAlpha.txtBbfi,
   qryAlpha.txtSwbf, qryAlpha.txtSol, qryAlpha.txtChurchB, qryAlpha.txtOther,
   qryAlpha.tel, qryAlpha.ok, qryAlpha.fairid, qryAlpha.supamt, qryAlpha.note,
   qryAlpha.yellow, qryAlpha.kjv, qryAlpha.deput, qryAlpha.web, qryAlpha.txtwife,
   qryAlpha.fortel, qryAlpha.blamks, qryAlpha.acode, qryAlpha.phoneonly, 
  qryAlpha.dist, qryAlpha.new, qryAlpha.sortd, qryAlpha.rv, qryAlpha.hooksew,
   qryAlpha.hookpho, qryAlpha.ltpkg, qryAlpha.ltcom, qryAlpha.ltmeet,
   qryAlpha.ltrem, qryAlpha.lttku, qryAlpha.ltsup, qryAlpha.fcountry,
   qryAlpha.earlywor, qryAlpha.LtrSentCatagory, qryAlpha.LtrTrackingAGM,
   qryAlpha.LtrTrackingMBC, qryAlpha.sort3, qryAlpha.YesNoIndep, qryAlpha.txtPhoneExt
  FROM qryAlpha
  WHERE (((qryAlpha.txtAdd) Is Null));
1st SQL works
2nd SQL doesn't work

[CODE]
  SELECT qryAlpha.txtAdd, qryAlpha.txtChurch, qryAlpha.txtCity, qryAlpha.txtNameF,
   qryAlpha.txtNameL, qryAlpha.YesNoSupportingCh, qryAlpha.bookmark,
   qryAlpha.txtState, qryAlpha.county, qryAlpha.[2004], qryAlpha.trip04Dates,
   qryAlpha.trip04Day, qryAlpha.trip04Sort, qryAlpha.sortautorec, qryAlpha.sortc2,
   qryAlpha.sorte, qryAlpha.sortG, qryAlpha.Sortmeet, qryAlpha.sortSNonPastor,
   qryAlpha.sortpkg, qryAlpha.[sort r], qryAlpha.sorts, qryAlpha.sortT, qryAlpha.sort1,
   qryAlpha.sort2, qryAlpha.asstpastor, qryAlpha.[txtPh/Ch], qryAlpha.[texPh/Ho],
   qryAlpha.[txtPh/Other], qryAlpha.[txtPh/Cell], qryAlpha.txtZip, qryAlpha.Email,
   qryAlpha.quesindep, qryAlpha.by, qryAlpha.CHID, qryAlpha.hadmeet, qryAlpha.locationl,
   qryAlpha.datmeet, qryAlpha.[2ndsunsch], qryAlpha.sunsch, qryAlpha.Update,
   qryAlpha.Dir, qryAlpha.wor, qryAlpha.[2ndeve], qryAlpha.txtSunEve, qryAlpha.wed,
   qryAlpha.thur, qryAlpha.apt, qryAlpha.support, qryAlpha.quicksource,
   qryAlpha.schmeet, qryAlpha.txtBbfi, qryAlpha.txtSwbf, qryAlpha.txtSol,
   qryAlpha.txtChurchB, qryAlpha.txtOther, qryAlpha.tel, qryAlpha.ok,
   qryAlpha.fairid, qryAlpha.supamt, qryAlpha.note, qryAlpha.yellow, 
  qryAlpha.kjv, qryAlpha.deput, qryAlpha.web, qryAlpha.txtwife,
   qryAlpha.fortel, qryAlpha.blamks, qryAlpha.acode, qryAlpha.phoneonly,
   qryAlpha.dist, qryAlpha.new, qryAlpha.sortd, qryAlpha.rv, qryAlpha.hooksew,
   qryAlpha.hookpho, qryAlpha.ltpkg, qryAlpha.ltcom, qryAlpha.ltmeet, 
  qryAlpha.ltrem, qryAlpha.lttku, qryAlpha.ltsup, qryAlpha.fcountry, 
  qryAlpha.earlywor, qryAlpha.LtrSentCatagory, qryAlpha.LtrTrackingAGM,
   qryAlpha.LtrTrackingMBC, qryAlpha.sort3, qryAlpha.YesNoIndep, qryAlpha.txtPhoneExt
  FROM qryAlpha
  WHERE (((qryAlpha.txtCity) Is Null));

[/CODE]

 

[Dick7Access]

I ‘ve lost about 35 pounds, and I now drool around the corners of my mouth, but here is the answer. The source for form (frmChurchesAllJen) is a query called, (qryAlpha) it of course alphabetize the record set. However a long time ago I got tired of looking at the records with no cities, so I put a criteria under City (is not null) so the record set had no records with no cities. Thanks for all those that tried to help.