Results 1 to 14 of 14
  1. #1
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20

    Conditional statement applied to entire column?

    Hi Guys,
    Any ideas how I can use a conditional statement to search an entire column in a table?

    For example:


    I have a table in a form (subform), and I want to check one of the columns in this table to ensure that no values in this column are equal to a certain value from the main form.

    Currently my macro uses an if statement, but this if statement will only check the first record in this table, it won't go through the entire column of records for this field.

    If I only ever had one record in this table, it would work great, but unfortunately that's not the case.

    Any ideas?

    Thanks a million in advance.

    Matt

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Try DLookup() or DCount(). Search Access Help or Google for guidance on use of domain aggregate functions.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Add a text box with a calculation: IIf(fieldname<>Forms!mainform!fieldname,"a","b")

  4. #4
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Thanks @June7, I think this is the answer! My only problem is trying to call-up the subform as a domain in the expression.

    I have tried:
    =DCount("[FieldName]"," [subFormName].Form", "[FieldName]>[Forms]![MainForm]![Control]" )

    =DCount("[FieldName]"," [Forms]![MainForm]![subFormName].Form", "[FieldName]>[Forms]![MainForm]![Control]" )

    =DCount("[FieldName]"," [subFormName]", "[FieldName]>[Forms]![MainForm]![Control]" )

    Does anyone what my problem may be?

    Thanks!

  5. #5
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Thanks for the reply @aytee111, however this only checks the expression against one record, not the entire list of records, unless I'm missing something

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Can't reference form or report objects as the source domain, only tables or queries.

    Do you want the count based on all records in table or only on filtered subset of form?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Ahh that's my problem. I need a filtered subset.

    I am building a bicycle rental database.

    The main form I am working on is the "check out" form, which is reading/writing to a table with all of the bicycles(model#, serial #, etc)

    The sub form is a "reserves" form, which shows when the bicycle selected is reserved.

    My objective is to ensure a bicycle can't be rented when it is reserved.

    Somehow I need to ensure the checkout date on the main form does not lie within a reserve date in the reserve query, for that particular bicycle.

  8. #8
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Thanks for the help @June7.
    I used a dcount() expression with an "And" statement, this allowed me to filter by bike ID and also ensure the dates don't conflict.

    If interested: =DCount("[ReserveBegin]","[CurrentReserves]","[AssetID]=[ID] And [CheckOutDate]<[ReserveBegin]")

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Are you sure you get the desired results? I would expect not because the variable parameters are not concatenated.

    =DCount("*","[CurrentReserves]","[AssetID]=" & [ID] & " And [CheckOutDate]<#" & [ReserveBegin] & "#")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Hi @June7, can you possibly elaborate on what you mean? I seem to be getting the desired results, but if you think I am doing something incorrect I probably am, as I'm pretty new to access.

    Should I be changing my code to what you have typed below?

    Thanks so much!

    Quote Originally Posted by June7 View Post
    Are you sure you get the desired results? I would expect not because the variable parameters are not concatenated.

    =DCount("*","[CurrentReserves]","[AssetID]=" & [ID] & " And [CheckOutDate]<#" & [ReserveBegin] & "#")

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Try it and see what happens. Can always change it back.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Will do, I just want to understand the syntax. What does the "*" and the "#" do? What should I search online to read more about this?

    Thanks so much for the help.

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The asterisk (*) is wildcard. A count operation is performed on records so a specific field reference is not needed (as opposed to for example sum which must specify the field to operate on).

    The sharp (#) is delimiter character for date/time parameters of date/time fields. Text type fields need apostrophe (') for parameters. Numbers do not have delimiter.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  14. #14
    matt_wpg is offline Novice
    Windows 10 Access 2016
    Join Date
    Mar 2017
    Posts
    20
    Thanks! I will change and see the results

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

Similar Threads

  1. Clearing and entire column
    By MaineLady in forum Access
    Replies: 2
    Last Post: 07-09-2016, 12:03 PM
  2. Replies: 5
    Last Post: 07-17-2014, 09:16 AM
  3. Drop Down Box changes entire column
    By tennisbuck in forum Forms
    Replies: 4
    Last Post: 02-26-2014, 12:23 PM
  4. added text to entire column in the Query
    By Jerseynjphillypa in forum Queries
    Replies: 3
    Last Post: 06-12-2012, 09:39 AM
  5. Replies: 3
    Last Post: 04-01-2012, 01:11 PM

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