Using Access Form Text Fields as "contains" criteria for a query

[miaorantes]

I am an intermediate user of Access and I am having difficulty using some forms that I have created that I am using separately as criteria for their respective queries.

Two forms are currently set-up and these are the problems:

1. Form name: Primary NAICS Code Search
This form contains one field called "NAICSlist" (record source is from a separate table entitled "NAICS codes")
"NAICSlist" is the name of the combo box for the field.

I would like to use the selection in that form as criteria in my query called "Primary NAICS Code Query" - its not working

2. Form name: Keyword_search
This form contains a text box called "WhatKeyword"

I would like to use whatever I enter in this text box as the criteria in the query called "Product OR Service Keyword" - this is working generally BUT it is only bringing up results that START WITH whatever I enter in the text box - I want it to return results that CONTAIN ANYWHERE IN THE FIELD what I entered

CAN YOU HELP???

Mia
miaorantes AT aol.com

 

[pbaldy]

It sounds like you have a wildcard after the form reference, and you'd also need one before the form reference.

 

[miaorantes]

Yes, I realize that, but I need what to specifically put in the query criteria - EXACTLY as I am supposed to type it...

any thoughts?

 

[pbaldy]

What do you have now?

 

[miaorantes]

So far

For the "criteria" in the "Primary NAICS Code Query" - I have:

Like [Forms]![Primary NAICS Code Search]![NAICSlist] & "*"

For the criteria in the "Product or Service Keyword" Query - I have:

Like [Forms]![Keyword_search]![WhatKeyword] & "*"

and for the "or" criteria I have:

Like "*[Forms]![Keyword_search]![WhatKeyword]*" & "*"

Do you need any other info?

 

[Kiwiman]

Howzit

Try something like

Like "*" & [Forms]![Primary NAICS Code Search]![NAICSlist] & "*"

 

[miaorantes]

I Love You

You dont understand how HAPPY I am!! Thank you so much...
but one more problem:

The Keyword search is working thanks to your help, but the NAICS code isnt - the difference for the NAICS code on is that I am using a combo box which is sourced from a table in the database. The search form NEVER brings up any results no matter which one I select.

Any thoughts?

 

[pbaldy]

One possibility is that the bound column of the combo is different than what you expect, so the value being compared is different.

 

[miaorantes]

How do i do that?

How do i resolve?

 

[pbaldy]

What is the row source of the combo (the SQL if a query), the column count and bound column properties? What field in the query that's failing is the criteria on?

 

[miaorantes]

Code for NAICS query

SQL for Query:

SELECT Profiles.[Company Name], Profiles.[Company Contact Name], Profiles.[In what state is the firm incorporated?], Profiles.[List and describe each major product/service offered by your fir], Profiles.[List and describe each major product/service offered by your fir], Profiles.[Please list your PRIMARY NAICS Codes], Profiles.[Please list all other NAICS Codes applicable to your firm], Profiles.[What Federal certifications do you have?]
FROM Profiles
WHERE (((Profiles.[Please list your PRIMARY NAICS Codes]) Like "*" & [Forms]![Primary NAICS Code Search]![NAICSlist] & "*") AND ((Profiles.[Please list all other NAICS Codes applicable to your firm]) Like "*" & [Forms]![Primary NAICS Code Search]![NAICSlist] & "*"))
ORDER BY Profiles.[Company Name];

The name of the table with the combo box values is : "NAICS codes"

Code for the form that users select the NAICS codes from the combo box is is (not sure if this is what you are looking for):

Option Compare Database

Private Sub Detail_Click()

End Sub

Private Sub Form_Load()

End Sub

Private Sub NAICSlist_BeforeUpdate(Cancel As Integer)

End Sub

 

[pbaldy]

In the properties of the combo box, I'm looking for the row source, column count and bound column.

 

[miaorantes]

My bad

OHHH! I'm sorry...

row source:
SELECT [NAICS codes].[2007 NAICS US Codes], [NAICS codes].[2007 NAICS US Title], [NAICS codes].*, * FROM [NAICS codes];

column count: 2

bound column: 2

 

[miaorantes]

Maybe this is the problem?

The search button on the NAICS code form just opens the query. How can I be sure that the query is taking the selection from the combo box?

 

[pbaldy]

The query criteria is on a field that appears to be codes, but the bound column of the combo is [2007 NAICS US Title], which does not appear to be codes. Don't know what it does to other aspects of the application, but for this purpose maybe you want to change the bound column to 1?

By the way, those field names are a handful. Spaces and symbols are not a good idea, and instead of really long names like:

[List and describe each major product/service offered by your fir]

I would use something like

ProductsServices

or something like that.

 

[miaorantes]

Pbaldy,

changing the bound column still didn't work... any other suggestions?

 

[pbaldy]

Can you post a sample db?

 

[abh.access]

Thanks a lot I got my needful information to search data base query, form, etc...
Thanks..............

Howzit

Try something like

Like "*" & [Forms]![Primary NAICS Code Search]![NAICSlist] & "*"