Help with code

S

scubadiver

Apologies for the repost.

I have a set of fields that require information before "Qry_status" can be
changed from "outstanding" to "complete". The added complication is that if
the query type is an "invoice" then I need an extra two fields.

What I had originally intended wouldn't work. When it comes to something
this complicated I don't have any experience so please be *very* clear.


The list of controls required where [Qry_QryType] is NOT an "invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5



The list of controls required where the option for [Qry_QryType] is "invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7
 
J

John Spencer

You gave no indication on where you needed this, what alternative values
that qry_qryType could have, and whether or not qry_Status had to be
Outstanding before it could be changed to complete.

You can concatenate all the fields together using the + operator (which will
propagate nulls) and then check to see if the result is null. If the result
is null, then at least one of the items you wish to have a value does not
have a value.

Select Case Nz(qry_qryType, "NOT Invoice")
Case "NOT Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.qryStatus = "Complete"
End if
Case "Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then
Me.qryStatus = "Complete"
End if
End Select


In a query you might have to use nested IIF statements

IIF(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3
+ SLA_Date4 + SLA_Date5 Is Not Null and qry_qrytype = "??Check for some
value or check for <> Invoice??", "Complete", IIF(Cont_InvName +
Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3 + SLA_Date4 +
SLA_Date5 + SLA_Date6 + SLA_Date7 Is Not Null and qry_qryType
="Invoice","Complete", qry_qryStatus))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
S

scubadiver

Thanks!

The first code could do the trick but where do I put it?



John Spencer said:
You gave no indication on where you needed this, what alternative values
that qry_qryType could have, and whether or not qry_Status had to be
Outstanding before it could be changed to complete.

You can concatenate all the fields together using the + operator (which will
propagate nulls) and then check to see if the result is null. If the result
is null, then at least one of the items you wish to have a value does not
have a value.

Select Case Nz(qry_qryType, "NOT Invoice")
Case "NOT Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.qryStatus = "Complete"
End if
Case "Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then
Me.qryStatus = "Complete"
End if
End Select


In a query you might have to use nested IIF statements

IIF(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3
+ SLA_Date4 + SLA_Date5 Is Not Null and qry_qrytype = "??Check for some
value or check for <> Invoice??", "Complete", IIF(Cont_InvName +
Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3 + SLA_Date4 +
SLA_Date5 + SLA_Date6 + SLA_Date7 Is Not Null and qry_qryType
="Invoice","Complete", qry_qryStatus))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
Apologies for the repost.

I have a set of fields that require information before "Qry_status" can be
changed from "outstanding" to "complete". The added complication is that
if
the query type is an "invoice" then I need an extra two fields.

What I had originally intended wouldn't work. When it comes to something
this complicated I don't have any experience so please be *very* clear.


The list of controls required where [Qry_QryType] is NOT an "invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5



The list of controls required where the option for [Qry_QryType] is
"invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7
Click to expand...
Click to expand...
 
J

John Spencer

Again, WHERE are you using this? In a form?

If in a form, try the form's after update event.

OR create the code as a sub that you can call from each control's after
update event and set the value.

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

scubadiver said:
Thanks!

The first code could do the trick but where do I put it?



John Spencer said:
You gave no indication on where you needed this, what alternative values
that qry_qryType could have, and whether or not qry_Status had to be
Outstanding before it could be changed to complete.

You can concatenate all the fields together using the + operator (which
will
propagate nulls) and then check to see if the result is null. If the
result
is null, then at least one of the items you wish to have a value does not
have a value.

Select Case Nz(qry_qryType, "NOT Invoice")
Case "NOT Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.qryStatus = "Complete"
End if
Case "Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True
Then
Me.qryStatus = "Complete"
End if
End Select


In a query you might have to use nested IIF statements

IIF(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3
+ SLA_Date4 + SLA_Date5 Is Not Null and qry_qrytype = "??Check for some
value or check for <> Invoice??", "Complete", IIF(Cont_InvName +
Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3 + SLA_Date4 +
SLA_Date5 + SLA_Date6 + SLA_Date7 Is Not Null and qry_qryType
="Invoice","Complete", qry_qryStatus))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
Apologies for the repost.

I have a set of fields that require information before "Qry_status" can
be
changed from "outstanding" to "complete". The added complication is
that
if
the query type is an "invoice" then I need an extra two fields.

What I had originally intended wouldn't work. When it comes to
something
this complicated I don't have any experience so please be *very* clear.


