VBA radio button coding

Hi Experts,

I currently have a form which has 2 option groups both containing sets of radio buttons. The form also contains a button which loads a report from its onlclick event. The report has a query behind it which links various link tables and performs basic filtering.

Option Group 1 contains 2 radio buttons, All Students & Second years only. Obv if the user selects all students then no filtering is necessary but if they select second years only then **/2/* needs to be applied to the 'course code' value in the query.

Options Group 2 contains 4 options, National Award, National Certificate, National Diplioma & AS/A2. The query contains a field that uses the midpoint function to pull (for example) ND for a national awards code NC for national certificate etc

Is it easier to filter using VBA code behind the oncllick event of the button that launches the report or actually in the where clause of the query itself. Bearing in mind that the query already has the following where clause already,,,,, Like "9ND*/1/*" Or Like "8ND*/2/*" Or Like "9NC*" Or Like "9NA*"

Thanks,
Dean.
deanlee17Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

deanlee17Author Commented:
Ok ive made some progress, im using a case select statement like so:

Private Sub Command21_Click()
Select Case Me.Year_select_options

    Case 1
          DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          DoCmd.OpenReport "Predicted_VA", acViewPreview ,,where [E_REFERENCE] like **/2/*

 End Select
End Sub

Case 2 is throwing up a problem though. E_REFERENCE is a field in the query how would i code the Like **/2/*?
0
deanlee17Author Commented:
Ok i now have 2 case select statements behind the 1 button, however it only reads the first case select, is there a way around this?
Private Sub Command21_Click()
Select Case Me.Year_select_options

    Case 1
          DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "E_REFERENCE like '*/2/*'", acWindowNormal

 End Select
 
 Select Case Me.Award_option_group

    Case 1
          DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'ND'", acWindowNormal
    Case 3
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'NC'", acWindowNormal
    Case 4
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'NA'", acWindowNormal
    Case 5
          DoCmd.OpenReport "Predicted_VA", acViewPreview, , "test like 'AS' OR 'A2'", acWindowNormal

 End Select
 
End Sub

Open in new window

0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You'd need to build up your Where clause for the OpenReport method.

Note in the code below I've used the OR keyword when adding filtering criteria. Depending on your needs, you may need to change that to AND.


Private Sub Command21_Click()

Dim sWhere As String


Select Case Me.Year_select_options
    Case 1
          '/DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
          sWhere = "E_REFERENCE like '*/2/*'"

 End Select
 
 Select Case Me.Award_option_group

    Case 1
          '/DoCmd.OpenReport "Predicted_VA", acViewPreview
    Case 2
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & " test like 'ND'"
    Case 3
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & "test like 'NC'"
    Case 4
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & "test like 'NA'"
    Case 5
	If Len(sWhere) > 0 Then sWhere = sWhere & " OR "
	sWhere = sWhere & "test like 'AS' OR 'A2'"

 End Select

 DoCmd.OpenReport "Predicted_VA", acViewPreview, , sWhere, acWindowNormal
 
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

deanlee17Author Commented:
Ah ok, i see how this is building the Where clause. Did you mean to comment out Case 1 for the first select statement? What if they with to choose all students (case 1) then a selection from the second case select?

You were correct that i need to change the OR for AND to build the correct Where clause:

sWhere = "E_REFERENCE like '*/2/*'" " AND " sWhere = sWhere & " test like 'ND'"
 
0
deanlee17Author Commented:
Oh, with case 1 the way it currently stands does it just carry nothing down to the second select statement? so you just end up with all students and the course selected from the second case select?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
<Did you mean to comment out Case 1 for the first select statement? >

There is no need to provide anything for a "non-filter"; I just left the Case 1 stuff in there for reference.

<so you just end up with all students and the course selected from the second case select?>

Yes.
0
deanlee17Author Commented:
If i select all students and all awards it runs fine. If i select all students and ND for the award then it asks for a Parameter value for test. However if I run the query alone it returns values for test, such as ND, NC etc?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
There's an extra space in my code:

   Case 2
        If Len(sWhere) > 0 Then sWhere = sWhere & " OR " 
        sWhere = sWhere & "test like 'ND'"  <<<<< note this line

Also, it's a good idea to enclose your Field values in square brackets:

   Case 2
        If Len(sWhere) > 0 Then sWhere = sWhere & " OR " 
        sWhere = sWhere & "[test] like 'ND'"

 
0
deanlee17Author Commented:
Where abouts is the extra space? I tried altering it but get the same problem.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
The extra space was in front of the word "test"

If your OpenReport method is prompting you for the value of "test", then this means it cannot find that value (i.e. Field) in the query used for your Report. Can you post the SQL of that query here?

IN case you're not familiar with that, open the Query in Design view, then click View - SQL. Copy/paste that back here.
0
deanlee17Author Commented:
Here it is mate....

SELECT UNITE_CAPD_MODULEENROLMENT_CRS.E_PROJGRADE AS [Projected Grade], UNITE_CAPD_PERSON.P_SURNAME AS Surname, UNITE_CAPD_PERSON.P_FORENAMES AS Forenames, UNITE_CAPD_STUDENT.S_STUDENTREFERENCE AS PSN, Mid(UNITE_CAPD_MODULEENROLMENT.E_REFERENCE,2,4) AS [Course Factor], UNITE_CAPD_ENROLMENTISR.EI_Q21 AS [Actual Grade], [QCA Score table].[Avg QCA Score], Round(([Avg QCA Score]-10)/6,1) AS [AVG GCSE], UNITE_CAPD_MODULEENROLMENT.E_STATUS, IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="1","L",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="2","C",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="3","W",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="4","T",IIf(UNITE_CAPD_MODULEENROLMENT.E_STATUS="9","X",UNITE_CAPD_MODULEENROLMENT.E_STATUS))))) AS Status, Round(((Round(([Avg QCA Score]-10)/6,1))*[Gradient])+[Intercept],0) AS [ALIS Predict UCAS Points], LOOKUP_Target.Grade AS [Target Grade], LOOKUP_Predict.QCA_Ped_points AS [QCA points value], LOOKUP_Predict.UCAS AS [Staff Predict UCAS], LOOKUP_Predict_Actual.UCAS AS [Actual Residual], LOOKUP_Predict_Actual.QCA_Ped_points AS [Actual QCA points value], Round(([Actual Residual]-[ALIS Predict UCAS Points])/[SD],1) AS [Actual VA], Round(([Staff Predict UCAS]-[ALIS Predict UCAS Points])/[SD],1) AS [Predicted VA], UNITE_CAPD_MODULEENROLMENT.E_REFERENCE, UNITE_CAPD_SECTION.S_NAME, UNITE_CAPD_DEPARTMENT.D_NAME, Mid(UNITE_CAPD_MODULEENROLMENT.E_REFERENCE,2,2) AS test, [Course VA Target].VA_Target_VA, UNITE_CAPD_MODULEENROLMENT.E_NAME, [Team VA Target].Team_VA_Target, [Course VA Target].VA_Target_VA AS [Course VA target], UNITE_CAPD_ENROLMENTISR_1.EI_Q17 AS [Expected end]
FROM EQUATIONS_ALIS, LOOKUP_Target, LOOKUP_Predict, LOOKUP_Predict AS LOOKUP_Predict_Actual, [Course VA Target], UNITE_CAPD_ENROLMENTISR AS UNITE_CAPD_ENROLMENTISR_1 INNER JOIN ((((UNITE_CAPD_MODULE INNER JOIN ((((((UNITE_CAPD_MODULEENROLMENT INNER JOIN UNITE_CAPD_STUDENT ON UNITE_CAPD_MODULEENROLMENT.E_STUDENT = UNITE_CAPD_STUDENT.S_ID) INNER JOIN UNITE_CAPD_PERSON ON UNITE_CAPD_STUDENT.S_ID = UNITE_CAPD_PERSON.P_ID) INNER JOIN [QCA Score table] ON UNITE_CAPD_STUDENT.S_STUDENTREFERENCE = [QCA Score table].studentreference) INNER JOIN UNITE_CAPD_MODULEENROLMENT AS UNITE_CAPD_MODULEENROLMENT_CYR ON UNITE_CAPD_MODULEENROLMENT.E_PARENT = UNITE_CAPD_MODULEENROLMENT_CYR.E_ID) INNER JOIN UNITE_CAPD_MODULEENROLMENT AS UNITE_CAPD_MODULEENROLMENT_CRS ON UNITE_CAPD_MODULEENROLMENT_CYR.E_PARENT = UNITE_CAPD_MODULEENROLMENT_CRS.E_ID) INNER JOIN UNITE_CAPD_ENROLMENTISR ON UNITE_CAPD_MODULEENROLMENT_CRS.E_ID = UNITE_CAPD_ENROLMENTISR.EI_ID) ON UNITE_CAPD_MODULE.M_ID = UNITE_CAPD_MODULEENROLMENT.E_MODULE) INNER JOIN UNITE_CAPD_SECTION ON UNITE_CAPD_MODULE.M_MODULESECTION = UNITE_CAPD_SECTION.S_ID) INNER JOIN UNITE_CAPD_DEPARTMENT ON UNITE_CAPD_MODULE.M_MODULEDEPT = UNITE_CAPD_DEPARTMENT.D_ID) INNER JOIN [Team VA Target] ON UNITE_CAPD_SECTION.S_REFERENCE = [Team VA Target].Code) ON UNITE_CAPD_ENROLMENTISR_1.EI_ID = UNITE_CAPD_MODULEENROLMENT.E_ID
WHERE (((UNITE_CAPD_MODULEENROLMENT.E_STATUS) In ("1","2")) AND ((UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "9ND*/1/*" Or (UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "8ND*/2/*" Or (UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "9NC*" Or (UNITE_CAPD_MODULEENROLMENT.E_REFERENCE) Like "9NA*") AND ((Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,4))=[Course Code] And (Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,4))=[Course VA Target].[VA_Course Code] And (Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,4))=[Course Code]) AND ((IIf([Avg QCA Score] Is Null,-9,Round(((Round(([Avg QCA Score]-10)/6,1))*[Gradient])+[Intercept],0)+40)) Between [UCAS_Min] And [UCAS_Max] Or (IIf([Avg QCA Score] Is Null,-9,Round(((Round(([Avg QCA Score]-10)/6,1))*[Gradient])+[Intercept],0)+40)) Is Null) AND ((IIf([UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE] Is Null,"N/A",[UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE]))=[LOOKUP_Predict].[Grade] Or (IIf([UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE] Is Null,"N/A",[UNITE_CAPD_MODULEENROLMENT_CRS].[E_PROJGRADE])) Is Null) AND ((IIf([UNITE_CAPD_ENROLMENTISR].[EI_Q21] Is Null,"N/A",[UNITE_CAPD_ENROLMENTISR].[EI_Q21]))=[LOOKUP_Predict_Actual].[Grade] Or (IIf([UNITE_CAPD_ENROLMENTISR].[EI_Q21] Is Null,"N/A",[UNITE_CAPD_ENROLMENTISR].[EI_Q21])) Is Null) AND ((Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,2))=[LOOKUP_Target].[Code] Or (Mid([UNITE_CAPD_MODULEENROLMENT].[E_REFERENCE],2,2)) Is Null))
ORDER BY UNITE_CAPD_PERSON.P_SURNAME;
0
deanlee17Author Commented:
Oh, ignore that, this is the code behind that query....


SELECT [info for make table query for reporting].S_NAME, [info for make table query for reporting].E_REFERENCE, [info for make table query for reporting].E_NAME, Left([E_REFERENCE],10) AS [Course code], [info for make table query for reporting].E_REFERENCE AS [Full code], Count([info for make table query for reporting].PSN) AS CountOfPSN, Avg([info for make table query for reporting].[Actual VA]) AS [AvgOfActual VA], [info for make table query for reporting].Team_VA_Target, [info for make table query for reporting].D_NAME, Avg([info for make table query for reporting].[QCA points value]) AS [AvgOfQCA points value], StrConv([S_NAME],3) AS [Propercase Team], [info for make table query for reporting].Team_VA_Target, Round([Course VA target],1) AS [crs va], StrConv([D_NAME],3) AS [Propercase Academy], [info for make table query for reporting].[Course VA target], Avg([info for make table query for reporting].[Predicted VA]) AS [AvgOfPredicted VA], [QCA Sum]/[QCA Count] AS [QCA for Academy totals], Sum([info for make table query for reporting].[QCA points value]) AS [QCA Sum], Sum(IIf([QCA points value] Is Null,0,1)) AS [QCA Count], Sum([info for make table query for reporting].[Predicted VA]) AS [Predicted VA sum], Sum(IIf([Predicted VA] Is Null,0,1)) AS [Predicted VA Count], [Predicted VA sum]/[Predicted VA Count] AS [Predicted VA for Academy totals], Sum([info for make table query for reporting].[Actual VA]) AS [Actual VA sum], Sum(IIf([Actual VA] Is Null,0,1)) AS [Actual VA Count], [Actual VA sum]/[Actual VA Count] AS [Actual VA for Academy totals]
FROM [info for make table query for reporting]
GROUP BY [info for make table query for reporting].S_NAME, [info for make table query for reporting].E_REFERENCE, [info for make table query for reporting].E_NAME, Left([E_REFERENCE],10), [info for make table query for reporting].E_REFERENCE, [info for make table query for reporting].Team_VA_Target, [info for make table query for reporting].D_NAME, StrConv([S_NAME],3), [info for make table query for reporting].Team_VA_Target, Round([Course VA target],1), StrConv([D_NAME],3), [info for make table query for reporting].[Course VA target];
0
deanlee17Author Commented:
Which i then realised didnt have the test field added and not all works fine :)

Thanks very much, you have been very helpful.

0
deanlee17Author Commented:
Noticed one problem, when i select AS OR A2 (    sWhere = sWhere & "[test] like 'AS' OR 'A2'"   ) is displays all courses, when (at this stage of the database) it should display none
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You must use separate LIKE statements for this:

WHERE "[test] LIKE 'AS' OR [test] LIKE 'A2'"

This would show records where the [test] field had EITHER of those values.
0
deanlee17Author Commented:
Excellent support.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.