Query using input from Forms

  • Thread starter Thread starter Carl
  • Start date Start date
C

Carl

I'm running a query for a report where the user will
select an event from a drop down list. The user will
have the option of selecting one event or leaving the
combo box empty. In VBA put a 0 in a form field if the
combobox value is null otherwise, the field contains the
unique ID.

In the query, I use the following statement to select the
appropriate records.

IIf([Forms]![frmReports]![intEventID]>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)

The query works correctly if a single event is selected
but returns no records if the combo box is null and form
field (intEventID) is 0.

What am I missing here?

Thanks for any help.
 
Carl said:
I'm running a query for a report where the user will
select an event from a drop down list. The user will
have the option of selecting one event or leaving the
combo box empty. In VBA put a 0 in a form field if the
combobox value is null otherwise, the field contains the
unique ID.

In the query, I use the following statement to select the
appropriate records.

IIf([Forms]![frmReports]![intEventID]>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)

The query works correctly if a single event is selected
but returns no records if the combo box is null and form
field (intEventID) is 0.

What am I missing here?

Thanks for any help.
Click to expand...

Carl,

If you want a zero in the IIF() condition when the combo box is Null, use the
NZ() function (Null to Zero).

Try this:

IIf(NZ([Forms]![frmReports]![intEventID])>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)


Note: the way you have written the immediate IF, if [intEventID] = 0, either
TRUE or FALSE will be returned.


HTH
 
-----Original Message-----
Carl said:
I'm running a query for a report where the user will
select an event from a drop down list. The user will
have the option of selecting one event or leaving the
combo box empty. In VBA put a 0 in a form field if the
combobox value is null otherwise, the field contains the
unique ID.

In the query, I use the following statement to select the
appropriate records.

IIf([Forms]![frmReports]![intEventID]>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)

The query works correctly if a single event is selected
but returns no records if the combo box is null and form
field (intEventID) is 0.

What am I missing here?

Thanks for any help.
Click to expand...

Carl,

If you want a zero in the IIF() condition when the combo box is Null, use the
NZ() function (Null to Zero).

Try this:

IIf(NZ([Forms]![frmReports]![intEventID])>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)


Note: the way you have written the immediate IF, if [intEventID] = 0, either
TRUE or FALSE will be returned.


HTH
Click to expand...

Steve,

Thanks for the tip. Unfortunately, I still not getting
any results if I leave the combo box empty (a null) but
do get results if I select a single event.

Using the watch window I noted that the value of the
combo box (cboEventList.Value) shows "3" (with the
quotes) for a single event and a "null" if left blank.
The type is shown as a Variant/String (for the combo box)
even though the ID field in the table is long number.
This, of course has me really confused.

Just for laughs I also tried just entering a ">0"
argument (without the quotes) in the criteria of the
query and did get all events to list.

Any other suggestions?

Thanks for the help
Carl
 
Carl said:
-----Original Message-----
Carl wrote:

I'm running a query for a report where the user will
select an event from a drop down list. The user will
have the option of selecting one event or leaving the
combo box empty. In VBA put a 0 in a form field if
Click to expand...
the
combobox value is null otherwise, the field contains
Click to expand...
the
unique ID.

In the query, I use the following statement to select
Click to expand...
the
appropriate records.

IIf([Forms]![frmReports]![intEventID]>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)

The query works correctly if a single event is
Click to expand...
selected
but returns no records if the combo box is null and
Click to expand...
form
field (intEventID) is 0.

What am I missing here?

Thanks for any help.
Click to expand...

Carl,

If you want a zero in the IIF() condition when the combo
Click to expand...

box is Null, use the
NZ() function (Null to Zero).

Try this:

IIf(NZ([Forms]![frmReports]![intEventID])>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)


Note: the way you have written the immediate IF, if
Click to expand...

[intEventID] = 0, either
TRUE or FALSE will be returned.


HTH
Click to expand...


Steve,

Thanks for the tip. Unfortunately, I still not getting
any results if I leave the combo box empty (a null) but
do get results if I select a single event.

Using the watch window I noted that the value of the
combo box (cboEventList.Value) shows "3" (with the
quotes) for a single event and a "null" if left blank.
The type is shown as a Variant/String (for the combo box)
even though the ID field in the table is long number.
This, of course has me really confused.

Just for laughs I also tried just entering a ">0"
argument (without the quotes) in the criteria of the
query and did get all events to list.

Any other suggestions?

Thanks for the help
Carl
Click to expand...

I'm confused now also. In the IIF() function, you are testing for [intEventID]>0
but in your now you are referencing [cboEventList].


Please post the SQL of the combo box rowsource and the name of the combo box.
 
