Show All Query (1 Viewer)

[andy_dyer]

This sounds so simple but I cannot get it to work

Combo box has three options

1 Yes
2 No
3 All

Table field has two options

1 Yes
2 No

In my search form the query on this field

Like [Forms]![frmProjectSearch]![cboProjectType]

Works if I choose Yes or No but nothing if I choose All (so option 3)

What do I need to do to this query to fix this?

Many thanks

 

[Trevor G]

Try changing the code to include & "*"

Like [Forms]![frmProjectSearch]![cboProjectType]

becomes

Like [Forms]![frmProjectSearch]![cboProjectType]&"*"

 

[andy_dyer]

Try changing the code to include & "*"

Like [Forms]![frmProjectSearch]![cboProjectType]

becomes

Like [Forms]![frmProjectSearch]![cboProjectType]&"*"

Hi Trevor - I've made that change but still no records show... :-(

 

[Trevor G]

Andy can you attach a copy of the database or example to your thread.

 

[andy_dyer]

Thanks I'll just try and sort that out so I can post something

This is the query in SQL if it helps at all? Looks jibberish to me in SQL...

SELECT tblProject.ProjectID, tblProject.ProjectName AS [Project Name], tblProject.[Funded Project?], tblProjectCountry.ProjectCountry AS [Project Country], tblProjectStatus.Status AS [Project Status], tblSVPArea.SVPArea AS [SVP Area], tblVPArea.VPArea AS [VP Area], Nz([tblProject].[StatusID],"") & ";" AS StatusIDSearch, Nz([tblProject].[ProjectCountryID],"") & ";" AS CountryIDSearch, Nz([tblProject].[SVPAreaID],"") & ";" AS SVPAreaIDSearch, IIf([Forms]![frmProjectSearch]![txtVPSearch]<>"",InStr(1,[Forms]![frmProjectSearch]![txtVPSearch],Nz([tblProject].[VPAreaID],"") & ";"),1) AS VPAreaIDSearch
FROM (((tblProject LEFT JOIN tblProjectCountry ON tblProject.ProjectCountryID = tblProjectCountry.ProjectCountryID) LEFT JOIN tblProjectStatus ON tblProject.StatusID = tblProjectStatus.StatusID) LEFT JOIN tblSVPArea ON tblProject.SVPAreaID = tblSVPArea.SVPAreaID) LEFT JOIN tblVPArea ON tblProject.VPAreaID = tblVPArea.VPAreaID
WHERE (((tblProject.ProjectName) Like [Forms]![frmProjectSearch]![txtProjectNameSearch]) AND ((tblProject.[Funded Project?]) Like [Forms]![frmProjectSearch]![cboProjectType] & "*") AND ((Nz([tblProject].[StatusID],"") & ";") Like [Forms]![frmProjectSearch]![txtStatusSearch]) AND ((Nz([tblProject].[ProjectCountryID],"") & ";") Like [Forms]![frmProjectSearch]![txtCountrySearch]) AND ((Nz([tblProject].[SVPAreaID],"") & ";") Like [Forms]![frmProjectSearch]![txtSVPSearch]) AND ((IIf([Forms]![frmProjectSearch]![txtVPSearch]<>"",InStr(1,[Forms]![frmProjectSearch]![txtVPSearch],Nz([tblProject].[VPAreaID],"") & ";"),1))>0)) OR ((([Forms]![frmProjectSearch]![cboProjectType]) Is Null) AND ((([tblProject].[Funded Project?]) Like [Forms]![frmProjectSearch]![cboProjectType]) Is Null))
ORDER BY tblProject.ProjectName;

 

[andy_dyer]

Ok here is the search form as it currently works...

Thanks for your time looking and helping

 

[Trevor G]

Andy I have downloaded your database but your Project Table only has 1 and 2 nothing else assuming that this is the issue as there is not a 3 in the table. Also your Yes No Table only has 1 and 2 not the 3.

 

[andy_dyer]

Andy I have downloaded your database but your Project Table only has 1 and 2 nothing else assuming that this is the issue as there is not a 3 in the table. Also your Yes No Table only has 1 and 2 not the 3.

Hi - I'm a little confused

tblProjectFundingType has

ProjectTypeID ProjectType
1 Funded Projects Only
2 Non-Funded Projects Only
3 All Projects

But yes my tblYesNo table

YesNoID YesNo
1 Yes
2 No

So currently if a project has funding someone selects Yes from tblYesNo which gives an ID of 1 which aligns with Funded Projects in tblProjectFundingType and also the same for No and Non-Funded Projects...

On the search form if you choose funded projects you get Bob1 shown in the results section and if you choose Non-Funded you get Bob2 I can't get Bob1 and Bob2 to show when you choose All Projects...

 

[andy_dyer]

Hi everyone - this is still doing ym head in as I'm so sure it's something basic but I can't figure it out...

I've tried everything I can think of - how can I get all entries displayed (so both ID's 1 & 2) when I choose "All Projects"

Thanks for your help

 

[MSAccessRookie]

Just a guess, but since you appear to have two entries (Numeric Code and Text Description) for each item, if you changed the Numeric Field to Text, and changed the last entry from a number to "*", you might be able to use a LIKE Comparison to view all of the entries.

ProjectTypeID ProjectType
1 Funded Projects Only
2 Non-Funded Projects Only
* All Projects

 

[andy_dyer]

Thanks that set me on the right track at least and now I've fixed it