Page 1 of 2 12 LastLast
Results 1 to 15 of 25
  1. #1
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162

    combobox value or text with wildcard as a query criteria

    Hi there.
    Can I have combobox value or text with wildcard as criteria of a query, so that I can search with partial value of a field? For instance a cell value in my Import_ID field is "2020-21 BHM Con 3" which is text. I wish to have an Unbound combobox "Station" in a form whose value suppose I select BHM. So in my query criteria I put
    Like "*" & "[Forms]![Form1]![Station]" & "*"
    It should fetch BHM from Station combobox and should return the above mentioned record ie "2020-21 BHM Con 3".


    But it returns nothing.

  2. #2
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Try this as the query criteria

    [Forms]![Form1]![Station]
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    don't put quotes around the FORMS path

    "*" & [Forms]![Form1]![Station] & "*"

  4. #4
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by ranman256 View Post
    don't put quotes around the FORMS path

    "*" & [Forms]![Form1]![Station] & "*"
    Thanks Mate! It works accordingly. But now I am facing another problem.
    Along with "Station" field I'm applying multiple field criteria.
    For instance I have another combobox named "Des" which fetches values from "Description" field. In my second criteria I have put
    Code:
    [Forms]![Form1]![Des]
    which then returns double filtering result of the query.

    But sometimes I am not interested to do the double filtering ie of the Description field. Then I left the "Des" combobox blank on the "Form1". The problem occurs when I run the query leaving the combobox blank. It then returns no result.

  5. #5
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    You need to remove the criteria from the Query.

    Then create a Command Button on your Form1 with an On Click Event like this:-

    Code:
    Dim strCrit As String
    If Me.Station > "" Then
      strCrit = strCrit & "([Import_ID] = " & Chr(34) & Me.Station & Chr(34) & ") AND "
    End If
     
    If Me.Des > "" Then
      strCrit = strCrit & "([Description] = " & Chr(34) & Me.Des & Chr(34) & ") AND "
    End If
    If strCrit > "" Then
      strCrit = Left(strCrit, Len(strCrit) - 5)
     
    DoCmd.OpenQuery "QueryName", acPreview, , strCrit
     
    End If
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  6. #6
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by mike60smart View Post
    Hi

    You need to remove the criteria from the Query.

    Then create a Command Button on your Form1 with an On Click Event like this:-
    Hi!
    The code crashes. Just to remind you that Import_ID is a unique field of my Cons table which is the mother table of my database.

  7. #7
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    What is the name of the field for Station?
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  8. #8
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    Quote Originally Posted by bubai View Post
    Thanks Mate! It works accordingly. But now I am facing another problem.
    Along with "Station" field I'm applying multiple field criteria.
    For instance I have another combobox named "Des" which fetches values from "Description" field. In my second criteria I have put
    Code:
    [Forms]![Form1]![Des]
    which then returns double filtering result of the query.

    But sometimes I am not interested to do the double filtering ie of the Description field. Then I left the "Des" combobox blank on the "Form1". The problem occurs when I run the query leaving the combobox blank. It then returns no result.
    You might get the desired result if you put the second criteria on the next line down to the first criteria. This should change the AND between the criteria to OR
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

  9. #9
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by Bob Fitz View Post
    You might get the desired result if you put the second criteria on the next line down to the first criteria. This should change the AND between the criteria to OR
    But the second criteria is on the second column ie Description that's without a wildcard. First criteria is on Import_ID with a wildcard.

  10. #10
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Can you upload a zipped copy of the database
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  11. #11
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by mike60smart View Post
    Can you upload a zipped copy of the database
    Sure.
    FYI, recordset that are relevant are -
    Table Cons,
    Query1,
    Form1.
    But CopyofQuery1 is the original query on which I tried to use criteria without your code.
    Attached Files Attached Files

  12. #12
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Why are you creating this string in the Index_ID field?

    "2018-19 Mgl Cig 2"

    Then in your Combobox you have a hardcoded List of 3 Letter Strings

    What would happen if you needed another 3 Letter String to add to this hard Coded List?

    I would recommend that you add a field that allows you to select a 3 Letter String from a Lookup Table which would solve this problem
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  13. #13
    bubai is offline Competent Performer
    Windows 7 32bit Access 2016
    Join Date
    Nov 2014
    Location
    Dhaka
    Posts
    162
    Quote Originally Posted by mike60smart View Post
    Hi

    Why are you creating this string in the Index_ID field?

    "2018-19 Mgl Cig 2"

    Then in your Combobox you have a hardcoded List of 3 Letter Strings

    What would happen if you needed another 3 Letter String to add to this hard Coded List?

    I would recommend that you add a field that allows you to select a 3 Letter String from a Lookup Table which would solve this problem
    Hi Mate.
    THe "Import_ID" field has 4 part
    Fiscal year,
    Station,
    Catagory,
    SL.

    This field is fed from the form "FrmCons" where I have 4 comboboxes for each parts. I have hardcoded the values for the comboboxes because my no of stations and category are fixed. This field help me identify the consignment at a glance. And this is a unique field. There is no chance of duplicate. Comboboxes for other 2 parts have options for change in the form.

  14. #14
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    Sorry but I stick with my opinion that you should have a field for Station and actually store the StationID from your list of Stations.

    This would enable you to search quite easily for a specific Station

    Luck with your Project
    You can PM me if you need further help.
    Good Reading https://docs.microsoft.com/en-gb/off...on-description

  15. #15
    Bob Fitz's Avatar
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    3,530
    bubai

    IMHO you should start again.

    Read about database design, table relationships and normalization

    Define the relationships

    Do not use lookups in tables. Use lookups on forms.

    Good luck with your project.
    If this helped, please click the star at the bottom left of this posting and add to my reputation . Many thanks.
    Bob Fitzpatrick

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 7
    Last Post: 11-11-2014, 06:10 PM
  2. Wildcard within a combobox
    By T_Tronix in forum Access
    Replies: 10
    Last Post: 11-21-2013, 03:35 PM
  3. Replies: 3
    Last Post: 10-31-2012, 12:50 PM
  4. Replies: 1
    Last Post: 08-13-2012, 03:38 PM
  5. Problem with a wildcard in a query criteria
    By desk4tbc in forum Programming
    Replies: 1
    Last Post: 08-10-2011, 06:02 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