-----Original Message-----
Carl said:
-----Original Message-----
Carl wrote:


I'm running a query for a report where the user will
select an event from a drop down list. The user will
have the option of selecting one event or leaving the
combo box empty. In VBA put a 0 in a form field if
the

combobox value is null otherwise, the field contains
the

unique ID.

In the query, I use the following statement to select
the

appropriate records.

IIf([Forms]![frmReports]![intEventID]>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)

The query works correctly if a single event is
selected

but returns no records if the combo box is null and
form

field (intEventID) is 0.

What am I missing here?

Thanks for any help.


Carl,

If you want a zero in the IIF() condition when the
Click to expand...
combo

box is Null, use the
NZ() function (Null to Zero).

Try this:

IIf(NZ([Forms]![frmReports]![intEventID])>0,[Forms]!
[frmReports]![intEventID],[tblPayrollEvent].[ID]>=0)


Note: the way you have written the immediate IF, if
Click to expand...

[intEventID] = 0, either
TRUE or FALSE will be returned.


HTH
Click to expand...


Steve,

Thanks for the tip. Unfortunately, I still not getting
any results if I leave the combo box empty (a null) but
do get results if I select a single event.

Using the watch window I noted that the value of the
combo box (cboEventList.Value) shows "3" (with the
quotes) for a single event and a "null" if left blank.
The type is shown as a Variant/String (for the combo box)
even though the ID field in the table is long number.
This, of course has me really confused.

Just for laughs I also tried just entering a ">0"
argument (without the quotes) in the criteria of the
query and did get all events to list.

Any other suggestions?

Thanks for the help
Carl
Click to expand...

I'm confused now also. In the IIF() function, you are testing for [intEventID]>0
but in your now you are referencing [cboEventList].


Please post the SQL of the combo box rowsource and the name of the combo box.
Click to expand...

Steve,

I apologize for the confustion. I use form code that
calls the Query. While experimenting in that code, I was
taking the value of [cboEventList] and storing it in
[intEventID] then using [intEventID] to test for the
value of >0.

The SQL query is shown below:

SELECT tblPayrollEvent.ID, tblPayrollEvent.Event,
tblPayrollEvent.EventDate, tblPayCode.PayCode,
tblVenueTable.VENUE, tblPayrollEvent.MS_SS,
tblPayrollEvent.AutoDeduction, tblNameMaster.Name,
tblPayroll.AcctNo, tblPayroll.WorkDate,
tblPayroll.InCrew, tblPayroll.InHours, tblPayroll.InOT,
tblPayroll.InDT, tblPayroll.InGross, tblPayroll.WorkCrew,
tblPayroll.WorkHours, tblPayroll.WorkOT,
tblPayroll.WorkDT, tblPayroll.WorkGross,
tblPayroll.ContCrew, tblPayroll.ContHours,
tblPayroll.ContOT, tblPayroll.ContGross,
tblPayroll.ShowCrew, tblPayroll.ShowHours,
tblPayroll.ShowGross, tblPayroll.OutCrew,
tblPayroll.OutHours, tblPayroll.OutOT, tblPayroll.OutDT,
tblPayroll.OutGross, tblPayroll.GrossPay INTO
tblPayrollReport
FROM tblPayCode RIGHT JOIN (tblVenueTable RIGHT JOIN
(tblNameMaster RIGHT JOIN (tblPayroll LEFT JOIN
tblPayrollEvent ON tblPayroll.Event = tblPayrollEvent.ID)
ON tblNameMaster.AcctNo = tblPayroll.AcctNo) ON
tblVenueTable.VEN_CODE = tblPayrollEvent.Venue) ON
tblPayCode.ID = tblPayrollEvent.PayrollCode
WHERE (((tblPayrollEvent.ID)=IIf([Forms]![frmReports]!
[intEventID]>"1",[Forms]![frmReports]![intEventID],
[tblPayrollEvent].[ID]>1)));

This one is slightly different in that I added an "Event"
to the table the [cboEventList] references with an ID of
1 (hence the test for >1). In theory, I was getting
around the possibility of a "null" value. However, this
one also does not work as I'm intending.

The form code i'm using to call the query is as follows:

Me.intEventID = Nz(Me.cboEventList.Value, "1")
DoCmd.OpenQuery "qryPayrollReport"

Again, I appreciate the assist and am stumped on how to
resolve this.

Carl
 
but

blank.

box)


I'm confused now also. In the IIF() function, you are
Click to expand...

testing for [intEventID]>0
but in your now you are referencing [cboEventList].


Please post the SQL of the combo box rowsource and the
Click to expand...

name of the combo box.


Steve,

