Access 2013 Query

[Raabi]

Hello geeks!

I am grappling with the Query design view of Access 2013 for two days, day and night, in the following way :-(

It is a very simple Query, named "qryStudentsByClass" with a few fields, notably:

StudentID, StudentName, DoB, Age (Calculated field), and Class etc.

I have entered the Criteria for Class as "ClassName"

Within my Form, I have included the following VBA code:

           strClass = Me.txtClass.Value
            Set QryDef = CurrentDb.QueryDefs("qryStudentsByClass")

' Just to try any of the following 3 statements, one by one, of course:

           QryDef![ClassName] = strClass
            QryDef.Parameters(0).Value = strClass
            QryDef.Parameters("ClassName").Value = strClass

            rptName = "rptStudentsByClass"
            DoCmd.OpenReport reportName:=rptName, View:=acViewReport
            strQrySql = QryDef.SQL
            Reports(rptName).RecordSource = strQrySql
            ...
            ...

Testing:

Debug.Print QryDef.Parameters.Count --- returns [COLOR=DarkRed][I]1[/I][/COLOR]

and

Debug.Print QryDef.Parameters(0).Name ---- correctly returns [COLOR=DarkRed][I]ClassName[/I][/COLOR]

Problem:
The problem is that when I run the program, Query still prompts for the ClassName, instead of receiving the value passed from the Form.

I have even tried including the following line of code in the Criteria line of the Query:

[Forms]![frmStudentsRpt]![txtClass] ------ with/without = sign.

But, still the problem remains the same.

Would any geek kindly get me out of this mess! Salute in anticipation

 

[sneuberg]

This seems awful complicated. Why not make qryStudentsByClass the record source of your report and then just

DoCmd.OpenReport reportName:=rptname, View:=acViewReport, [COLOR="Blue"]WhereCondition:="Class = '" & Me.txtClass & "'"[/COLOR]

 

[Raabi]

Thanks Sneuberg for your attention.
I tried your suggestion, with exactly the same result it is still prompting for the ClassName.
Further suggestion will be appreciated.

 

[Raabi]

Further to my previous reply, please note that I am using the Query because it contains a calculated field, Age, as well.

 

[sneuberg]

Thanks Sneuberg for your attention.
I tried your suggestion, with exactly the same result it is still prompting for the ClassName.
Further suggestion will be appreciated.

You probably still have ClassName in the query as a parameter. Just take it out. The query should not have any criteria specified. It will get that from the report where clause.

Having the calculated age in the query shouldn't be a problem.

 

[JHB]

Show the SQL-string for "qryStudentsByClass" and some sample data in Excel or database.

 

[Raabi]

Thanks again.
SQL string for the qryStudentsByClass is:

PARAMETERS [ClassName] Text ( 255 );
SELECT tblStudents.StudentID, tblStudents.StudentName, tblStudents.DOB, tblStudents.Class, (Date()-[DOB])/365.25 AS Age, tblStudents.Section, tblStudents.FatherName, tblStudents.ContactNum, tblStudents.Remarks, tblStudents.Picture, *
FROM tblStudents
WHERE (((tblStudents.Class)=[ClassName]))
ORDER BY tblStudents.Class;

Also, I have again made sure that; there is No Parameter listed in the Parameter window.
The sample value of the Class is just a number; like 6, 7, 8, 9 and 10.

I appreciate your effort. Have a nice time.

 

[sneuberg]

Change the SQL to

SELECT tblStudents.StudentID, tblStudents.StudentName, tblStudents.DOB, tblStudents.Class, (Date()-[DOB])/365.25 AS Age, tblStudents.Section, tblStudents.FatherName, tblStudents.ContactNum, tblStudents.Remarks, tblStudents.Picture, *
FROM tblStudents
ORDER BY tblStudents.Class;

And I think you will find my suggestion will work fine.

Going to bed now. Good night.

 

[Raabi]

Bravo! It worked. It really worked.
You have made my day.
I have best wishes for you, the real geek!
Best regards.