Help with form based parameter query/null fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Happy New Year
I'm setting up a form based parameter query that uses drop down boxes to
search different fields. One of the parameters I want to search for is null
fields (not zero length strings since the field in the table was set up not
to accept those).
Can someone tell me what expression to use in the criteria field of the
query to retrieve null values. Since the query receives the parameter from
the form I know it's something like
forms!formname!controlname.....(but then what?)
Thanks for any help
 
Steve

How is the entry in the combobox on the form supposed to relate to the
criteria of the query? Do you mean that you want the query to select
the records where the field is Null if the combobox is left empty, i.e.
no selection? Or do you have an item in the combobox's list for "null"
or some such, so the query selects on the basis of this? Maybe it would
help if you could give an example.
 
Thanks. I'm basically trying to do whats in this help column so that the
query obtains its parameters from the controls on the form....

http://office.microsoft.com/en-us/assistance/HA011170771033.aspx

The only difference is that I would like the combo box to give me an option
of selecting records where the specified field is null. I don't have is null
in my combo list because it didn't work.. I left it as a blank, and also
tried "" which also doesn't work. I'm new at this and used the combo box
wizard on the form which created a value list of drop down parameters to
choose from. For example...If the form drop down includes apples, oranges
and pears and I select pears..it retrieves the correct records. I can't
figure out what I need to include in the value list which would be the
equivalent of "is null". Typing that doesn't work.
 
Steve,

Ok, thanks for the further explanation. If you add an item to the
combobox's value list, let's say it just says "none", then you can use
this in the query criteria...
[Forms]![formname]![ComboName] Or Is Null And
[Forms]![formname]![ComboName]="none"
(watch for wordwrap in the newsreader... the above is all meant to be on
one line!)
 
Steve,

I apologise for not explaining. Maybe if I'd put parentheses in, it may
have been easier to read, like this...
[Forms]![formname]![ComboName] Or (Is Null And
[Forms]![formname]![ComboName]="none")
.... or, to translate into English:
Select the records where the value in the field is the same as the
value in the combobox, or where the field is Null and the value in the
combobox is "none".
 
Now I Got it. Thanks!

Steve Schapel said:
Steve,

I apologise for not explaining. Maybe if I'd put parentheses in, it may
have been easier to read, like this...
[Forms]![formname]![ComboName] Or (Is Null And
[Forms]![formname]![ComboName]="none")
.... or, to translate into English:
Select the records where the value in the field is the same as the
value in the combobox, or where the field is Null and the value in the
combobox is "none".

--
Steve Schapel, Microsoft Access MVP
Steve, thanks a lot. It works great!! I'm still figuring out what's going
on there but really appreciate your time.
Click to expand...
Click to expand...
 
hello steve in tn,
i am shivalee and i am also doing part of what you have done. can you please..please send me the code on which u r working.
i want the code- when you have a combo-box and have values inside it coming from a table, and when the user selects a value, he is shown some selected rows from a table or 2 tables or from a query based on the value selected from the combo-box.
thanks a lot,
shivalee
 
Back
Top