I apologize for the confustion. I use form code that
calls the Query. While experimenting in that code, I was
taking the value of [cboEventList] and storing it in
[intEventID] then using [intEventID] to test for the
value of >0.

The SQL query is shown below:

SELECT tblPayrollEvent.ID, tblPayrollEvent.Event,
tblPayrollEvent.EventDate, tblPayCode.PayCode,
tblVenueTable.VENUE, tblPayrollEvent.MS_SS,
tblPayrollEvent.AutoDeduction, tblNameMaster.Name,
tblPayroll.AcctNo, tblPayroll.WorkDate,
tblPayroll.InCrew, tblPayroll.InHours, tblPayroll.InOT,
tblPayroll.InDT, tblPayroll.InGross, tblPayroll.WorkCrew,
tblPayroll.WorkHours, tblPayroll.WorkOT,
tblPayroll.WorkDT, tblPayroll.WorkGross,
tblPayroll.ContCrew, tblPayroll.ContHours,
tblPayroll.ContOT, tblPayroll.ContGross,
tblPayroll.ShowCrew, tblPayroll.ShowHours,
tblPayroll.ShowGross, tblPayroll.OutCrew,
tblPayroll.OutHours, tblPayroll.OutOT, tblPayroll.OutDT,
tblPayroll.OutGross, tblPayroll.GrossPay INTO
tblPayrollReport
FROM tblPayCode RIGHT JOIN (tblVenueTable RIGHT JOIN
(tblNameMaster RIGHT JOIN (tblPayroll LEFT JOIN
tblPayrollEvent ON tblPayroll.Event = tblPayrollEvent.ID)
ON tblNameMaster.AcctNo = tblPayroll.AcctNo) ON
tblVenueTable.VEN_CODE = tblPayrollEvent.Venue) ON
tblPayCode.ID = tblPayrollEvent.PayrollCode
WHERE (((tblPayrollEvent.ID)=IIf([Forms]![frmReports]!
[intEventID]>"1",[Forms]![frmReports]![intEventID],
[tblPayrollEvent].[ID]>1)));

This one is slightly different in that I added an "Event"
to the table the [cboEventList] references with an ID of
1 (hence the test for >1). In theory, I was getting
around the possibility of a "null" value. However, this
one also does not work as I'm intending.

The form code i'm using to call the query is as follows:

Me.intEventID = Nz(Me.cboEventList.Value, "1")
DoCmd.OpenQuery "qryPayrollReport"

Again, I appreciate the assist and am stumped on how to
resolve this.

Carl
Click to expand...

Carl,

So you have a form 'frmReports' with a combo box 'cboEventList' that is used as
criteria for a query 'qryPayrollReport'.

What is the SQL of the rowsource for 'cboEventList'?
Is the bound column for 'cboEventList' an Integer or LongInteger?


If you replace the Where clause in the query 'qryPayrollReport' with:

WHERE tblPayrollEvent.ID) = [Forms]![frmReports]![cboEventList];

and select an event in [cboEventList], are the correct records returned?


If you replace the Where clause in the query 'qryPayrollReport' with (watch for
line wrap):

WHERE tblPayrollEvent.ID)= [Forms]![frmReports]![cboEventList] OR
[Forms]![frmReports]![cboEventList] Is Null;

and DON'T select an event in [cboEventList], are all records returned?



Another way to handle this is to replace the following in the form code
Me.intEventID = Nz(Me.cboEventList.Value, "1")
DoCmd.OpenQuery "qryPayrollReport"
Click to expand...

with


'--- snip -----
If IsNull(Me.cboEventList) then
Msgbox "Please Select an Event ID"
Me.cboEventList.SetFocus
Exit Sub
End If

DoCmd.OpenQuery "qryPayrollReport"

'--- snip -----

Then the criteria for the query 'qryPayrollReport' could be:

WHERE tblPayrollEvent.ID)= [Forms]![frmReports]![cboEventList];




The Value property is the default so you don't need to explicitly use it in the
expression.
 
-----Original Message-----
Steve,

Thanks for the tip. Unfortunately, I still not getting
any results if I leave the combo box empty (a null)
but

do get results if I select a single event.

Using the watch window I noted that the value of the
combo box (cboEventList.Value) shows "3" (with the
quotes) for a single event and a "null" if left
blank.

The type is shown as a Variant/String (for the combo
box)

even though the ID field in the table is long number.
This, of course has me really confused.

Just for laughs I also tried just entering a ">0"
argument (without the quotes) in the criteria of the
query and did get all events to list.

Any other suggestions?

Thanks for the help
Carl

I'm confused now also. In the IIF() function, you are
Click to expand...

testing for [intEventID]>0
but in your now you are referencing [cboEventList].


