Results 1 to 10 of 10
  1. #1
    Ghostdog920 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6

    Report Help - Display IF Value1 or Value2 or Value3 equals "Server"

    Good morning,



    I am working on a report that is giving me some trouble and hopefully someone can assist. I have a form that provides data entry of a file name that among the fields on it, contains the following:

    file_location_server
    source_folder_Server
    Destination_Folder_Server
    Archive_Folder_server

    All of these fields are lookups to another table called server and can contain either the same or different server ID's.

    I am trying to create a report that would allow a user to lookup a server name and display all associated files, whether it is because that server is the value for any of the following fields,

    file_location_server
    source_folder_Server
    Destination_Folder_Server
    Archive_Folder_server

    Can someone help me get this setup. I am thinking I need to create a query first, but not sure what I should create to get this to work.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a form, put in a combo box to pick one of the items in tServer table.
    then open the report.
    the query for the report would look at the box....

    select * from tServer where [serverID]=forms!myForm!cboServer

  3. #3
    Ghostdog920 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    Thanks for that. I have actually gotten that for. My issue is the user selects a server and then opens the report, I only know how to display the files if i compare the selection of the server name to one of the locations, not any of the 4. That seems to be my hangup. I need it to show all of the files if the server is listed in any of the 4 locations.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    where [field] like "*" & forms!myForm!cboServer & "*"

  5. #5
    Ghostdog920 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    I apologize for my denseness, based on that, would my entry be:

    where [field1] like "*" & forms!myForm!cboServer & "*"
    or
    where [field2] like "*" & forms!myForm!cboServer & "*"
    or
    where [field3] like "*" & forms!myForm!cboServer & "*"
    or
    where [field4] like "*" & forms!myForm!cboServer & "*"


    ??

  6. #6
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    where [field1] like "*" & forms!myForm!cboServer & "*"
    or
    [field2] like "*" & forms!myForm!cboServer & "*"
    or
    [field3] like "*" & forms!myForm!cboServer & "*"
    or
    [field4] like "*" & forms!myForm!cboServer & "*"

  7. #7
    Ghostdog920 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    Thank you! Going to give this a try now!

  8. #8
    Ghostdog920 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    I am still not doing something right. I have query created which has the following SQL:

    SELECT File.File_ID, File.File_Name, File.File_Location, File.Source_Folder, File.Destination_Folder, File.Archive_Folder, File.Archive_Notes, File.Application_ID, File.File_Notes, File.Archive_Server
    FROM File
    where [File.Location_Server] like "*" & forms!File Lookup by Server!Combo14 & "*"or where [File.Source_Server] like "*" & forms!File Lookup by Server!Combo14 & "*" or where [File.Destination_Server] like "*" & forms!File Lookup by Server!Combo14 & "*" or where [File.Archive_Server] like "*" forms!File Lookup by Server!Combo14 & "*" ;

    If i try to close the query it yells saying i am missing an operator in the query expression.

    I am also confused then on what my vba openreport statement should look like now. Currently it is:

    Private Sub btnGenerateFileServerReport_Click()


    Dim strWhereCondition As String
    strWhereCondition = Combo14
    DoCmd.OpenReport "File Query by Server Report", acViewReport, , "[Location_Server] = " & strWhereCondition
    'MsgBox (strWhereCondition)
    strWhereCondition = vbNullString




    End Sub


    Any help that you can give is greatly appreciated.

  9. #9
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Query: copy this query and make some changes - use only one of the fields, once you get that working then you can use the same format for the others. Change the reference to the form field to something you type in, including the "*". Get that working before moving into the more complex situation. There is a space missing above, before the first "or", it could be something as simple as that.

    Report: The query will handle the filtering for the report, remove the where from the open statement.

  10. #10
    Ghostdog920 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Posts
    6
    Thanks again everyone for your input. With your help I got this all working. My final query looked like this on the query side:

    SELECT File.File_ID, File.File_Name, File.File_Location, File.Source_Folder, File.Destination_Folder, File.Archive_Folder, File.Archive_Notes, File.Application_ID, File.File_Notes
    FROM File
    WHERE ((([File.Location_Server]) Like "*" & [forms]![FileLookupbyServer]![Combo14] & "*")) OR ((([File.Source_Server]) Like "*" & [forms]![FileLookupbyServer]![Combo14] & "*")) OR ((([File.Destination_Server]) Like "*" & [forms]![FileLookupbyServer]![Combo14] & "*")) OR ((([File.Archive_Server]) Like "*" & [forms]![FileLookupbyServer]![Combo14] & "*"));

    Combo14 of course is the combo box with the id on the form itself.

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

Similar Threads

  1. Replies: 2
    Last Post: 04-12-2016, 12:58 PM
  2. Replies: 1
    Last Post: 09-07-2015, 08:00 AM
  3. Replies: 5
    Last Post: 04-09-2014, 03:34 PM
  4. Export "Query or Report" to a "Delimited Text File"
    By hawzmolly in forum Import/Export Data
    Replies: 3
    Last Post: 08-31-2012, 08:00 AM
  5. Replies: 1
    Last Post: 08-23-2012, 08:32 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