Results 1 to 4 of 4
  1. #1
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84

    IF Then vba statement

    I need help of writing the following if then



    1. If in qry [gd_rp] below: If scope for a (Unit Number) = "90" and (Kitchen Floor) = "Replace"

    Click image for larger version. 

Name:	Criteria6.PNG 
Views:	16 
Size:	13.8 KB 
ID:	24687

    2. Go to Qry [Floor] read and find the (xmcode) data ex.+APPR4/3P40A

    Click image for larger version. 

Name:	Criteria7.PNG 
Views:	14 
Size:	10.0 KB 
ID:	24688
    3. in qry [combo] below: find the same (xmcode) data in the [combo] qry below and subtract the (qty) number from the [floor] Qry from the (qty) number in [combo] qry and create a new column in [combo] and place that value in the new column
    Click image for larger version. 

Name:	Criteria8.PNG 
Views:	15 
Size:	50.0 KB 
ID:	24689

    End

    Then separately i need same logic to change

    1. If in qry [gd_rp] below: If scope for a (Unit Number) = "FS" and (Kitchen Floor) = "Good"

    Click image for larger version. 

Name:	Criteria6.PNG 
Views:	16 
Size:	13.8 KB 
ID:	24687

    2. Go to Qry [Floor] read and find the (xmcode) data ex.+APPR4/3P40A

    Click image for larger version. 

Name:	Criteria7.PNG 
Views:	14 
Size:	10.0 KB 
ID:	24688
    3. in qry [combo] below: find the same (xmcode) data in the [combo] qry below and ADD the entire row from Qry [Floor] to qry [combo] data by unit
    Click image for larger version. 

Name:	Criteria8.PNG 
Views:	15 
Size:	50.0 KB 
ID:	24689

    End

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    unable to understand your logic - for example where in the data in your first query is the relevant xmcode?

    Also your table design does not look right - door/cable/sink/etc should be in different rows.

    And with regards step 3 - what do you mean by 'create a new column'.

    This sounds more like a job to do in a form.

    Suggest you explain your business and what you are trying to do in simple English (no jargon)

  3. #3
    GCLIFTON is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    84
    there is not a relevant xmcode in the first data. The first data is what is used to deteremine if you are goign to use the 2nd qry or not. There are 3 different qry that are being used. the later 2 are connect by xmcode the first determines whether or not if the later 2 will be needed.

    yes door / cable/ sink/ etc are in different row. Further down they in the table design they have replace or good in there fields. There are actually 50 variable /Headers going across

    step 3 to add a new column--- i can see what you mean on this one. I could just add that column and just have the returning new value drop in that column associatied with the unit

    And what do you me it sounds like a job to do in a form. Never thought about it in that way.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    have a form with subforms - one subform for each query

    then for the current event of the form used in the subform [gd_rp] use your logic to determine the filter or find to use for the form used in the subform for [Floor], etc

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Help with IIf statement
    By RAHILTON76 in forum Access
    Replies: 7
    Last Post: 03-17-2016, 09:43 AM
  2. Replies: 11
    Last Post: 04-29-2015, 01:38 PM
  3. iff or nz statement
    By angie in forum Forms
    Replies: 12
    Last Post: 05-11-2014, 07:29 PM
  4. if statement in sql statement, query builder
    By 54.69.6d.20 in forum Access
    Replies: 4
    Last Post: 09-11-2012, 07:38 AM
  5. Replies: 7
    Last Post: 08-17-2011, 01:49 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums