Problem with IIF in selecting a source for data

[BobC]

I am currently using the expression: Between
DateSerial([Forms]![frmClaimDetail]![cboBeginYear],[Forms]![frmClaimDetail]![cboBeginmonth],1)
And
DateSerial([Forms]![frmClaimDetail]![cboEndYear],[Forms]![frmClaimDetail]![cboEndmonth]+1,0)
in the date field of a query (qryClaimDetails) to limit the selection in
the query for one of my reports.
I now want to 'alternatively' use another form
(frmClaimDistributionSheets) to limit qryClaimDetails for another Report.
Only one of the two forms will be active at any given time.
I tried using an IFF()to select which form I was getting my beginning
and ending dates from, using a
IsNull(IsNull([Forms]![frmClaimDetail]![cboBeginYear]) to test to see if
one of the forms was active in the IIF statement; but that did not seem
to work ... probably because it does not understand what I am talking
about if that form is not active.
Suggestions Please?
Bob

 

[BobC]

That appears to be a good idea; however, seem to be bogged down with a
syntax error.
I am getting an error message "Enter Parameter Value"
"Forms!frmClaimDetail.IsLoaded"

This is the statement:
IIf(Forms!frmClaimDetail.IsLoaded,(Between
DateSerial([Forms]![frmClaimDetail]![cboBeginYear],[Forms]![frmClaimDetail]![cboBeginmonth],1)
And
DateSerial([Forms]![frmClaimDetail]![cboEndYear],[Forms]![frmClaimDetail]![cboEndmonth]+1,0)),(Between
DateSerial([Forms]![frmClaimDistributionCheckSheets]![cboBeginYear],[Forms]![frmClaimDistributionCheckSheets]![cboBeginmonth],1)
And
DateSerial([Forms]![frmClaimDistributionCheckSheets]![cboEndYear],[Forms]![frmClaimDistributionCheckSheets]![cboEndmonth]+1,0)))

I get confused about when I need the [] ???
Thanks,
Bob

I am currently using the expression: Between
DateSerial([Forms]![frmClaimDetail]![cboBeginYear],[Forms]![frmClaimDetail]![cboBeginmonth],1)
And
DateSerial([Forms]![frmClaimDetail]![cboEndYear],[Forms]![frmClaimDetail]![cboEndmonth]+1,0)

in the date field of a query (qryClaimDetails) to limit the selection
in the query for one of my reports.
I now want to 'alternatively' use another form
(frmClaimDistributionSheets) to limit qryClaimDetails for another Report.
Only one of the two forms will be active at any given time.
I tried using an IFF()to select which form I was getting my beginning
and ending dates from, using a
IsNull(IsNull([Forms]![frmClaimDetail]![cboBeginYear]) to test to see
if one of the forms was active in the IIF statement; but that did not
seem to work ... probably because it does not understand what I am
talking about if that form is not active.
Suggestions Please?
Bob

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Why not use the .IsLoaded property of the form?

IIf(Forms!frmClaimDistributionSheets.IsLoaded,
DateSerial(Forms!frmClaimDistributionSheets.cbo...etc. ),
DateSerial(Forms!frmClaimDetail!cboBeginYear... etc. ))

 

[John W. Vinson]

That appears to be a good idea; however, seem to be bogged down with a
syntax error.
I am getting an error message "Enter Parameter Value"
"Forms!frmClaimDetail.IsLoaded"

IsLoaded is a VBA function property of a Form object; it can't be used in SQL
in a query (or so it appears, I've never tried it).

 

[John Spencer]

A couple of ways to handle the problem.

A) use a function to see which form is open and then pass back the needed
value(s) depending on which form is open.

B) always use form frmClaimDetail in the query. When you open the second
form, use it to also open frmClaimDetail and use code to populate the values
in frmClaimDetail in the after update event of the second form

C) set global variables to hold the values you need and use a simple
function in the query to get the values of the global variables.

Public Function fGetStartDate()
fGetStartDate= globalStartDate
End If

Public Function fGetEndDate
fGetEndDate = globalEndDate
End If

D) (My choice) Design a separate form to gather the relevant data and use
that as the source for the parameters. You could use the new form's
openArgs argument to pass in information to populate the relevant fields.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..