Results 1 to 7 of 7
  1. #1
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99

    Queries and Iff Statements


    Hi Everyone,
    I've been struggling with this for a minute "Lol" . I built this Select-Query that has Six(6) tables and is also the Record-Source for a Report. However I recently added another Table seven(7)tables base on the information my Supervisors want.. good...ok!, Everything again works fine .... BUT If there's no entry into the particular field i.e. the seventh table ...the record will not show up... I want it to filter where isNull and is not Null and the record shows on the report.

    Note this expression
    Code:
     =IIf((IsNull([tblStations]![InTakeStation])) And (IsNull([tblStations]![InTakeStation ])),"","Not Available")
    works but still omits the record that has no available data and when I change it to
    Code:
    =IIf((IsNull([tblStations]![InTakeStation])) And (Is not Null([tblStations]![InTakeStation ])),"","Not Available")
    I get syntax error any Help!

    This code in criteria [Expression Builder] works but where there's no data in the particular field it does not produce into the report ....even though I want to trap this information also?


  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you appear to have a space

    Code:
    [InTakeStation ]
    you are not showing the full sql, so not possible to comment on why the record won't show up

    not clear what you are trying to do

    (IsNull([tblStations]![InTakeStation])) And (IsNull([tblStations]![InTakeStation ]))

    seem to be saying 'if true and true'

    whilst this

    (IsNull([tblStations]![InTakeStation])) And (Is not Null([tblStations]![InTakeStation ]))
    seems to be saying 'if true and false' - how can that be?

  3. #3
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    As you explained it I now fully see the Logic... self taught I like to say this sometimes... but I now discovered something this is why i try not to post too quickly. When I go into joint-properties and switch to option (3) "includes all records from [tbl1]and only those from [tbl2] where joined fields are equal. All the records show up the way i want them to... including the ones in field where data is empty "no data".. I basically want if its empty is says Not Available so that this can also print unto the report

    this is the full sql as before... scrap that I've just edited my post. What will a isEmpty Function look like since if the conditions are only true No false as before i want to say "Not Available"
    Code:
    =IIf(IsEmpty([InTakeStation]),"Not Available")
    this is not working the field are still coming up blank...where the other records show....more less if its empty i want it to say this???
    Do you understand Ajax???

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    So you use another field with the iif() function that shows the field value if entered, or not available if not. NOT the actual field.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not sure of your query - but is empty won't work in a query - it refers to whether variables have been initialised or not - and only relevant in vba, in sql they will be always be initialised by definition

    see this link
    https://docs.microsoft.com/en-us/off...empty-function

    FYI this sort of information is easy to find - just google your question and include vba e.g. 'vba isempty' or 'vba what is a syntax error'

    include vba, or 'access vba' so you don't get links back relating to other languages or apps (although you will frequently get answers relating to excel, but they are usually relevant to your question)

  6. #6
    Jamesy_007 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2020
    Location
    Caribbean
    Posts
    99
    Yes Sir! Welshgasman, while I may had an idea of this way because I've done something similar... was thinking this application is so powerful there must be another way in criteria within the query grid so basically was stressing out for nothing. Created a new field and will just hide the other field when doing reports.

    This is the trick had to create a new field in query
    Code:
    Station: IIf(IsNull([InTakeStation]),"Not Available",[IntakeStation])

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    No ned to 'hide' the field, just do not include it in the Select, and use the other field in it's place.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

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

Similar Threads

  1. Convertion of queries to SQL statements
    By sanal in forum Queries
    Replies: 17
    Last Post: 02-21-2018, 07:09 PM
  2. Replies: 8
    Last Post: 12-31-2015, 12:05 PM
  3. Queries using IIF statements
    By jaarons in forum Queries
    Replies: 2
    Last Post: 12-30-2012, 03:21 AM
  4. IIF Statements and Update Queries.
    By nicknicknick in forum Queries
    Replies: 1
    Last Post: 06-05-2011, 12:08 PM
  5. If statements
    By Ezeecopy in forum Access
    Replies: 0
    Last Post: 03-24-2009, 04:54 AM

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