Calculating the information for a text field on a form from a query

[guinness]

Thanks for getting back to me vbaInet.

Use Kevin McBride as an example. So Trainer_Name kevin McBride and Start_Date 20/08/2014 should show a last holiday date of 15/08/2014. Instead it shows the first holiday record 01/01/2014.

Does this help?

 

[vbaInet]

Yep, better!

Here's what you do:
1. Create a new query, open it in SQL view and paste this:

PARAMETERS Forms!Resourcing!Trainer_Name Text ( 255 ), Forms!Resourcing!Start_Date DateTime;
SELECT [Hours Holiday_P1].Start_Date, [Hours Holiday_P1].Payroll_Number, [Hours Holiday_P1].Trainer_Name, [Hours Holiday_P1].Duration, [Hours Holiday_P1].Hours
FROM [Hours Holiday_P1]
ORDER BY [Hours Holiday_P1].Start_Date DESC;

2. Save and close this query and use it in your DLookup instead.

 

[guinness]

I'd love to know what you did there

The parameters part at the top seems unnecessary as those reflect the parameters set on the source table. I also notice that you set the query to sort descending. Is that what made the difference?

Anyway thank you very much for that. Your help and patience is much appreciated

 

[vbaInet]

If the parameters were unnecessary I would have not included it in the first place. Take both out and see for yourself.

 

[guinness]

I know far better than to doubt you. I've booked myself on a two day vba course in September and am purchasing a book entiirely on the subject. I can just about get by with queries and can adapt sql code and I don't struggle with forms but vba just has me beat.

Thanks again for all your help.

 

[vbaInet]

The parameters part at the top seems unnecessary as those reflect the parameters set on the source table.

A better approach would have been to ask what parameters are for.

Parameters are used to declare the data type of the parameters being passed to the query.
In the DLookup() you're passing two parameters, i.e. "Forms!FormName!FieldName", and the query needs to know what data type those parameters are. It can decipher what data type the date parameter is because you set it up a Format of General Date (remember when I asked about this?) but it doesn't know what data type Trainer Name is because it wasn't set, hence, you explicitly declare it in the Parameters list. Even if you have a Format set I still like to set up parameters to be double sure.