Can you use a Yex/No field in another field. Totally newbe

[Jackson]

I have two tables one with building information and addresses and the other with the owners and their mailing addresses. I made a query that has the following

First name - Last Name - Building no - Street - City - State - Zip - Mailing Street - Mailing City - Mailing State - Mailing Zip - Owner occupied Y/N check box

I need it to print the owner occupied building names and addresses when occupied and the names and only the mailing address of the ones not occupied. I tried to find the answer by reading and searching for hours on end but gave up. Don't know enough to ask the right questions yet.


Any help will be appreciated.

Also are there any tutors out there?

[CJ_London]

easiest way is to do so in your query.

would be better if you had posted your query, but something like

addressto:iif(owneroccupied,firstname & " " & lastname,"")

[Jackson]

Being new at this can be frustrating, so I really appreciate your help. No sure how to put query on here. Where do I put the iif statement?

[CJ_London]

No sure how to put query on here

1. open query in design view
2. click on sql view, top left of ribbon, or bottom right of access window
3. copy/paste the code into a post
4. highlight pasted code
5. click the code button (the # button)

Where do I put the iif statement?

in a new column in the query window. note it is a bad idea to have spaces in table and field names (or any non alpha number characters, with the exception of the underscore). It is not clear what you have called your fields, so change names to suit.

[Jackson]

Not sure if this is going to work - but figured out how to use print screen. Thanks Ajax for replying, At this stage of the game Access is confusing but I'll get there. Thanks for your help.


Need to print all address but some do not live on site and have another mailing address.

[CJ_London]

so do as I suggested, add an additional column to your query view.

Be aware that most responders will not respond to screenshots of a query window because often there is missing information - so learn to copy and paste the sql. It also makes it easier for the responder to reply - they can change what you have posted and you just need to copy and paste it back again to the sql window. On your image on the ribbon on the left you can see 'View' with a little arrow below that, click on that and select 'SQL'.

Looking at your join, I suspect your table are not constructed properly. You do not link primary keys together - you link a foreign key to a primary key. in this case I suspect that BuildingNoID should be the primary key to tablebuilding and addressID should be a foreign key to tableowner primary key

[Jackson]

I changed the primary key - sorry for the screen shot, thought that was what you needed.
I added the code but get an error "Characters found after SQL statement"


This the code I copied:

SELECT TableOwner.FirstName, TableOwner.LastName, TableBuilding.BuildingNoID, TableBuilding.Street, TableBuilding.City, TableBuilding.TX, TableBuilding.ZipCode, TableBuilding.OwnerOccupied, TableOwner.MStreet, TableOwner.MCity, TableOwner.MState, TableOwner.MZip
FROM TableBuilding INNER JOIN TableOwner ON TableBuilding.AddressID = TableOwner.OwnerID;
addressto:iif(owneroccupied,firstname & " " & lastname,"")

[CJ_London]

thought that was what you needed.

??? where does screenshot come into

1. open query in design view
2. click on sql view, top left of ribbon, or bottom right of access window
3. copy/paste the code into a post
4. highlight pasted code
5. click the code button (the # button)

anyway, you have it in the wrong place, just copy and paste this into your sql window (correct location shown in red), replacing all the existing code

SELECT TableOwner.FirstName, TableOwner.LastName, TableBuilding.BuildingNoID, TableBuilding.Street, TableBuilding.City, TableBuilding.TX, TableBuilding.ZipCode, TableBuilding.OwnerOccupied, TableOwner.MStreet, TableOwner.MCity, TableOwner.MState, TableOwner.MZip, iif(owneroccupied,firstname & " " & lastname,"") as addressto
 FROM TableBuilding INNER JOIN TableOwner ON TableBuilding.AddressID = TableOwner.OwnerID;

now when you go back to the query design window, you will see the extra column.

The design window is just a visual way of creating and viewing sql

[Jackson]

Thanks a million