Question regarding a relationship between 2 tables.

[Sniper-BoOyA-]

Good Morning peeps,

Situation:

I am trying to tweak a database i made a couple of months ago. As you might recall from my earlier posts, i made this database for a laboratory. Everything works fine but theres just one small thing that needs to be "fixed"..

On one of the reports, a query checks and compares the labresults to the standard-requirements. And if it meets the requirements it will show a textbox with a simple text "meets the requirements".

But it also happends that even though the standard-requirements are unknown, a textbox is visable with the text that it does not meet the requirements. That is what i want to change. If there are no standard-requirements available, i dont want anything to be visable.

To fix it :

All standard-requirements are in a linked / online table named "tblasfaltNENEN, which is being used in all the databases. I added a new record in that table, with a checkbox. If there is a requirement set, then the box will be ticked. If there is no requirement, it will not be ticked.

Then theres a table which contains all the general lab information named "labgegevens". Including the names of al the tests in the lab. Including a record named "Asfaltbenamnew" which is set as text, but it looks in table "tblasfaltNENEN" and selects the record "id". Which is the name of a particular sample.

The problem :

I made a query and i want to show pretty much all information of table "Labgegevens", plus i want to show the checkboxes of table "tblasfaltNENEN".

When i try to run the query without a relation between both, i get an error. Which is obviously caused by the fact that there is no relation between both tables.

But ok, i make a relation between

labgegevens.asfaltbenamnew and tblasfaltNENEN.Id, because they are the same. But no luck, get an expression error. Which is obvious too since labgegevens.asfaltbenamnew is set as text, and tblasfaltNENEN.ID is a autonumbering field.

But i cant set labgegevens.asfaltbenamnew to "number" since the name of the samples contains letters..

Anyways, i am struggling to make a relationship between Labgegevens and tblAsfaltNENEN.

Does anyone have any suggestion on how to get this to work.. So i dont have to go to 'Plan B'... ?

 

[PNGBill]

But it also happends that even though the standard-requirements are unknown, a textbox is visable with the text that it does not meet the requirements. That is what i want to change. If there are no standard-requirements available, i dont want anything to be visable.

What do you mean by with the text that it does not meet the ....

Have you tried to use an IIf so text is showing if it meets and " " if it doesn't meet.

Or do you mean the query is showing some data even though it shouldn't be there?

Seems to me you should resolve this issue rather then what appears to be a convoluted "fix"

With a Form, you can use vba code behind the text box so it becomes Invisible if a condition is meet but not so easy with a report - I think.