Vlookup Function in Microsoft Access 2007

Question

Hi,

The followings are the doubts in my access.

Question 1:

1a) Under the Main Switchboard -> Daily Deployment Report -> once click the different name, I would like it to auto lookup and display the "ROUTE", "DESIGNATION", "NIRC", "EMP_NO", "COST CENTRE" and "OD GP" which had pre entry from Main Switchboard -> Register Name.

1b)  If the "COST CENTRE" was changed thru the "Daily employment Report" -> I would like it to give me a prompt to email a person to notify that its was being changed

Question 2:

2. Under the Main Switchboard -> Select - Date & Designation -> once enter 1/5 (Date) & Driver (etc) ->Click Print - Daily Deployment Report -> It would display the report. Under the "ROUTE", if it is "XXP" is chosen, under "FOT (hrs)", I would like it to auto display 2.67. And if it is "XXR" is chosen, under "FOT (hrs)", I would like it to auto display 2.5.

Question 3:

3a) Under the Main Switchboard -> Select - Date & Designation -> once enter 1/5 (Date) & Driver (etc) ->Click Print - Daily Deployment Report -> It would display the report. On the top left hand corner of the report, I would like to manually key in the "A" or "B", or "C" up to "G" for the OFF GROUP for the particular day. Then under the report, it should auto display "0" under the "FOT (hrs)" & "RDN" under "Status" column.

3b) Then under "Remarks 2" should auto prompt to key in the name [row A].

3c) If once this name is chosen from the drop down list under "Remarks 2", under the "Name" column from this particular date (etc 1/5), the "REMARKS 1" should be displaying the "ROUTE" figure which I changed from the drop down list under "Remarks 2" from the previous [row A].

Question 4:

4. Under the Main Switchboard -> Select - Date & Designation -> once enter 1/5 (Date) & Driver (etc) ->Click Print - Daily Deployment Report -> It would display the report. Under "ROUTE", if the column as long as contain "XXXX SPARE", under "NAME" column required to sort in ascending order.

Thanks for the assisting.

Answer 1

Remember we can't see what you did. So you need to tell us exactly what code or expression you used, where you put it and what you see.  I don't understand what you mean by "result of the column display blank"

Answer 2

I choose the Column Property method.

The result of the column display blank. Can you advice?

Thanks

Answer 3

Where did you look? The URL of my blog is in my signature.  When you get there on the right is a list of Recent Entries, the blog I referred to is near the bottom.

Answer 4

I will go step by step.

Can you spell out for question 1? I can't find from your blog entry on Displaying Data from Related Tables.

Answer 5

Question 1. See my blog entry on Displaying Data from Related Tables.

Question 2. Use an IIF function in the query behind the report to chose the values. Note are XXP and XXR the only choices for Route?

Question 3. a) Again, this can be done in the query. Have a control on the form where you can key in the letter, then use an expression in your query to set the FOT and Status.

b) This is done on your form.

c) Again this is done in your query. You can read the value in any control on an open form using the syntax:

Forms!formname!Controlname

Question 4. This is a bit more complex. In the ON Open event of the report, you need to read the value for Route from the form, and change the Order By property accordingly.

Not sure what all this has to do with VLookup, but there is no Vlookup function in Access. The closest is the DLookup function which can look up a value in a table or query based on specific criteria.