Problem with IIF in selecting a source for data

B

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
 
M

MGFoster

BobC said:
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
Click to expand...

-----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. ))

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR+MspoechKqOuFEgEQJI6ACgheoz/ziPvPXDmQ89jS+c6k9O9R4An1EN
nDN5t4VBvYrvFjik146D507Q
=hDrE
-----END PGP SIGNATURE-----
 
B

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

BobC said:
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
Click to expand...

-----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. ))
Click to expand...
 
J

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"
Click to expand...

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).
 
J

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
..
 
M

MGFoster

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

Sorry, IsLoaded should be used from the AllForms collection. Ex:

CurrentProject.AllForms![form name].IsLoaded

Your set up would use (formatted for easier reading):

IIf(CurrentProject.AllForms!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)
)

Only use the [] brackets when the name is a reserved word (using the
brackets indicates that the name is not to be processed as a reserved
word) or there is a space between words in the name.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBR+ddtoechKqOuFEgEQKdWgCgwTI/IbpCohjZG60GUMliOxXsAtkAniQS
A7aNgptZwyZettY6dqCaXTbS
=3aec
-----END PGP SIGNATURE-----
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

BobC said:
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
Click to expand...

-----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. ))
Click to expand...
Click to expand...
 
Top