3 combobox values as query parameter 1

[BakerUSMC]

Hello to all,

I have a form that contains 3 combo boxes. I need the value of the combo boxes be passed to a query that another form is based off of.

1st field passes a -1 or 0 to the query for a yes/no field

2nd field passes text for the country selected

3rd field is bases off a joined table in the query and will see if any ID exists or not... so I need it to check for null or not null.

I don't know how either thru VBA or the query to tell it that I want null records or not. I can't pass "Is Null" from a field on the form thru the criteria in the query.

For exaple:

OrphanStatus: SponsoredStatus Country
(Value to pass) (Value to pass) (Value to pass)
from the Active? field from the sponsoring.sponsorship_id field from the country field
--------------------------------------------------------

-1 is not null sierra leone

**Should return 1 record

-1 is null sierra leone

**Should return 72 records

Here is the sql that I have now:

SELECT orphans.[Active?], sponsoring.[Sponsorship ID], orphans.Country, orphans.[Orphan ID], orphans.Name, orphans.Sex, orphans.Age, orphans.[Date of Birth], orphans.Location, orphans.Photo, orphans.[Date Added], orphans.[Date Updated], sponsoring.[Sponsor ID], sponsoring.[Term of Sponsorship], sponsoring.[Start of Sponsorship], sponsoring.[End fo Sponsorship], sponsoring.[Payment Type], sponsoring.[Payment Schedule], sponsoring.[Total Number of Payments Due], sponsoring.[Number of Payments Made], sponsoring.[Total Amount Due], sponsoring.[Total Amount Paid], sponsoring.[Total Overdue Payments], sponsoring.[Record Active?], sponsoring.[Date Last Updated]
FROM orphans LEFT JOIN sponsoring ON orphans.[Orphan ID] = sponsoring.[Orphan ID]
WHERE (((orphans.[Active?])=[forms]![frm_ViewOrphans]![txtOrphanStatus]) AND ((orphans.Country)=[forms]![frm_ViewOrphans]![txtCountry]));

Please help me with this...

 

[PHV]

Something like this ?
...
WHERE orphans.[Active?]=[Forms]![frm_ViewOrphans]![txtOrphanStatus] AND orphans.Country=[Forms]![frm_ViewOrphans]![txtCountry]
AND ((sponsoring.[Sponsorship ID] Is Null AND [Forms]![frm_ViewOrphans]![txtSponsoredStatus]='is null')
OR (sponsoring.[Sponsorship ID] Is Not ull AND [Forms]![frm_ViewOrphans]![txtSponsoredStatus]='is not null'))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BakerUSMC]

PHV,

You know, I was hoping you would answer my posting... because you have always been consistant with your correct responses to my problems... Thank you so much... My headache is now gone...

You always deserve a star from me...

THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[PHV]

Reading your previous threads, another way:
...
WHERE orphans.[Active?]=[Forms]![frm_ViewOrphans]![txtOrphanStatus] AND orphans.Country=[Forms]![frm_ViewOrphans]![txtCountry]
AND IsNull(sponsoring.[Sponsorship ID])=([Forms]![frm_ViewOrphans]![cboSponsoredStatus]='Unsponsored')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[BakerUSMC]

Hello to all again....

Using the sql that PHV and post for me has been working great but I need to add an option to select all for each criteria. How would I add this to the sql?

SELECT [orphans].[Active?], [sponsoring].[Sponsorship ID], [orphans].[Country], [orphans].[Orphan ID], [orphans].[Name], [orphans].[Sex], [orphans].[Age], [orphans].[Date of Birth], [orphans].[Location], [orphans].[Photo], [orphans].[Date Added], [orphans].[Date Updated], [sponsoring].[Sponsor ID], [sponsoring].[Term of Sponsorship], [sponsoring].[Start of Sponsorship], [sponsoring].[End fo Sponsorship], [sponsoring].[Payment Type], [sponsoring].[Payment Schedule], [sponsoring].[Total Number of Payments Due], [sponsoring].[Number of Payments Made], [sponsoring].[Total Amount Due], [sponsoring].[Total Amount Paid], [sponsoring].[Total Overdue Payments], [sponsoring].[Record Active?], [sponsoring].[Date Last Updated]
FROM orphans LEFT JOIN sponsoring ON [orphans].[Orphan ID]=[sponsoring].[Orphan ID]
WHERE [orphans].[Active?]=[Forms]![frm_ViewOrphans]![txtOrphanStatus] And [orphans].[Country]=[Forms]![frm_ViewOrphans]![txtCountry] And (([sponsoring].[Sponsorship ID] Is Null And [Forms]![frm_ViewOrphans]![txtSponsoredStatus]='is null') Or ([sponsoring].[Sponsorship ID] Is Not Null And [Forms]![frm_ViewOrphans]![txtSponsoredStatus]='is not null'));

Thanks so much!!