IIf

[shijuantony2002]

Here is the attachment to the query to give more insight view to the question. I had linked the query 'Form3' to the 'Table'. If you will see, there are two fields i had added, 'Soonest Value Date' and other 'Soonest Value'(which is not linked to the table and contains the expression).

The column which is not linked to the table and has expression in it, is working properly as it gives the output as expected (It identifies if there are any null values in the column [Value Date] and if yes, it returns with text "Soonest Value".

My question is, can i get the same output in the column which is linked to the table. I need the same output as above, but at the column [Soonest Value Date].

I had tried to setup a criteria on the field with linked table with the same expression as mentioned above. IIf(IsNull([Value Date]),"SOONEST VALUE","") but this is not getting the required result.

Thanks in advance for your reply

 

[raskew]

From the Help File -

IIf(expr, truepart, falsepart)

The IIf function syntax has these named arguments:

Part Description

expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Now, how does that compare to your statement?

Bob

 

[CameronM]

I think from your post that you want to return the [Value Date] field if it has a value, otherwise return the words "SOONEST VALUE". the snippet below will do that.

IIf(IsNull([Value Date]),"SOONEST VALUE",[Value Date])

 

[shijuantony2002]

Thanks for your input camron, but this is not what i required.

I require the IIf expression to be linked to the table wherein Soonest Value Date is written. I would like to to scrap the column where i have the parameter written and would like to get those into criteria row under the table "Soonest Value Date". With this way, i will have a direct link to the table "Soonest Value Date".

Also the output i require is that if there are null value in field "Value Date" then the output should be in text "Soonest Value" or else it will show blank. The expression which i placed was correct, but it somehow does not works in criteria row.

Please let me know, if this is the correct expression to be used in the criteria.

 

[CameronM]

You will need to provide details of the

table wherein Soonest Value Date is written

as the database you attached only has one table.

If you need to return the value from that table, you enclose the field name in square brackets in your IIF statement. For example the code below returns the value in the field named Soonest Value Date when Value Date is not null.

IIf(IsNull([Value Date]),"Soonest Value",[Soonest Value Date])

but it somehow does not works in criteria row

The IIF statement can not be used this way for the 'Criteria' row as it will filter out records based on the the result of the IIF statement. You need to use IIF in the 'Field' row.

 

[shijuantony2002]

ok, so it looks like IIf indeed never works on criteria, but i had seen somewhere IIf working on the criteria.

What would be the ways you suggest on getting the text "Soonest Value" on the query?

I had revised the file in the attachment and adding Forms in it. Hope it might work with Forms. Am scratching a lot in this query to work out. Your assistance is of great helpt to me, Camron.

Also please suggest, if there are other code that can be applied other than IIf for this to work out?

 

[Brianwarnock]

You achieved getting the text "Soonest Value" into the query at post1, what you cannot do is update the table except in an update query. Having said that I am at a loss as to what you real aims are.

Brian

 

[shijuantony2002]

Hi Brian,

Am looking the way to update the query under the column "Soonest Value Date" In Post 1, i achieved because that was an independant field not linked to the table. I would like to achieve the result by applying the same parameters or any other, but to the one which is linked to the table "Soonest Value Date".

If its not achievable with table, is it possible to apply the parameters to queries or Forms.

Please refer my 2nd attachement provided earlier.

 

[Brianwarnock]

If the field Soonest Value date was on the Form the option button could run a procedure to update that field, and that would be reflected back in the table as the form is based on the table, but I'm puzzled s to why you want to store this in the table as the null Value date field tells you all you need to know, this is similar to storing a calculated value which good DB design says don't do.

Brian

 

[shijuantony2002]

Hi Brian,

That was a great piece of information for me. Since am new to access and not aware of this segment, so i just created a label of Soonest Value date on the Form as Option button, but actually there was no direct effect of that option effect on table and was just for a display. Instead i kept an IIf command on query which could provide me the result.

If you would be able to provide me the solution on how the option button on forms would work out, that would be of great help for me.

My target is :-

On the Form, if i will click the Option Button of "Soonest Value" the table will be updated on value date column as "Soonest Value" or else if the Value Date field is populated, it will pick up the Value Date field only.

Would be great if you ammend and upload the sample db attached, for me to understand in detailed.

Thanks

shiju

 

[shijuantony2002]

Am just trying to put following code for my option button to work based on the above, please advise what would be wrong here as am getting Run time error when clicked "The value you entered isn't valid for this field"

-------
Private Sub Soonest V/D_Click()
If IsNull(Me.Value_Date) Then
Me.Value_Date = "SVD"
Else
Me.Value_Date = Me.Value_Date & "; " & "SVD"
End If
End Sub

-------

 

[shijuantony2002]

Apologies the code should be this way... but still not working

-------
Private Sub Soonest V/D_Click()
If IsNull(Me.Value_Date) Then
Me.Value_Date = "SVD"
Else
Me.Value_Date = Me.Value_Date
End If
End Sub

 

[CameronM]

The error message "The value you entered isn't valid for this field" points to your problem. You are trying to store the string "SVD" in a field that has a data type for Date/Time?

If you want to store a value in the field [Soonest Value Date] then you need to modify your code to;

If IsNull(Me.[Value Date]) Then
Me.[Soonest Value Date] = "SVD"
Else
Me.[Soonest Value Date] = Me.Value_Date
End If

 

[shijuantony2002]

Perfect!! It worked.. thats what i was looking for...

I had deleted one line of the code after, Else, as i do not want soonest value date = value date.

If IsNull(Me.[Value Date]) Then
Me.[Soonest Value Date] = "SVD"
Else
End If


However, i would like to add further to this code, saying that, if [soonest value date] is clicked then [value date] cell is blocked, OR if [Value Date] is typed then [Soonest Value Date] cell is blocked.

Please let me know if this could be done.

Thanks for all your response

 

[Brianwarnock]

Hi
Didn't intentionally ignore you, a walking day yesterday so didn't have my computer to hand , anyway you have made good progress, Yes you can do what you want. Me.Fieldname.Locked=True/False will enable disable a field. THe intellisense propts will direct you through it. Remember to set the contro; to the opposite condition when appropriate ie if you are not setting Lock=True then set Lock=False.

Brian

 

[CameronM]

Reiterating Brian's comments and using your previous code, I have assumed you have two(2) textbox controls on you form. One named "Soonest Value Date" and "Value Date". You can use the Value Date LostFocus event or After_Update event to lock the Soonest Value Date field.

Private Sub Soonest_Value_Date_Click()
    'if the Value Date field is null then insert SVD into Soonest Value
    If IsNull(Me.[value date]) Then
        Me.[Soonest Value Date] = "SVD"
        Me.[value date].Locked = False
    End If
End Sub
Private Sub Value_Date_LostFocus()
    'test that the Value Date field is null and lock the Soonest Value
    If IsNull(Me.[value date]) Then
        Me.[Soonest Value Date].Locked = False
    Else
        'unlock the Soonest Value field since Value Date is null
        Me.[Soonest Value Date].Locked = False
    End If
End Sub

 

[shijuantony2002]

yes..thanks for appreciating... its alwayz the interest which drive you to get things what u want....
and what would i have done with you guyz!!... thanks you'll have made this worked.....

this is my code which worked..

Private Sub Option65_Click()
If IsNull(Me.[Value Date]) Then
Me.[Soonest Value Date] = "SVD"
Me.Value_Date.Locked = True
Else
End If
End Sub

but theres something missing in here i guess....when i click the option button, it does block the [Value Date] field and gives the output...but then it should also go reverse way....when i type on [Value Date] field the option button aso should get locked. Also there should be some coding or events wherein it should prompt with msg box, if either of two field is not been clicked.

I know it should be possible by scratching some code, but am a finance guy, too hard to scribble by myself. Need someone to complete the coding.

Thanks Brian, Camron, and all the experts for your support to the new bies.

 

[shijuantony2002]

OOPS!!! Looks like Cameron has provided me with some detail coding at the same time when i scrapped my text...

I will check and get back to you... Looks like you gave what i want,

thanks for ur effort!!

 

[shijuantony2002]

Hi Cameron

Your further coding is not working, especially when its false. I had tested with true and its working, but with just the option button event.

Private Sub Option65_Click()
If IsNull(Me.[Value Date]) Then
Me.[Soonest Value Date] = "SVD"
Me.[Value Date].Locked = True
Else
End If
End Sub

Had pasted your coding, but still not working, if value date is set to lost focus or after update event. Also had tried with click event, but in vain.

As said above, i also want to give an option to focus either of two, if the two is not populated, it should not allow to enter and give a relavant messange box.

 

[Brianwarnock]

The code I thought that you required, probably in the form_current event is like this

'if the Value Date field is null then insert SVD into Soonest Value
' and lock Value Date field
    If IsNull(Me.[value date]) Then
        Me.[value date].Locked = True
        Me.Soonest_Value_Date.Locked = False
        Me.Soonest_Value_Date = "SVD"
    Else
        Me.Soonest_Value_Date.Locked = True
        Me.[value date].Locked = False
    End If

However that would not work for new records, which you enter on the same form, I had trouble with your form as it has no navigation buttons and the originator field is missing.

Can I attempt to clarify your requirements.
For existing records
1 If Date Value is null then "SVD" in Soonest_Date_Value and Lock Date Value
2 If Date Value is not null Lock Soonest_Date_Value

For a new record allow both fields initially but allow entry into only one.
OR
Do you want both locked and a message box?

I'm trying to understand this

i also want to give an option to focus either of two, if the two is not populated, it should not allow to enter and give a relavant messange box.

I think I am correct in that English is not your first language, but do not worry we will understand if we try.