Partial Entry in Expression

  • Thread starter Thread starter Dave Couch
  • Start date Start date
D

Dave Couch

(Access 2003) I have 2 separate queries that I use for 2 separate forms. One
takes the first few characters to match with table data. i.e.
[FormField]+"*". So that if someone inputs "Or" I will find "Orange". The
second query is set up the same. The field is a text field but numbers are
entered. I try putting in 090 looking for 09060 but the query doesn't seem
to work. (I get no results.) If I put in 09060, it works fine. Do I need
to do something special to the expression or the form field??
 
Please provide the SQL view of the queries. Whether your text field contains
letters or numbers shouldn't make a difference in wildcard behaviour.
 
Although I don't think this should make any difference, you can try using
the & concatenation operator instead of the + concatenation operator
[FormField] & "*"

Other than that post your SQL statement.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Duane, here are the 2 expressions from the builder. The CountySearch works,
the MapNumberSearch does not. I don't know how to get the SQL view of the
expressions.

Like [Forms]![frmCountySearch]![ListCounty]+"*"

Like [Forms]![frmMapNumberSearch]![ListMapNumber]+"*"

Dave

Duane Hookom said:
Please provide the SQL view of the queries. Whether your text field contains
letters or numbers shouldn't make a difference in wildcard behaviour.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Dave Couch said:
(Access 2003) I have 2 separate queries that I use for 2 separate forms. One
takes the first few characters to match with table data. i.e.
[FormField]+"*". So that if someone inputs "Or" I will find "Orange". The
second query is set up the same. The field is a text field but numbers are
entered. I try putting in 090 looking for 09060 but the query doesn't seem
to work. (I get no results.) If I put in 09060, it works fine. Do I need
to do something special to the expression or the form field??
Click to expand...
Click to expand...
 
I agree with John on using "&" rather the "+" with text. "+" is used to add
numeric values but can be used for strings in special instances.

--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Dave Couch said:
Duane, here are the 2 expressions from the builder. The CountySearch works,
the MapNumberSearch does not. I don't know how to get the SQL view of the
expressions.

Like [Forms]![frmCountySearch]![ListCounty]+"*"

Like [Forms]![frmMapNumberSearch]![ListMapNumber]+"*"

Dave

Duane Hookom said:
Please provide the SQL view of the queries. Whether your text field contains
letters or numbers shouldn't make a difference in wildcard behaviour.
--
Duane Hookom
Microsoft Access MVP
If I have helped you, please help me by donating to UCP
http://www.access.hookom.net/UCP/Default.htm


Dave Couch said:
(Access 2003) I have 2 separate queries that I use for 2 separate forms. One
takes the first few characters to match with table data. i.e.
[FormField]+"*". So that if someone inputs "Or" I will find "Orange". The
second query is set up the same. The field is a text field but numbers are
entered. I try putting in 090 looking for 09060 but the query doesn't seem
to work. (I get no results.) If I put in 09060, it works fine. Do I need
to do something special to the expression or the form field??
Click to expand...
Click to expand...
Click to expand...
 
OK, I got the basics working. I had a "where" condition in my macro that was
causing the problem. Now, the only problem is that the subform only lists
one match, instead of all of the matches. I figure the problem is in how I
setup the subform to link to the main form. I would post the SQL statement
if you could give me a clue on how to find it. If all else fails, I could
recreate the subform and correct the linkage.

John Spencer said:
Although I don't think this should make any difference, you can try using
the & concatenation operator instead of the + concatenation operator
[FormField] & "*"

Other than that post your SQL statement.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dave Couch said:
(Access 2003) I have 2 separate queries that I use for 2 separate forms.
One
takes the first few characters to match with table data. i.e.
[FormField]+"*". So that if someone inputs "Or" I will find "Orange".
The
second query is set up the same. The field is a text field but numbers
are
entered. I try putting in 090 looking for 09060 but the query doesn't
seem
to work. (I get no results.) If I put in 09060, it works fine. Do I
need
to do something special to the expression or the form field??
Click to expand...
Click to expand...
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

On a form, check the form's record source - it will either show the name of
a query, the name of a table, or a query string. If it show a query string,
then copy the query string.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dave Couch said:
OK, I got the basics working. I had a "where" condition in my macro that
was
causing the problem. Now, the only problem is that the subform only lists
one match, instead of all of the matches. I figure the problem is in how
I
setup the subform to link to the main form. I would post the SQL
statement
if you could give me a clue on how to find it. If all else fails, I could
recreate the subform and correct the linkage.

John Spencer said:
Although I don't think this should make any difference, you can try using
the & concatenation operator instead of the + concatenation operator
[FormField] & "*"

Other than that post your SQL statement.
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Dave Couch said:
(Access 2003) I have 2 separate queries that I use for 2 separate
forms.
One
takes the first few characters to match with table data. i.e.
[FormField]+"*". So that if someone inputs "Or" I will find "Orange".
The
second query is set up the same. The field is a text field but numbers
are
entered. I try putting in 090 looking for 09060 but the query doesn't
seem
to work. (I get no results.) If I put in 09060, it works fine. Do I
need
to do something special to the expression or the form field??
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

iif function syntax problem 5
Use In() Expression as Query criteria with guts from Function 1
Full And Partial Matching 1
Partial information queries 2
NOT expression in a Query 3
Query - partial name 3
Special Characters in Access 2007 2
Duplicates in union query 3

Back
Top