Iif Statement in query for multiple conditions

[suz65]

Hi

Hoping to get some help on a problem I have been banging my head about.

I have 2 tables: clients_tbl and job_history_tbl

in the clients table I have the following 9 fields and the (field types);

[home_address] (text) [home_address_suburb], (text), [job_location_home] (yes or no)
[work_address] (text), [work_address_suburb] (text) , [job_location_work] (yes or no)
[other_address] (text), [other_address_suburb] (text), [job_location_other] (yes or no).

In the job history table & form, I have a field [job_address]. I need to have this field populated with one of the addresses from the Clients_TBL where the check box has been ticked.

I tried this in the SQL query but it just returns all the addresses for each record. The statement I used was

If [clients_TBL]![job_location_home] = true then
[job_history_TBL]![job_address] = [clients_TBL]![home_address] "&" [clients_TBL]![home_address_suburb]
OR
If [clients_TBL]![job_location_work] = true then
[job_history_TBL]![job_address] = [clients_TBL]![work_address] "&" [clients_TBL]![work_address_suburb]
OR
IF [clients_TBL]![job_location_other] = true then
[job_history_TBL]![job_address] = [clients_TBL]![other_address] "&" [clients_TBL]![other_address_suburb]
ELSE null
end if


I haven't used Access for a very long time and I just cant get my head around this.

I'd really appreciate some help on how to do this or even a better less muddy way...my ever declining brain cells thank you in advance

 

[June7]

That code cannot be used in a query. I don't see how query would even open. That is not even valid VBA code.

Unless the two tables are joined in a query or this is a form/subform arrangement, can't reference clients_tbl fields.

 

[suz65]

That code cannot be used in a query. I don't see how query would even open. That is not even valid VBA code.

Unless the two tables are joined in a query or this is a form/subform arrangement, can't reference clients_tbl fields.

Thank you for your reply, but do you have any suggestions as how to do it then?

 

[CJ_London]

I tried this in the SQL query but it just returns all the addresses for each record. The statement I used was

Suggest show the whole sql, not just this part of it

 

[suz65]

Suggest show the whole sql, not just this part of it

I'm afraid that is the whole lot. clearly I am waaaaaaaay off track.

suggestions???

 

[CJ_London]

suggestions???

OK, so it is not SQL, but VBA, and as June says the code will not work. Our dilemma is you have provided some code that you say works, but we know won't. Which tells us what you have provided is not what worked. So we are unable to be clear what the problem is or what you actually have to work with to provide a solution

describe what you have - a form? report? what is the recordsource? what are the names of the relevant controls. What is the whole VBA to the form/report? what is your actual table structure? - what you have described is incomplete. It also sounds as if it is not constructed properly

Also be clear about whether you are talking about fields in a table or controls on a form or report.

 

[Dreamweaver]

Is the system like the screen below I.E. you have a client which may or maynot be the site details?

 

[suz65]

Okay...sorry I'm obviously not communicating my issue clearly and I apologise and ask for patience please.

I have a few Screen dumps to show each of the Tables & Forms I am working on.

It is a very basic client database that shows each time a job has been performed for a specific client.

In the Client_TBL (table) there are 3 different addresses, ie home, work or other. A form has been created with the exact same fields etc just to give a better user interface. See screen shots 1 & 2 below.

Next to each of the addresses is a tick Y/N box to indicate which address was the site of the job.

On the Job_History_TBL (table), I would like to link back to the Client_TL addresses but only return the address which has the ticked "job_location" box into the "job_Address" field. See screen shots 3 & 4.

I have been trying to create a query that will search the client_TBL fields in each record to return the one selected address.

I hope that I have explained a little better???

Screen shot following;

 

[arnelgp]

comment.
in job history table you need the clientID and not the clientName.
name is common, anyone can have the same name.
but clientID gives him his own identity.

if you have updated the job history table with clientID,
you can add code to the Form's BeforeUpdate event to
update the [job_address] field:

Private Sub Form_BeforeUpdate(Cancel As Integer)
   Dim strAddress As String
   If [job_location_home] Then
      strAddress= [home_address] & " " & [home_address_suburb]
    If
   If [job_location_work] Then
      strAddress= [work_address] & " " & [work_address_suburb]
   End If
   If [job_location_other] Then
      strAddress= [other_address] & " " & [other_address_suburb]
   End If
   If strAddress <> "" Then
      Currentdb.Execute Update [job_history_TBL] Set [job_address]=" & Chr(34) & _
                        strAddress & Chr(34) & _
                        " Where ClientID=" & Me.ClientID
   End If
End Sub

 

[arnelgp]

btw, when you have the ClientID in [job_history_tbl], you don't need the [job_address].
let this info be kept on 1 table, the client table.
job related info, to the history table.

what you need on your second form, job history, is a Query.
a query that you can link both tables by the common ClientID.

you can further reduce your forms to 1. the Client table form, then the job history as Subform.
so it will be easy to see at a glance the info for the client as well as the job history for that client.

 

[Pat Hartman]

1. Since Job location is mutually exclusive, you would use one field with three potential values rather than 3 fields which you would programatically have to restrict so that only one of them contained the true value.
2. In a query, you would use a nested IIF() function.

Select fld1, fld2, fld3, IIf(JobLoc = "Home", [clients_TBL]![home_address] "&" [clients_TBL]![home_address_suburb], IIf(JobLoc = "Work", [clients_TBL]![work_address] "&" [clients_TBL]![work_address_suburb], [clients_TBL]![other_address] "&" [clients_TBL]![other_address_suburb])) As [job_address], fld4 From clients_TBL

It is not clear what you are trying to do but you can use a similar nested IF in a form if you want to save the data in a history table.

 

[suz65]

Thank you for your suggestions, I mucked around with it all day, but clearly I am too stupid because I just couldn’t get it to work. So I have done a bandaid job and taken the actual addresses off the Job History table/form but left in the combo box for Job location, then I have just set up a command button to go to the relevant client in client table / form to check that the correct address is listed.

Maybe on another day I might have enough brain matter to look at it again.

Thank you anyway

 

[JHB]

Else you could post your database with some sample data, zip it because you haven't 10 post yet.