The list of controls required where [Qry_QryType] is NOT an "invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5



The list of controls required where the option for [Qry_QryType] is
"invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7
Click to expand...
Click to expand...
Click to expand...
 
S

scubadiver

I am using it in a form but I have changed the code slightly:


Select Case Nz(Qry_QryType, "NOT Invoice")
Case "NOT Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then Me.Qry_Status.Locked = True
Else Me.Qry_Status.Locked = False

Case "Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then
Me.Qry_Status.Locked = True Else Me.Qry_Status.Locked = False

End Select


It isn't quite working. If all the fields are empty the "qry_status" combo
is locked but if I enter info into all the fields the combo remains locked.



John Spencer said:
Again, WHERE are you using this? In a form?

If in a form, try the form's after update event.

OR create the code as a sub that you can call from each control's after
update event and set the value.

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

scubadiver said:
Thanks!

The first code could do the trick but where do I put it?



John Spencer said:
You gave no indication on where you needed this, what alternative values
that qry_qryType could have, and whether or not qry_Status had to be
Outstanding before it could be changed to complete.

You can concatenate all the fields together using the + operator (which
will
propagate nulls) and then check to see if the result is null. If the
result
is null, then at least one of the items you wish to have a value does not
have a value.

Select Case Nz(qry_qryType, "NOT Invoice")
Case "NOT Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.qryStatus = "Complete"
End if
Case "Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True
Then
Me.qryStatus = "Complete"
End if
End Select


In a query you might have to use nested IIF statements

IIF(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3
+ SLA_Date4 + SLA_Date5 Is Not Null and qry_qrytype = "??Check for some
value or check for <> Invoice??", "Complete", IIF(Cont_InvName +
Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3 + SLA_Date4 +
SLA_Date5 + SLA_Date6 + SLA_Date7 Is Not Null and qry_qryType
="Invoice","Complete", qry_qryStatus))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..


Apologies for the repost.

I have a set of fields that require information before "Qry_status" can
be
changed from "outstanding" to "complete". The added complication is
that
if
the query type is an "invoice" then I need an extra two fields.

What I had originally intended wouldn't work. When it comes to
something
this complicated I don't have any experience so please be *very* clear.


The list of controls required where [Qry_QryType] is NOT an "invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5



The list of controls required where the option for [Qry_QryType] is
"invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Select Case Nz(Qry_QryType, "NOT Invoice")
Case "NOT Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.Qry_Status.Locked = True
Else
Me.Qry_Status.Locked = False
End if
Case "Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then

Me.Qry_Status.Locked = True
Else
Me.Qry_Status.Locked = False
end if
End Select


Or you could use
....
Case "Not Invoice"
Me.Qry_Status.Locked = IsNull(Cont_InvName + Qry_ProdType +
Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5)
Case "Invoice"
Me.Qry_Status.Locked = IsNull(Cont_InvName + Qry_ProdType +
Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7)
End Select
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
I am using it in a form but I have changed the code slightly:


Select Case Nz(Qry_QryType, "NOT Invoice")
Case "NOT Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then Me.Qry_Status.Locked = True
Else Me.Qry_Status.Locked = False

Case "Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then
Me.Qry_Status.Locked = True Else Me.Qry_Status.Locked = False

End Select


It isn't quite working. If all the fields are empty the "qry_status" combo
is locked but if I enter info into all the fields the combo remains
locked.



John Spencer said:
Again, WHERE are you using this? In a form?

If in a form, try the form's after update event.

OR create the code as a sub that you can call from each control's after
update event and set the value.

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

scubadiver said:
Thanks!

The first code could do the trick but where do I put it?



:

You gave no indication on where you needed this, what alternative
values
that qry_qryType could have, and whether or not qry_Status had to be
Outstanding before it could be changed to complete.

You can concatenate all the fields together using the + operator
(which
will
propagate nulls) and then check to see if the result is null. If the
result
is null, then at least one of the items you wish to have a value does
not
have a value.

Select Case Nz(qry_qryType, "NOT Invoice")
Case "NOT Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.qryStatus = "Complete"
End if
Case "Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True
Then
Me.qryStatus = "Complete"
End if
End Select


In a query you might have to use nested IIF statements

IIF(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3
+ SLA_Date4 + SLA_Date5 Is Not Null and qry_qrytype = "??Check for
some
value or check for <> Invoice??", "Complete", IIF(Cont_InvName +
Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3 + SLA_Date4 +
SLA_Date5 + SLA_Date6 + SLA_Date7 Is Not Null and qry_qryType
="Invoice","Complete", qry_qryStatus))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..


