Problem with IIF in selecting a source for data

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

BobC - 21 Mar 2008 00:04 GMT

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

Reply to this Message

MGFoster - 21 Mar 2008 04:34 GMT

> I am currently using the expression: Between
> DateSerial([Forms]![frmClaimDetail]![cboBeginYear],[Forms]![frmClaimDetail]![cboBeginmonth],1)
[quoted text clipped - 14 lines]
> Suggestions Please?
> Bob

Why not use the .IsLoaded property of the form?

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

Signature

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

Reply to this Message

BobC - 21 Mar 2008 05:34 GMT

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)
[quoted text clipped - 23 lines]
> DateSerial(Forms!frmClaimDistributionSheets.cbo...etc. ),
> DateSerial(Forms!frmClaimDetail!cboBeginYear... etc. ))

Reply to this Message

John W. Vinson - 21 Mar 2008 08:00 GMT

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

Signature

John W. Vinson [MVP]

Reply to this Message

John Spencer - 21 Mar 2008 12:39 GMT

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.

Signature

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

>>That appears to be a good idea; however, seem to be bogged down with a
>>syntax error.
[quoted text clipped - 4 lines]
> SQL
> in a query (or so it appears, I've never tried it).

Reply to this Message

MGFoster - 24 Mar 2008 08:52 GMT

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.

Signature

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

> That appears to be a good idea; however, seem to be bogged down with a
> syntax error.
[quoted text clipped - 42 lines]
>> DateSerial(Forms!frmClaimDistributionSheets.cbo...etc. ),
>> DateSerial(Forms!frmClaimDetail!cboBeginYear... etc. ))

Reply to this Message