|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
May 18th, 2010, 09:25 AM
posted to microsoft.public.access
|
|||
|
|||
Wildcard in query
Hi All, I would like to kow how to use the "Like" and wildcard in a query, I
have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards 
|
|
#2
May 18th, 2010, 12:23 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards
|
|
#3
May 18th, 2010, 12:52 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
Hi Wayne, thanks for your reply, I have placed the code below in criteria and
then ran the query, it is now returning the word HONDA but nothing afterwards ? ie Honda Prelude or Honda Civic. Any further suggestions Thanks Again Left([Forms]![notprocessed]![combo49],InStr([Forms]![notprocessed]![combo49]," ")-1) "Wayne-I-M" wrote: It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards
|
|
#4
May 18th, 2010, 01:01 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
Are you still appending the wildcard character at the end?
Originally, you had Like [Forms]![ModelSelect]![Combo59] & "*" Using Wayne's suggestion, you want Like Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) & "*" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "blake7" wrote in message ... Hi Wayne, thanks for your reply, I have placed the code below in criteria and then ran the query, it is now returning the word HONDA but nothing afterwards ? ie Honda Prelude or Honda Civic. Any further suggestions Thanks Again Left([Forms]![notprocessed]![combo49],InStr([Forms]![notprocessed]![combo49]," ")-1) "Wayne-I-M" wrote: It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards
|
|
#5
May 18th, 2010, 02:26 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
Hi Guys, that works great although can the code also incorporate a single
word, I have just single words in the combo box and when selected it displays the message "this expression is too complex to be evaluated etc etc" Thanks both again for your help Regards Tony "Douglas J. Steele" wrote: Are you still appending the wildcard character at the end? Originally, you had Like [Forms]![ModelSelect]![Combo59] & "*" Using Wayne's suggestion, you want Like Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) & "*" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "blake7" wrote in message ... Hi Wayne, thanks for your reply, I have placed the code below in criteria and then ran the query, it is now returning the word HONDA but nothing afterwards ? ie Honda Prelude or Honda Civic. Any further suggestions Thanks Again Left([Forms]![notprocessed]![combo49],InStr([Forms]![notprocessed]![combo49]," ")-1) "Wayne-I-M" wrote: It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards .
|
|
#6
May 18th, 2010, 02:33 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
Slightly more complicated statement is all:
Like IIf(InStr([Forms]![FormName]![cboSelectCar]," ") 1, Left[Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1, [Forms]![FormName]![cboSelectCar]) & "*" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "blake7" wrote in message ... Hi Guys, that works great although can the code also incorporate a single word, I have just single words in the combo box and when selected it displays the message "this expression is too complex to be evaluated etc etc" Thanks both again for your help Regards Tony "Douglas J. Steele" wrote: Are you still appending the wildcard character at the end? Originally, you had Like [Forms]![ModelSelect]![Combo59] & "*" Using Wayne's suggestion, you want Like Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) & "*" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "blake7" wrote in message ... Hi Wayne, thanks for your reply, I have placed the code below in criteria and then ran the query, it is now returning the word HONDA but nothing afterwards ? ie Honda Prelude or Honda Civic. Any further suggestions Thanks Again Left([Forms]![notprocessed]![combo49],InStr([Forms]![notprocessed]![combo49]," ")-1) "Wayne-I-M" wrote: It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards .
|
|
#7
May 18th, 2010, 02:36 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
Like
Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar] & " "," ")-1) & "*" "blake7" wrote: Hi Guys, that works great although can the code also incorporate a single word, I have just single words in the combo box and when selected it displays the message "this expression is too complex to be evaluated etc etc" Thanks both again for your help Regards Tony "Douglas J. Steele" wrote: Are you still appending the wildcard character at the end? Originally, you had Like [Forms]![ModelSelect]![Combo59] & "*" Using Wayne's suggestion, you want Like Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) & "*" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "blake7" wrote in message ... Hi Wayne, thanks for your reply, I have placed the code below in criteria and then ran the query, it is now returning the word HONDA but nothing afterwards ? ie Honda Prelude or Honda Civic. Any further suggestions Thanks Again Left([Forms]![notprocessed]![combo49],InStr([Forms]![notprocessed]![combo49]," ")-1) "Wayne-I-M" wrote: It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards .
|
|
#8
May 18th, 2010, 03:43 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
Guys, Thanks to you both for all your help, it work perfectly now
Regards Tony "RonaldoOneNil" wrote: Like Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar] & " "," ")-1) & "*" "blake7" wrote: Hi Guys, that works great although can the code also incorporate a single word, I have just single words in the combo box and when selected it displays the message "this expression is too complex to be evaluated etc etc" Thanks both again for your help Regards Tony "Douglas J. Steele" wrote: Are you still appending the wildcard character at the end? Originally, you had Like [Forms]![ModelSelect]![Combo59] & "*" Using Wayne's suggestion, you want Like Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) & "*" -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "blake7" wrote in message ... Hi Wayne, thanks for your reply, I have placed the code below in criteria and then ran the query, it is now returning the word HONDA but nothing afterwards ? ie Honda Prelude or Honda Civic. Any further suggestions Thanks Again Left([Forms]![notprocessed]![combo49],InStr([Forms]![notprocessed]![combo49]," ")-1) "Wayne-I-M" wrote: It will not work like you have it as you are saying Like "Honda Prelude"&"somethingelse" Like "Honda OtherName"&"somethingelse" Use something like this in the criteria row (change cboSelectCar to the name of the combo box on your form) Left([Forms]![FormName]![cboSelectCar],InStr([Forms]![FormName]![cboSelectCar]," ")-1) -- Wayne Manchester, England. "blake7" wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. I have tried code below but it returns only the specific model thats selected in the Combo box and not all the range Like [Forms]![ModelSelect]![Combo59] & "*" can you help Regards .
|
|
#9
May 18th, 2010, 04:35 PM
posted to microsoft.public.access
|
|||
|
|||
|
Wildcard in query
On Tue, 18 May 2010 01:25:01 -0700, blake7
wrote: Hi All, I would like to kow how to use the "Like" and wildcard in a query, I have a combo box which the user selects from this can contain words like Honda Civic, or Honda Prelude etc etc, I would like to use the LIKE statement to make the query return all the results that start with Honda. The reason you're having trouble is that your field is storing two distinct pieces of information - a make (Honda) and a model (Prelude). These are two interacting but DIFFERENT pieces of information. this process would be a lot easier if you had the make in one field for the make, and a different field for the model. It's always easier to splice together two distinct chunks than to parse one thing into two. -- John W. Vinson [MVP]
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