Apologies for the repost.

I have a set of fields that require information before "Qry_status"
can
be
changed from "outstanding" to "complete". The added complication is
that
if
the query type is an "invoice" then I need an extra two fields.

What I had originally intended wouldn't work. When it comes to
something
this complicated I don't have any experience so please be *very*
clear.


The list of controls required where [Qry_QryType] is NOT an
"invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5



The list of controls required where the option for [Qry_QryType] is
"invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7
Click to expand...
Click to expand...
Click to expand...
 
S

scubadiver

Thanks for the reply. The combo remains locked after all fields have been
entered. I wonder what "else" cases would be considered in the code?

Would it be ALL fields are not empty? Or something else?

John Spencer said:
Select Case Nz(Qry_QryType, "NOT Invoice")
Case "NOT Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.Qry_Status.Locked = True
Else
Me.Qry_Status.Locked = False
End if
Case "Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then

Me.Qry_Status.Locked = True
Else
Me.Qry_Status.Locked = False
end if
End Select


Or you could use
....
Case "Not Invoice"
Me.Qry_Status.Locked = IsNull(Cont_InvName + Qry_ProdType +
Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5)
Case "Invoice"
Me.Qry_Status.Locked = IsNull(Cont_InvName + Qry_ProdType +
Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7)
End Select
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

scubadiver said:
I am using it in a form but I have changed the code slightly:


Select Case Nz(Qry_QryType, "NOT Invoice")
Case "NOT Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then Me.Qry_Status.Locked = True
Else Me.Qry_Status.Locked = False

Case "Invoice"
If IsNull(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True Then
Me.Qry_Status.Locked = True Else Me.Qry_Status.Locked = False

End Select


It isn't quite working. If all the fields are empty the "qry_status" combo
is locked but if I enter info into all the fields the combo remains
locked.



John Spencer said:
Again, WHERE are you using this? In a form?

If in a form, try the form's after update event.

OR create the code as a sub that you can call from each control's after
update event and set the value.

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


Thanks!

The first code could do the trick but where do I put it?



:

You gave no indication on where you needed this, what alternative
values
that qry_qryType could have, and whether or not qry_Status had to be
Outstanding before it could be changed to complete.

You can concatenate all the fields together using the + operator
(which
will
propagate nulls) and then check to see if the result is null. If the
result
is null, then at least one of the items you wish to have a value does
not
have a value.

Select Case Nz(qry_qryType, "NOT Invoice")
Case "NOT Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5) = True Then
Me.qryStatus = "Complete"
End if
Case "Invoice"
IF IsNull( Cont_InvName + Qry_ProdType + Qry_CntType +
Qry_CCDesc1
+ SLA_Date3 + SLA_Date4 + SLA_Date5 + SLA_Date6 + SLA_Date7) = True
Then
Me.qryStatus = "Complete"
End if
End Select


In a query you might have to use nested IIF statements

IIF(Cont_InvName + Qry_ProdType + Qry_CntType + Qry_CCDesc1 +
SLA_Date3
+ SLA_Date4 + SLA_Date5 Is Not Null and qry_qrytype = "??Check for
some
value or check for <> Invoice??", "Complete", IIF(Cont_InvName +
Qry_ProdType + Qry_CntType + Qry_CCDesc1 + SLA_Date3 + SLA_Date4 +
SLA_Date5 + SLA_Date6 + SLA_Date7 Is Not Null and qry_qryType
="Invoice","Complete", qry_qryStatus))
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..


Apologies for the repost.

I have a set of fields that require information before "Qry_status"
can
be
changed from "outstanding" to "complete". The added complication is
that
if
the query type is an "invoice" then I need an extra two fields.

What I had originally intended wouldn't work. When it comes to
something
this complicated I don't have any experience so please be *very*
clear.


The list of controls required where [Qry_QryType] is NOT an
"invoice"

Cont_InvName
Qry_ProdType
Qry_QryType
Qry_CntType
Qry_CCDesc1
SLA_Date3
SLA_Date4
SLA_Date5



The list of controls required where the option for [Qry_QryType] is
"invoice"

The same as above plus the following:

SLA_Date6
SLA_Date7
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need help with code... 7
I have a strange data mismatch error 4
HELP! with email code 1
Help with creative thinking needed 7
Need help with flags 0
Help with code to "blank" reports 2
Help with drop down name to autofill address 3
Code Reqd. to generate Loading Slip 3

Top