Query- Assuming wildcard if control is null (1 Viewer)

[p595659]

Hi,
I'm making a query which is used to generate a report. Anyways, I've ran into a problem. The query is based on some controls in a form, but what I would like to do is that if a control was null then a field in the query should be assumed to be a wildcard for the criteria, otherwise the data in the control should be used in the criteria of the query. Does anyone have any ideas if this could be done?

Cheers,
Ben

 

[KenHigg]

Could you do something like the following in the query:

MyFld: nz([FldX],"*")

???

 

[p595659]

Hi Ken,
Sorry didn't quite understand.

In my query I have 2 fields, provider and plan. So for my report, if I only chose an advisor then the query would display all the records that for the chosen advisor. Or, if I chose an advisor and a plan then the query would show all records that an advisor has sold for the chosen plan. Or, Maybe I would just select a plan and the query would generate the list of records of the chosen plan.

Hope this clarifies what I'm after.

Cheers,
Ben

 

[KenHigg]

Sorry, How are telling the report what the criteria is? Is the query simply prompting you for the inputs or are you getting the criteria from a form?

 

[p595659]

I'm going to be getting the criteria from the form. It would be [Forms]![frmReportSelect]![cboAdvisor] and [Forms]![frmReportSelect]![cboPlan] where I am getting the criteria from.

Cheers,
Ben

 

[KenHigg]

I would think the easiest thing to do would be to simply put a wild card in the forms text box for the appropriate field...?

 

[p595659]

but the 2 controls where I get the criteria from are 2 combo boxes.

 

[KenHigg]

Ok... Hum. So in your criteria in the query maybe:

nz(forms!MyComboBoxName,"*")

(I don't remember trying this so I maybe chasing a wild rabbit here...)

 

[p595659]

I tried the nz function but when I run the query all the advisors are getting listed, not just one of them. Here's my code:

Like (nz([forms]![frm_ReportSelect]![Advisor_ID],"*"))

Any ideas?

Cheers,
Ben

 

[KenHigg]

Hum...

Like Val(nz([forms]![frm_ReportSelect]![Advisor_ID],"*"))

???

 

[p595659]

Now there are no results when the query is run.

I would send you the actual database Ken, but the problem is that the data stored is for actual customers so would have a problem with data protection.

Ben

 

[YoungNastyMan]

put

[Table Data is coming from]=[Forms]![Your Form Name]![Combo] Or [Forms]![Your Form Name]![Combo] Is Null

in the field box in your query design window of your search fields then true in the criteria for each of the fields you want to search and it will work

 

[p595659]

Alright, after some modification of actual data I'm allowing people to have a look at it to try and work out the query solution. The query in question is qryPipelineAndCommission.

Apologies for the file size, but it was alot bigger. It was alot larger with 5K records. Currently the file is 6.3 megs

http://www.yousendit.com/transfer.php?action=download&ufid=D17F27F43635890B

Ben

 

[p595659]

Ops, forgot to include qryPipelineAndCommission in the file. Here is the SQL view of the query.

SELECT Trim([tblEmployee.Employee_First_Name] & " " & [tblEmployee.Employee_Surname]) AS Advisor, tblNewBusiness.NewBusiness_Date_Issued AS [Date Issued], Trim([Client_First_Name] & " " & [Client_Surname]) AS Client, tblProviders.Provider_Company AS Company, tblNewBusiness.NewBusiness_Policy_Number AS [Policy Number], tblNewBusiness.NewBusiness_Sum_Assured AS [Sum Assured], tblNewBusiness.NewBusiness_Benefit AS Benefit, tblNewBusiness.NewBusiness_Premium AS Premium, tblNewBusiness.NewBusiness_Brokerage AS Brokerage, tblNewBusiness.NewBusiness_Comments AS Comments, [Employee_Ref]=[Forms]![frm_ReportSelect]![Advisor_ID] Or [Forms]![frm_ReportSelect]![Advisor_ID] Is Null AS Expr1
FROM tblEmployee AS tblEmployee_1, tblProviders INNER JOIN (((tblPlanTypes INNER JOIN ((tblClients INNER JOIN tblNewBusiness ON tblClients.Client_Ref = tblNewBusiness.Client1_Ref) INNER JOIN (tblEmployee INNER JOIN tblAdviserSplit ON tblEmployee.Employee_Ref = tblAdviserSplit.Adviser_Ref) ON tblNewBusiness.NewBusiness_Ref = tblAdviserSplit.NewBusiness_Ref) ON tblPlanTypes.PlanTypes_Ref = tblNewBusiness.PlanType_Ref) INNER JOIN tblCancelled ON tblNewBusiness.NewBusiness_Ref = tblCancelled.NewBusiness_Ref) INNER JOIN (tblIntroducers INNER JOIN tblIntroducer ON tblIntroducers.Introducer_Ref = tblIntroducer.Introducers_Ref) ON tblNewBusiness.NewBusiness_Ref = tblIntroducer.NewBusiness_Ref) ON tblProviders.Provider_Ref = tblNewBusiness.Provider_Ref
WHERE ((([Employee_Ref]=[Forms]![frm_ReportSelect]![Advisor_ID] Or [Forms]![frm_ReportSelect]![Advisor_ID] Is Null)=True));

Anyways, I thought I better include a better explanation of the problem now. The form I'm getting the input from is frm_ReportSelect, and for now I would like a list of clients an advisor has dealt with to be exported to Excel. If you click on Reports, then select PipelineAndCommission and click on view report then another form comes up (frm_ReportSelect). I'm trying to setup the exporting of the query results into excel so now I just want to get the "Export Excel" button to work.

 

[stepdown]

Posted by SportsGuy Here

Like [Forms]![Search].[Author] & "*"
returns all records that begin with [Forms]![Search].[Author]
AND all records if [Author] Is Null

Like "*" & [Forms]![Search].[Author] & "*"
returns all records that contain [Forms]![Search].[Author]
AND all records if [Author] Is Null

Like "*" & [Forms]![Search].[Author]
returns all records that end with [Forms]![Search].[Author]
AND all records if [Author] Is Null

sportsguy

This is what I used to get a similar result. I'm just worried about if I had one entry of "Bryan Smith" and had another entry of "Bryan Smithson" then I think it would return both, right?

Personally I think that it wouldn't affect my database as the field I am searching on should contain very different entries, but it does seem like a weakness to me.

Thanks