Parameter Form to build report - Requery if Null Nz

[MadAtMicrosoft]

After reading multiple threads here I have built a Form for the user to select and/or enter their parameters that they want used within a Report.

The Form includes 3 fields One a combobox to select the Employee and then 2 text boxes to select BeginDate and EndDate

My select query works well to show ALL employees if that Employee box is left blank when I first open the form. The Report includes all employees.

It also works well if I select an Employee preview and then change to a different selected employee and rerun the Report.

************************************
HOWEVER
If in that combobox the first time I run it I select an employee and preview the report and THEN try to re-preview the report for all employees by blanking out the field by highlighting whatever is in that field and then hitting the delete key and try to get a report of all employees I get the error:

"You tried to assign the Null value to a Variable that is not a Variant data Type"
​

Why would it be different the first time I open the form and leave that field blank and preview vs. re-previewing with a blank field?

Any way to fix this?

 

[vbaInet]

Look into the Nz() function:

http://www.techonthenet.com/access/functions/advanced/nz.php

What approach did you take for it to show when nothing is selected?

 

[MadAtMicrosoft]

SELECT [qry_Employees].[EmployeeID], [qry_Employees].[PRSystemEmpID], [qry_Employees].[EmpFullNickname], [qry_Employees].[EmpFullName] FROM qry_Employees;

 

[vbaInet]

There's nothing wrong with your query. You're running code somewhere that's causing it.

 

[MadAtMicrosoft]

In my Parameter selquery

[Forms]![frmTimeClockEntries-SelectedForReporting-Parameters]![cboSelectedEmployee]

 

[vbaInet]

You need to give more information.

 

[MadAtMicrosoft]

Not sure what else to give.

The Form [Forms]![frmTimeClockEntries-SelectedForReporting-Parameters] has a Combobox as it's first field. The name of that combobox is [cboSelectedEmployee] http://www.access-programmers.co.uk/forums/editpost.php?do=editpost&p=1007633

Then it has a StartDate and EndDate field.

I have a Select Query named "qselTimeCardEntries-SelectedForReporting"

On that query I have a criteria for the field [EmployeeID]

That Select Queries criteria gets set by the Form by the code:
([Forms]![frmTimeClockEntries-SelectedForReporting-Parameters]![cboSelectedEmployee]) Or IsNull(([Forms]![frmTimeClockEntries-SelectedForReporting-Parameters]![cboSelectedEmployee]))

All field's on the Event Tab of the Property Sheet for the Form (OnClick, OnUpdate, etc... are all unused.

*******************************************
The problem is that it will give me a report that includes ALL the employees when I first open the form, BUT if I have previewed a report wherein I have say selected Employee Joe Smith and then I decide that I'd like a report of ALL employees I have to close the Form and come back in to that Form for the Report to include all Employees. I would think that I should just be able to highlight whatever is currently in [cboSelectedEmployee] http://www.access-programmers.co.uk/forums/editpost.php?do=editpost&p=1007633and hit the delete key to clear the existing entry (thus it is a null field) and Preview the report for it to give me All the employees again like it did when I first open that Form.

Doesn't work that way though.

If the above detail isn't explicit enough can you give me an example of what else I should be detailing?

 

[vbaInet]

I wasn't sure what you were getting at initially because you hadn't explained there was a WHERE part to that query. Criteria is different from Parameter.

It's quite interesting that your report loads with all the records on first go. I'm thinking you have code that is facilitating this process.

Can I see how you open the report plus any code that relates to setting the record source of the report? By code I mean VBA code, not sql.

There are two ways to make a query display all records when nothing is selected - one is using the LIKE operator as criteria and the other is building the sql in code. The syntax for LIKE criteria would look something like this:

LIKE IIF(IsNull([Forms]![FormName]![FieldName]), "*", [Forms]![FormName]![FieldName])

By the way, you've got pretty long form names