Using Access Form Text Fields as "contains" criteria for a query (1 Viewer)

miaorantes

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
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

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
It sounds like you have a wildcard after the form reference, and you'd also need one before the form reference.
 

miaorantes

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
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

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
What do you have now?
 

miaorantes

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
So far

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

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

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

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

and for the "or" criteria I have:

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

Do you need any other info?
 

Kiwiman

Registered User
Local time
Today, 16:16
Joined
Apr 27, 2008
Messages
799
Howzit

Try something like

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

miaorantes

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
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

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
One possibility is that the bound column of the combo is different than what you expect, so the value being compared is different.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
Code for NAICS query

SQL for Query:

Code:
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):
Code:
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

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
In the properties of the combo box, I'm looking for the row source, column count and bound column.
 

miaorantes

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
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

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
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

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
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

New member
Local time
Today, 12:16
Joined
May 28, 2008
Messages
9
Pbaldy,

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

pbaldy

Wino Moderator
Staff member
Local time
Today, 09:16
Joined
Aug 30, 2003
Messages
36,118
Can you post a sample db?
 

abh.access

New member
Local time
Today, 09:16
Joined
Sep 3, 2011
Messages
1
:eek::eek: Thanks a lot I got my needful information to search data base query, form, etc...
Thanks..............
Howzit

Try something like

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

Users who are viewing this thread

Top Bottom