IIF Help

[generaluser]

I have the following two tables:
Week table with the following fields
Date Week No.
1/3/02 1
1/4/02 1
1/10/02 2
1/11/02 2 etc.

I have an order table with the following fields:
Cust # Order# Date Or'd Week Ord Order Compl Week comp1
11 001 1/3/02 1/15/02

I'm entering order data through a form. I want to the week ordered and week compl to automatically put in the week no once I enter the date in the date or'd and Order Compl fields

Is it possible? Would I do an IIf statement? Basically, after I type the Date Or'd in the form the Week Ord field should automatically pop in "1" and so forth. Thanks in advance for the help

 

[jitter]

I would think you could use the DLookup function:

Me![Week Ord].Value = DLookup("[Week No.]", "[Week Table]","[Date] = #' & Me!Date Or'd.Value & "#&quot

You would need to change it to match your setup more closely.

Basically you would just use this in the afterUpdate event of the dateord control.

Anyway, this might help get you in the right direction.

....

 

[generaluser]

Thanks for the suggestion. But help me to define certain things:

What is the "Me" in Me![Week Ord]
What is the "Value" in [Week Ord].Value

What is the "#" in "[Date] = #'&Me!Date Or'd.Value & "#" )

Thanks

 

[jitter]

Me is just a reference to the current form. If I have a form called frmContacts and on that form I have a text box control called FirstName.

I could place the following code in the load event of the form to display the value in that text box in a message box.

Private Sub Form_Load()
MsgBox Me![FirstName].Value
End Sub

Basically Value is just a property of a control. In this case it is the value contained in the text box. You can also grab what is in a text box by using the .Text property. The thing about that is that the control needs to have focus or it will throw an error. That is where .Value comes in. You can use .Value property to return what is in a text box without having to shift focus to the text box.

You will find that Access has default properties that are used if you don't specifically reference a control. In the case of a text box .Value is the default so you don't have to specifically tell it but I do it out of habit.

So...

Me![FirstName] = "Hello"

Is the same as

frmContact![FirstName].Value = "Hello"

Anyway, I am not the best explainer so hope this helps.

 

[jitter]

Oh yeah, and Access likes date types to be surrounded by the pound sign hence the #.

 

[generaluser]

I tried it and it said that Access couldnt find the Macro Me!text52

(text52 is the textbox I'm using to store the week number)

Question,
If I was to put a field in my Complaint table titled Week Or'd, will it automatically fill the field in the table as well as on the form? Let me know.