Calculating the information for a text field on a form from a query (1 Viewer)

[guinness]

The date before. It may sound strange but I'm looking for the period of time between when last on holiday and the thing being resourced. It's a fair point though because when I go to do the same thing with End_Date I probably will want to recognise that the event being booked is actually a holiday.

Does that make any sense outside of my head?

 

[vbaInet]

If it's the date before then your code is fine.

Now it's time for DateDiff()

 

[guinness]

Should I pour a large drink first or is this something I might actually cope with?

Also I thought I needed to create variables and reference them hence the dims I wrote but your code doesn't seem to really need them. I kind of got that bit from a book but maybe I've missed the point. Shame. I was proud of them

 

[vbaInet]

If it makes you happy Dim a Variant variable and set it to Null. It won't do anything for your code but it may be a good ego booster.

To tackle to DateDiff(), you already know what value is returned from the DLookup() so instead of trying to place the DLookup inside the DateDiff, enter the date manually to get it working, then incorporate the DLookup later.

 

[guinness]

You're getting ahead of me again It isn't hard to do.

My thinking was if I went back to plan A where instead of a message box that date was populated in the Last Hol text box then I could reference that text box and the Start_Date above when attempting to define the DateDiff.

Am I off on the wrong track again?

 

[guinness]

So in sequence:

Enter Start_Date produces Last_Hol date
Difference between the two creates number of days since Holiday which can have it's very own text box.

Then enter end date (Provided it's not a holiday)
Look at next holiday booked date (If there is one)
Work out the days between last Holiday and next available date for a holiday

 

[vbaInet]

The function and Msgbox is there for testing and that's what you're still doing. Setting the value to the textbox can come later.

 

[guinness]

Ok I know better than to doubt you.

So what do you want me to do next?

 

[vbaInet]

So what do you want me to do next?

Tell the wife that you're leaving her for Access?

Work on the DateDiff()

 

[guinness]

Will do (possibly on both counts but I'd have to be better at access first) but she's about due home now so I might have to get to this either after 11pm or in the morning

 

[vbaInet]

...possibly on both counts but I'd have to be better at access first...

Don't worry, you'll work it out after marriage.

 

[guinness]

Come in Houston we appear to have a problem

Hi vbaInet. I'm testing that date function at the moment before I attempt datediff and I'm afraid it has issues.

I've attached a screenshot to give an example. I'm trying to book a trainer for 18/08/2014. His most recent holiday based on that date was 08/08/2014. For some reason it's finding a holiday from 29/07.

The Query Holiday Hours_P1 has the following sql

SELECT Resourcing.Start_Date, Resourcing.Trainer_Name, Resourcing.Duration, Time.Hours
FROM (Resourcing INNER JOIN Employees ON Resourcing.Trainer_Name = Employees.Trainer_Name) INNER JOIN [Time] ON Resourcing.Duration = Time.Time
WHERE (((Resourcing.Activity) Like "Holiday*"))
ORDER BY Resourcing.Start_Date;

I could understand if it was finding the first date, the last date or the date that we're looking for but this just seems to be a random date.

Any ideas?????

 

[vbaInet]

How is the code run? On a click event of a button?

 

[guinness]

Hi vbaInet

It runs automatically when you tab out of the field

Private Sub Start_Date_AfterUpdate()
LastHol
End Sub

 

[vbaInet]

It shouldn't make much of a difference but just to be sure run it on the Click event of a button. Also check (using another Msgbox) that the date used is the correct date.

 

[vbaInet]

Also check that the trainer name is the correct one used. And I've mentioned a few times already you should be using the ID not the Name.

 

[guinness]

Hi vbaInet

Sorry for the time in replying. I was away from my desk

I used a button as suggested but am now getting 02/01/2014 as the most recent date. I appreciate that it would be possible to have two trainers with the same name however the database is restricted to using the names from the employee table in order to prevent any inadvertant changes or miss spelling

 

[vbaInet]

I used a button as suggested but am now getting 02/01/2014 as the most recent date.

Like I mentioned in my last posts, you also need to check the actual values from the textboxes using a separate Msgbox.

I appreciate that it would be possible to have two trainers with the same name however the database is restricted to using the names from the employee table in order to prevent any inadvertant changes or miss spelling

How does this affect using the ID? You're dealing with a relational database here not a flat file.

 

[guinness]

Like I mentioned in my last posts, you also need to check the actual values from the textboxes using a separate Msgbox.

The second of the two message boxes didn't work. It kept comming up invalid use of Me. I thought it was just an alternative to the first so wasn't worried.

How does this affect using the ID? You're dealing with a relational database here not a flat file.

I haven't used ID in either my form or my query so would then have to either add it to both or reference the employees table as well as both the form and query. By making trainer Name a full name I've avoided duplicates so far and If we do get two John Smiths I can just use a digit to separate them. I'm much more concerned as to why I can't get the most recent date based on the date entered in the form.

 

[vbaInet]

You don't always have to put functions in a Module. If the function relates to the form, put it in the form. We did this with your other problem.

In this case the ID is relevant. When you're searching for duplicates you use the full name and any other necessary fields, when you're searching for a particular record you use the identifier which in this case is the ID. Makes sense? And yes it will need to be added to the query, it doesn't need to be present on the form, just the Record Source.