Go Back   Access World Forums > Microsoft Access Discussion > Queries

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-23-2019, 11:39 PM   #1
suz65
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
suz65 is on a distinguished road
Iif Statement in query for multiple conditions

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

suz65 is offline   Reply With Quote
Old 08-24-2019, 12:18 AM   #2
June7
AWF VIP
 
June7's Avatar
 
Join Date: Mar 2014
Location: The Great Land
Posts: 2,264
Thanks: 0
Thanked 532 Times in 528 Posts
June7 will become famous soon enough June7 will become famous soon enough
Re: Iif Statement in query for multiple conditions

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.
__________________
Attach File Manager is below Advanced editor window, click Go Advanced below Quick Reply window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG!
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
June7 is offline   Reply With Quote
Old 08-24-2019, 12:25 AM   #3
suz65
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
suz65 is on a distinguished road
Re: Iif Statement in query for multiple conditions

Quote:
Originally Posted by June7 View Post
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?

suz65 is offline   Reply With Quote
Old 08-24-2019, 01:21 AM   #4
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,224
Thanks: 40
Thanked 3,641 Times in 3,512 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Iif Statement in query for multiple conditions

Quote:
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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-24-2019, 01:34 AM   #5
suz65
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
suz65 is on a distinguished road
Re: Iif Statement in query for multiple conditions

Quote:
Originally Posted by CJ_London View Post
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???
suz65 is offline   Reply With Quote
Old 08-24-2019, 01:48 AM   #6
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 11,224
Thanks: 40
Thanked 3,641 Times in 3,512 Posts
CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light CJ_London is a glorious beacon of light
Re: Iif Statement in query for multiple conditions

Quote:
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.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 08-24-2019, 02:20 AM   #7
MickJav
Newly Registered User
 
Join Date: Nov 2005
Location: Margate
Posts: 928
Thanks: 64
Thanked 48 Times in 41 Posts
MickJav will become famous soon enough
Re: Iif Statement in query for multiple conditions

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


Attached Images
File Type: png 2019-08-24.png (65.1 KB, 60 views)

__________________
All open code examples and free projects are only available from:

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

MickJav is offline   Reply With Quote
Old 08-24-2019, 02:57 AM   #8
suz65
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
suz65 is on a distinguished road
Re: Iif Statement in query for multiple conditions

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;
Attached Images
File Type: jpg Client_TBL.jpg (93.5 KB, 10 views)
File Type: jpg Client_Form.jpg (100.0 KB, 8 views)
File Type: jpg Job_History_TBL.jpg (92.6 KB, 7 views)
File Type: jpg Job_History_Form.jpg (95.9 KB, 8 views)
suz65 is offline   Reply With Quote
Old 08-24-2019, 03:27 AM   #9
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,359
Thanks: 67
Thanked 2,683 Times in 2,569 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Iif Statement in query for multiple conditions

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:
Code:
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
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-24-2019, 03:50 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,359
Thanks: 67
Thanked 2,683 Times in 2,569 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Iif Statement in query for multiple conditions

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.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 08-24-2019, 08:09 AM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,112
Thanks: 15
Thanked 1,566 Times in 1,489 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Iif Statement in query for multiple conditions

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 08-24-2019, 11:13 PM   #12
suz65
Newly Registered User
 
Join Date: Aug 2019
Posts: 5
Thanks: 0
Thanked 0 Times in 0 Posts
suz65 is on a distinguished road
Re: Iif Statement in query for multiple conditions

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
suz65 is offline   Reply With Quote
Old 08-25-2019, 12:08 AM   #13
JHB
Have been here a while
 
Join Date: Jun 2012
Location: In the south of Denmark (Jutland), near the German border.
Posts: 7,720
Thanks: 3
Thanked 2,081 Times in 2,036 Posts
JHB has a spectacular aura about JHB has a spectacular aura about
Re: Iif Statement in query for multiple conditions

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

__________________
If the above post has helped you, why not take the time to say thank you, by pressing the "Thumbs up."
JHB is offline   Reply With Quote
Reply

Tags
iif statement , multiple conditions

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
IFF Statement with Multiple conditions cacolon Forms 5 07-02-2017 04:36 PM
IF statement having multiple conditions JQasd Modules & VBA 9 06-22-2016 05:29 AM
If then else Statement with multiple conditions kirsco Modules & VBA 10 07-27-2011 08:03 AM
IIf statement with multiple conditions Steve_T Queries 9 09-10-2008 11:09 AM
multiple conditions in if statement Happy YN Forms 3 05-30-2002 07:37 PM




All times are GMT -8. The time now is 08:34 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World