Please post the SQL of the combo box rowsource and the
Click to expand...

name of the combo box.


Steve,

I apologize for the confustion. I use form code that
calls the Query. While experimenting in that code, I was
taking the value of [cboEventList] and storing it in
[intEventID] then using [intEventID] to test for the
value of >0.

The SQL query is shown below:

SELECT tblPayrollEvent.ID, tblPayrollEvent.Event,
tblPayrollEvent.EventDate, tblPayCode.PayCode,
tblVenueTable.VENUE, tblPayrollEvent.MS_SS,
tblPayrollEvent.AutoDeduction, tblNameMaster.Name,
tblPayroll.AcctNo, tblPayroll.WorkDate,
tblPayroll.InCrew, tblPayroll.InHours, tblPayroll.InOT,
tblPayroll.InDT, tblPayroll.InGross, tblPayroll.WorkCrew,
tblPayroll.WorkHours, tblPayroll.WorkOT,
tblPayroll.WorkDT, tblPayroll.WorkGross,
tblPayroll.ContCrew, tblPayroll.ContHours,
tblPayroll.ContOT, tblPayroll.ContGross,
tblPayroll.ShowCrew, tblPayroll.ShowHours,
tblPayroll.ShowGross, tblPayroll.OutCrew,
tblPayroll.OutHours, tblPayroll.OutOT, tblPayroll.OutDT,
tblPayroll.OutGross, tblPayroll.GrossPay INTO
tblPayrollReport
FROM tblPayCode RIGHT JOIN (tblVenueTable RIGHT JOIN
(tblNameMaster RIGHT JOIN (tblPayroll LEFT JOIN
tblPayrollEvent ON tblPayroll.Event = tblPayrollEvent.ID)
ON tblNameMaster.AcctNo = tblPayroll.AcctNo) ON
tblVenueTable.VEN_CODE = tblPayrollEvent.Venue) ON
tblPayCode.ID = tblPayrollEvent.PayrollCode
WHERE (((tblPayrollEvent.ID)=IIf([Forms]![frmReports]!
[intEventID]>"1",[Forms]![frmReports]![intEventID],
[tblPayrollEvent].[ID]>1)));

This one is slightly different in that I added an "Event"
to the table the [cboEventList] references with an ID of
1 (hence the test for >1). In theory, I was getting
around the possibility of a "null" value. However, this
one also does not work as I'm intending.

The form code i'm using to call the query is as follows:

Me.intEventID = Nz(Me.cboEventList.Value, "1")
DoCmd.OpenQuery "qryPayrollReport"

Again, I appreciate the assist and am stumped on how to
resolve this.

Carl
Click to expand...

Carl,

So you have a form 'frmReports' with a combo
Click to expand...
box 'cboEventList' that is used as
criteria for a query 'qryPayrollReport'.

What is the SQL of the rowsource for 'cboEventList'?
Is the bound column for 'cboEventList' an Integer or LongInteger?


If you replace the Where clause in the query 'qryPayrollReport' with:

WHERE tblPayrollEvent.ID) = [Forms]![frmReports]! [cboEventList];

and select an event in [cboEventList], are the correct records returned?


If you replace the Where clause in the
Click to expand...
query 'qryPayrollReport' with (watch for
line wrap):

WHERE tblPayrollEvent.ID)= [Forms]![frmReports]! [cboEventList] OR
[Forms]![frmReports]![cboEventList] Is Null;

and DON'T select an event in [cboEventList], are all records returned?



Another way to handle this is to replace the following in the form code
Me.intEventID = Nz(Me.cboEventList.Value, "1")
DoCmd.OpenQuery "qryPayrollReport"
Click to expand...

with


'--- snip -----
If IsNull(Me.cboEventList) then
Msgbox "Please Select an Event ID"
Me.cboEventList.SetFocus
Exit Sub
End If

DoCmd.OpenQuery "qryPayrollReport"

'--- snip -----

Then the criteria for the query 'qryPayrollReport' could be:

WHERE tblPayrollEvent.ID)= [Forms]![frmReports]! [cboEventList];




The Value property is the default so you don't need to explicitly use it in the
expression.
Click to expand...
Steve,

SUCCESS!! Using the replacement code you suggested:

WHERE tblPayrollEvent.ID)= [Forms]![frmReports]!
[cboEventList] OR
[Forms]![frmReports]![cboEventList] Is Null;

the query works perfectly - just as I wanted. If the
cboEventList is a null, all records are selected. If a
value is chosen, only that event is reported.

It was necessary for me to keep the null option (or an
alternative) as the user may want to report on all
events - not just one.

Thank you very much for the ongoing help.

Carl
 
Back
Top