Results 1 to 5 of 5
  1. #1
    Join Date
    May 2002
    Posts
    157

    Unanswered: LimitToList and Wild Cards (*)

    I want to set the combo box's LimitToList property to Yes, and be able to enter an * (asterix) so that the user can only select the numeric ID from the list or can enter the * wild card (which is text) to get all records.

    Hi all I have an unbound combo box named 'txtSSDetailsID' in an unbound form 'aStaffTrainingDeleteMainF'.

    This field allows me to select the id number from the aStaffSiteDetailsQ Query and provides additional information to the user to help them to select the correct record. Note that I have other fields on this form as well that when combined allow the query to pull out specific information.

    The select statement for the 'txtSSDetailsID' is as follows:

    SELECT [aStaffSiteDetailsQ].[ID], [aStaffSiteDetailsQ].[StaffID], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[SiteCode], [aStaffSiteDetailsQ].[Site], [aStaffSiteDetailsQ].[RoleTitle], [aStaffSiteDetailsQ].[StaffActInact] FROM aStaffSiteDetailsQ ORDER BY [aStaffSiteDetailsQ].[StaffLastName], [aStaffSiteDetailsQ].[Staff1stName], [aStaffSiteDetailsQ].[Site];

    The 'aStaffSiteDetailsQ' ID is an automatically generated numeric number.

    The idea is that the user opens the 'aStaffTrainingDeleteMainF' form and selects a record from the 'txtSSDetailsID' (and other fields). The ID selected is then used by the 'aStaffSiteDetailsQ' query.

    I believe that if I use the NotInList event that any entry made will be added to my table. I do not want this to happen.

    I want to set the combo box's LimitToList property to Yes, and be able to enter an * (asterix) so that the user can only select the numeric ID from the list or can enter the * wild card (which is text) to get all records.

    Can anyone help me here?

  2. #2
    Join Date
    Jul 2004
    Location
    South Dakota
    Posts
    267
    Try this link to get you started.

    Forms: Adding "All" to a listbox or combobox

    I believe that if I use the NotInList event that any entry made will be added to my table. I do not want this to happen.
    That is not what happens...search the help to find out what the NotInList event does.

    C

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I usually like to do something like this:

    SELECT tblCustomers.CustomerID, tblCustomers.CompanyName, tblCustomers.City, IIf([Forms]![MyFormName]![MyUnboundField]="*","All",[MyFieldName]) AS CCriteria
    FROM tblCustomers
    WHERE (((IIf([Forms]![MyFormName]![MyUnboundField]="*","All",[MyFieldName])) Like [Forms]![MyFormName]![MyUnboundField] & "*" Or (IIf([Forms]![MyFormName]![MyUnboundField]="*","All",[MyFieldName]))="All"));

    Basically it uses an expression in the query which has the value of the unbound field from the form (tricking the query expression to show the word "All" if a * is the unbound form field value and then uses criteria under that column like this:
    Like Forms!MyFormName!MyUnboundField & "*" or "All" (or you could just use: =Forms!MyFormName!MyUnboundField or ="All" as criteria for the column for number field types.)

    This works well when you have number type values and need to show all records from the unbound field when a * is used or just the values which match the unbound field when a number is entered.
    (since you won't get returns in your query if you just use =Forms!MyFormName!MyUnboundField for criteria against blank number values.)
    Last edited by pkstormy; 04-26-09 at 04:05.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    May 2002
    Posts
    157

    Further help required -LimitToList and Wild Cards (*)

    I have attached a Word Document in hope that this might help you to understand my problem.

    I have given up on trying to Use and *. I have been given so many suggestions on how to do what I want to do, but unfortunately although I know a reasonable amount of Access I am getting totally lost and am unable to get anyone's suggestions to work.

    I figure that if I show you the problem then perhaps you can help me.

    My problem is now how to get my query to work correctly.

    The SQL for the query is currently:
    SELECT StaffTraining.ID, StaffTraining.StaffID, StaffTraining.SSDetailsID, StaffTraining.TrainingStage, StaffTraining.CourseCode, StaffTraining.ProvCode, StaffTraining.StartDate, StaffTraining.EndDate, StaffTraining.CourseCost, StaffTraining.CourseHours, StaffTraining.TrngExpireDate, StaffTraining.ResultCode, StaffTraining.Note
    FROM StaffTraining
    WHERE (((StaffTraining.SSDetailsID) Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchSSDetailsID]) AND ((StaffTraining.TrainingStage)="Proposed Training")) OR (((StaffTraining.TrainingStage)="Proposed Training") AND ((StaffTraining.CourseCode) Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchCourseCode])) OR (((StaffTraining.TrainingStage)="Proposed Training") AND ((StaffTraining.ProvCode) Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchProvCode]));


    If I run this query without linking it to the form, I can enter * (asterix) in all three Parameter Values and it will give me all ‘Proposed Training Records’. The same also occurs if I just add an * (asterix) in one Parameter and leave the two others blank.

    It has finally occurred to me that if I want to have the option of seeing all details then why not just create a query that does just this and have a separate query that allows me to select out only the records that I need.

    THE QUERY WORKS providing I am only selecting one parameter at a time or selecting all records using the * (Asterix).

    If I want to select more than one parameter then it does not work.
    If I enter nothing on the First Parameter but specify that I want ‘proposed’ training I will then get a query which lists all records with ‘proposed’ training and ProvCode = BRICK and a course code of DRUALC then I should only get one record showing.
    But I am getting all records with BRICK and all records with DRUALC.

    SO IT DOES NOT WORK PERFECTLY

    So what I am trying to do is:
    If I enter only one of the three parameters, then I should get only records that match that one parameter.
    If I enter two of the three parameters, then I should get only records that contain the two parameters (in the one record).
    If I enter all three parameters, then I should get only records that contain the three parameters (in the one record).

    Here's hoping someone can help me to get the query to work the way I want it to work. Taking a look at the attached word document will hopefully help explain everything.
    Attached Files Attached Files

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the problem, i believe, is your use of ORs instead of ANDs

    first, let's discombobulate your WHERE clause by adding some line breaks and spacing...
    Code:
    WHERE ( 
    
             ( 
             ( StaffTraining.SSDetailsID ) Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchSSDetailsID]
             )  
       
         AND ( 
             ( StaffTraining.TrainingStage ) ="Proposed Training"
             ) 
    
          )  
    
    
       OR ( 
    
             ( 
             ( StaffTraining.TrainingStage ) ="Proposed Training"
             )  
       
         AND ( 
             ( StaffTraining.CourseCode )  Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchCourseCode]
             ) 
    
          )  
    
       OR ( 
    
             ( 
             ( StaffTraining.TrainingStage ) ="Proposed Training"
             )  
       
         AND ( 
             ( StaffTraining.ProvCode )  Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchProvCode]
             ) 
    
          )
    notice that there is repetition of the "Proposed Training" condition

    let's clean this up by first removing the unnecessary parentheses --
    Code:
    WHERE ( StaffTraining.SSDetailsID Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchSSDetailsID]
        AND StaffTraining.TrainingStage ="Proposed Training"
          )  
       OR ( StaffTraining.TrainingStage ="Proposed Training"
        AND StaffTraining.CourseCode  Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchCourseCode]
          )  
       OR ( StaffTraining.TrainingStage ) ="Proposed Training"
        AND StaffTraining.ProvCode    Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchProvCode]
          )
    and then simplifying by isolating the common condition --
    Code:
    WHERE StaffTraining.TrainingStage ="Proposed Training"
      AND ( 
          StaffTraining.SSDetailsID Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchSSDetailsID]
       OR StaffTraining.CourseCode  Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchCourseCode]
       OR StaffTraining.ProvCode    Like [Forms]![aStaffTrainingDeleteMainF]![fCboSearchProvCode]
          )
    now you can easily see that if you enter * for one of those three conditions, it will return all rows, yes? because it's an OR that joins them
    So what I am trying to do is:
    If I enter only one of the three parameters, then I should get only records that match that one parameter.
    If I enter two of the three parameters, then I should get only records that contain the two parameters (in the one record).
    If I enter all three parameters, then I should get only records that contain the three parameters (in the one record).
    try changing the ORs to ANDs and see what happens

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •