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

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

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

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.

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.

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?

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

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.

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

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

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

-------

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

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

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

Hi
Didn't intentionally ignore you, a walking day yesterday so didn't have my computer to hand :D, 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

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

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!!:confused:... 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.

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!!

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.

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.

yes english is indeed not my first language.. and apologize for the confusion.

the code provided above is not working in normal scenario too as its showing compile error.


To clarify...

The form is for entering new record only. So when any one goes into the form, it will be for entering new records. In that sense, what u said the clarification for new records is rite.

My requirements :-

- I want to allow both fields initially while entering new records, but if i clicked on soonest value date button, then automatically the value date field should get frozen, and the reverse way,
- if value date field is typed then the soonest value date button is frozen. - And, if both the fields are not actioned, then the new records will not be allowed and a message box will be displayed to either click on the soonest value date field or type on value date field.

Also please ignore the originator field as it has no relavance to these two fields where i am working on. I just gave a sample data in public as i guess other displays are not important as what i wanted to achieve is just between value date and soonest value date which are linked to table.

Please let me know, if this clarifies to provide me with the code. Thanks

Hi again,

I have modified and attached a copy of you database that has code that will hopefully achieve what you require.

Firstly, when the form opens, use the On_Current event to enable all the required fields.


Private Sub Form_Current()
'enable all fields required
Me.Option62.Locked = False
Me.[Value Date].Locked = False
End Sub


Next, if the user enters something into the Value Date text field, then lock the option button using the On_Lost_Focus event.

Private Sub Value_Date_LostFocus()
'test that the Value Date field is null and unlock the Soonest Value
If IsNull(Me.[Value Date]) Then
Me.Option62.Locked = False
Else
'lock the Soonest Value field since Value Date is not null
Me.Option62.Locked = True
End If
End Sub

Lastly, if the user toggles the option button and there is the Value Date field is null, then lock the Value Date text box.


Private Sub Option62_Click()
'first text that the option button is ticked
If Me.[Option62] = -1 And IsNull(Me.[Value Date]) Then
'if the Value Date field is null then insert SVD into Soonest Value
Me.[Soonest Value Date] = "SVD"
Me.[Value Date].Locked = True
Else
'enable Value Date textbox if the user has un-ticked the option button
Me.[Value Date].Locked = False
End If
End Sub

In addition to CameronM 's code in the Form before update event you will need

If IsNull(Value Date]) and isnull(Soonest Date Value) Then

Msgbox This is to output a message to ask for an entry to be made
Read up on message boxes, sorry no time to code and test today.

end if


Brian

Hi Camron / Brian,

Thanks for your efford. This was an ultimate solution to my problem.

I was pasting these codes to my original mdb, however, i got stuck in one of the form.

There was no problem when the code was pasted when the field was named [Value Date], but when i changed the field to [Value Date fx Buy] and accordingly when i changed the below code as below, it showed the error : Compile Error : Method or data member not found

Private Sub Form_Current()
'enable all fields required
Me.[Soonest VD].Locked = False
Me.[Value Date fx Buy].Locked = False
End Sub

Compile Error : Method or data member not found

I had checked all the lines and it also shows up in the List box of the Form, then why it happens when i changed the field from [Value Date] to [Value Date fx Buy] then it shows up with the error.


Thanks

Please ignore my above scrap. I got where i was wrong.

Thanks again.

Hey guyz, my above problem is solved.

This is a fresh query but am continuing in the same thread as the problem is relating to the same but here there is a little twist.

Problem :-

In my above case my form was linked with a table where i had all the required field and against which the code provided by you works perfectly.

But in this case again, my form is linked with a table but at one line i need the code to say that if the field [Conv Curr if other than base] is null then pick up from the query field which is located at Queries -- A(Query name) -- B (Field Name) and input the value of the same field name in the query or else input the field [Conv Curr if other than base].

I had ammended and had framed this code. I know this should not be the code for pulling out queries (marked in bold in the code) but since you guyz had framed the code, am sure you could provide me with the solution again for this.


Private Sub Option65_Click()
If Me.[Option65] = -1 And IsNull(Me.[Conv Curr if other than base]) Then
'if the Conv Curr if other than base field is null then insert query field B as B
Me.[queries]![A]![B] = Me.[queries]![A]![B]
Me.[Conv Curr if other than base].Locked = True
Else
Me.[Conv Curr if other than base].Locked = False
End If
End Sub

Private Sub Conv_Curr_if_other_than_base_LostFocus()
If IsNull(Me.[Conv Curr if other than base]) Then
Me.[queries]![A]![B].Locked = False
Else
Me.[queries]![A]![B].Locked = True
End If
End Sub

Please let me know if you are not clear and i will provide you with a sample db

I was just closing when I spotted this so without much thought - can you not base the form on a query which is the result of joining the table and query mentioned?
else lookup Dlookup

Brian

i need the base as table. just wanted that query line to be on the code to pickup.

I dont know how lookup, dlookup works on code. Can you please provide me with the complete code in line with sample code provided below.

Thanks.

What does your query return? You do not explain how the query knows which records to return, or does it only return one row of records?

As Brian suggested, have a look at the Access help file for DLookup, it will tell you exactly how to set up this code. It also has some great examples to help you. After all how can you learn if we do all the work for you.

As a hint, the syntax for your example would be Me.textbox = Dlookup("FieldName", "Name of Your Query", "Criteria Used to get the right record from the query")

Thanks Cameron,

