VBA or query

[swarv]

Hi all,

In the attached is a quick holiday database.
I am just stuck on why the report side of it wont pick up the correct year.
If you open the form and then click on holiday detail report it shows all the holiday, but it should only show for the year specified in the top combo box.
Can somebody please shed some light on why this is doing this please.
Thanks
Martin

 

[WayneRyan]

Swarv,

Change your criteria to:

=[Forms]![HolidayMain]![ComboYear]

hth,
Wayne

 

[swarv]

Hi,

I have changed the query that runs to report to:

SELECT BaseData.FirstName, BaseData.LastName, BaseData.Title, BaseData.Department, Entitlement.Yearfld, Entitlement.Entitlementfld, BaseData.EmpTerm, HolidayBookings.DateBooked, HolidayBookings.HalfDay, IIf([Halfday] Like 0,"1","0.5") AS CountD
FROM (BaseData INNER JOIN HolidayBookings ON BaseData.ID = HolidayBookings.ID) INNER JOIN Entitlement ON BaseData.ID = Entitlement.Id
WHERE (((Entitlement.Yearfld)=[Forms]![HolidayMain]![ComboYear]));

but it still shows 2009 and 2010 in the holiday detail report and not just one year.

Is this where I should have changed the code?

Thanks

Martin

 

[swarv]

Just wondered if anybody else had any ideas?

Thanks

Martin

 

[speakers_86]

Can you post your db?

 

[engineer]

your title is " VBA or query "
if it refer to which of them response the error
the answer is the query.

your error in the query and relationship
in fact I tried to understand the relation between tables but I can't know
by trial and error
the solution is :

go to the query "Qry_Rpt_02" in the design view
and go to the field "Yearfld" and change the table from "Entitlement" to "HolidayBookings"
and tell me with the results

 

[swarv]

Hi,

I have tried your solution and the results seem to be the same. It still shows 2008 holiday bookings while searching through the 2009 data.

Any other ideas?

Thanks

Martin

 

[speakers_86]

Can you post your db?

Duh!


Well sir, the problem is that your relationships are, um, not there. In the query, you created relationships between the primary keys of the three tables. Unfortunately, this is not how it works. Look into foreign keys. Try google.

If I were you, I would stop developing this db until you figure out what the structure of your db should be. This is too bad, as I see you have put some work into this already. If you can describe what your are trying to accomplish, perhaps someone here will feel nice enough to show you what your tables and relationships should look like.