I did tried in figuring out myself, but somehow it ends up with an error.

The form is for making new entry, so necessarily i have to link it with table.

I had attached the db for your review.

what i require is when i click in Option65 button, and if [Conv Curr if other than base] is null then it will populate field [Base Curr] of the form by looking up the table [Fund Information] in field [Base Currency] and matching with field [Fund Number] against field [Fund Number] of the current Form.

What would be wrong in this code?


Private Sub Form_Current()
Me.[Option65].Locked = False
Me.[Conv Curr if other than base].Locked = False
End Sub
Private Sub Option65_BeforeUpdate(Cancel As Integer)
If Me.[Option65] = -1 And IsNull(Me.[Conv Curr if other than base]) Then
[Base Curr] = DLookup("[Base Currency]", "[Fund Information]", "[Fund Number] = " & [Fund Number])
Me.[Conv Curr if other than base].Locked = True
Else
Me.[Conv Curr if other than base].Locked = False
End If
End Sub
Private Sub Conv_Curr_if_other_than_base_LostFocus()
If IsNull(Me.[Conv Curr if other than base]) Then
Me.[Option65].Locked = False
Else
Me.[Option65].Locked = True
End If
End Sub

No DB attached.
A quick scan of the code noted 2 things
I think it should be Me.[bass curr]

and help never shows [] round the Domain (eg table) name and i have never used them, however i also never have spaces in names.

Brian

What error do you receive?

Put a breakpoint in your code to make sure the value of [Fund Number] is being sent in the DLookup. Also are you sure [Fund Number] is a number, not text?

The other point I need to make, which I know Brian made earier, is that you need to stop naming the controls (textboxes) on you form the same as the fields in your table. How can we tell that [Base Curr] refers to a textbox on your form or to a field in your table?

Brian, had tested already with Me.[] but did not work. I guess [] is used when i have spaces in text of table or field. Does it still not work even if give brackets? I had tested without space and without [] but no output still!! Dlookup code seems to be perfect but didnt know whats more required.

Cameron, i had differentiated my table field names of [Fund Number] to identify clearly. Fund Number is a text.

Had forgot to attach the DB last time. This time i do.

Thanks

I have corrected all of the field names, it is useful to let ACCESS intellisense show you the possible names, ie after typing say me. a drop down list of all possible entries apears , as you type it moves, try it.

I have added Base_curr to the form, and I have corrected the Dlookup.

and changed the event to the double click

I list the code below for you to put into your Db, remember to amend your form

Brian

Private Sub Option65_DblClick(Cancel As Integer)
strdq = Chr$(34)
If Me.Option65 = -1 And IsNull(Me.Conv_Curr_if_other_than_base) Then
Me.Base_Curr = DLookup("[Base Currency]", "[Fund Information]", "[Fund] = " & strdq & Me.Fund_Number & strdq)
Me.Conv_Curr_if_other_than_base.Locked = True
Else
Me.Conv_Curr_if_other_than_base.Locked = False
End If
End Sub

thanks brian,

somehow the intellisence is not working here. I had ammended all the fields as required.

Had also chenged the Query field [Base_curr] but somehow i guess its ignoring the field.

Any idea what would be the problem!

Attached the ammended DB again.

It works ok . the form your new version opens with has USD in the conv_curr etc field therefore does not do the Dlookup
Also the form goes not contain the field Bass_Curr

Brian

the form does not require [Base_Curr] field here. I want the Option65 click to identify that if the table [Conv_Curr_if_other_than_base] is left blank then it should automatically identify the [Base Currency] field which is in [Fund Information] table and by matching up by lookup with the field [Fund] of [Fund Information] table with those of Form Table [MT5BY - Offbook Buy].

This will ensure that when i input text under field [Conv_Curr_if_other_than_base] in the form will remain the same and field [Base_Curr] will goes blank, but when i click Option65 button it will pull the data from table [Fund Information] table against field [Base Currency] and leave field [Conv_Curr_if_other_than_base] as blank.

When i double click [Base_Curr] it pops with Comile Error! Method of Data member not found. It still dont identify field [Base_Curr] at intellisence when i type Me. Can't we reduce it to just a Click instead of double click?

Also am confused what does strdq = Chr$(34) do?

Sorry for too much of question, but just looking to end this problem.

Thanks for your continuous feedback.

Also am confused what does strdq = Chr$(34) do?

Thanks for your continuous feedback.

I will answer this bit because I understand it.

Fund_Number is actually text, therefore it needs to be in "" "", Chr$(34) is " and I believe that what I did is syntactically the easiest way to achieve what is desired.

Brian

Sorry forgot to say yes you can use the single click event, I used the double out of habit with other controls, a single click is sensible here.

Brian

thanks brian, but am still stuck with my main query...

I guess the main problem should be that intellisense is not identifying the object of the list box. The field [Base_Curr] is not been identified and so dlookup might not be working as the object of data member is not found.

Any way out?

I don't know what your problems with intellisense are but they do not influence the code, just make it easier to get correct.
The DLookup works, and the result appears in the undefined Variant Base_Curr , I do not understand what you want to do with this, I thought that you wanted to populate the Form, in which case put the control on the form and then change the DLookup to Me.Base_Curr= DLookup,,,etc.

I am not aware that you can populate anything other than a variant defined in the code or a control on the Form.

Brian

thanks brian, i figured out, why intellisense was not working. To refresh the intellisense i removed the link from list box and reloaded, and it worked.