how to use '% XXXXXX%' in access 1

[JavaToPerlNowVB]

SELECT MAPNUM, MAINTACTION, WO
FROM GISADM_GNDLINEFACILITYMAINTPOLE
WHERE (((MAPNUM) Like [Forms]![MainSearchForm]![MapNum_Text]));

But some map numbers don't get recognize since they have some wild characters at the end of the map number.
so I did direct sql in with wild cards on like this

SELECT MAPNUM, MAINTACTION, WO
FROM GISADM_GNDLINEFACILITYMAINTPOLE
WHERE MAPNUM like '%someMapNumer%';

It did find the data but my problem is I don''t know how to use % % with (((MAPNUM) Like [Forms]![MainSearchForm]![MapNum_Text]))

Thanks for the help

 

[gol4]

if access

like '*" & [Forms]![MainSearchForm]![MapNum_Text]& "*'"

if % works for you then

like '%" & [Forms]![MainSearchForm]![MapNum_Text]& "%'"

 

[jrbarnett]

SELECT MAPNUM, MAINTACTION, WO
FROM GISADM_GNDLINEFACILITYMAINTPOLE
WHERE MAPNUM like '*SomeMapNumer*';

John

 

[JavaToPerlNowVB]

gol4,

I put like '%" & [Forms]![MainSearchForm]![MapNum_Text]& "%'" but it gave me an error, I was thinking may be you have put extra ", but that was not the case

thanks

 

[gol4]

There needs to be a space between the second ampersand &
'%" & [Forms]![MainSearchForm]![MapNum_Text] & "%'"

the & is used to force string concatenation of two expressions.

Sorry thats from typing on keys the size of chicklets.

 

[Remou]

Hi
It seems from the above that you are creating a query, not writing a line of SQL for code. If this is the case:
[tt]Like "*" & [Forms]![MainSearchForm]![MapNum_Text] & "*"[/tt]
Would go in the criteria line.

 

[PHV]

WHERE MAPNUM Like '%' & [Forms]![MainSearchForm]![MapNum_Text] & '%'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JavaToPerlNowVB]

Gol4

Sorry to bug you it gives me an error saying "Expression you entered has invalid string" and it blinks on
'%" & [Forms]![MainSearchForm]![MapNum_Text] & "%'" on where i have marked red, what could be the reason.....

thanks

 

[PHV]

Have you tried my suggestion ?

 

[gol4]

Use phvs seems I Have an Extra ' in there as well PHVS appears to be perfect

 

[JavaToPerlNowVB]

Yeap PHV I tried this still the same
Like '%' & [Forms]![MainSearchForm]![MapNum_Text] & '%'

Just to check if the rest of the SQL is ok, I tried this
Like '%[mapNum]%')); it works

I guess the syntax with the form stuff is wrong, if any of you guys have an idea please shoot a post.

thanks

 

[JavaToPerlNowVB]

Thanks PHV, Thanks alot please ignore the previous post, I re-wrote the query including the way you mentioned worked fine thanks alot all you guys/

Anu

 

[JavaToPerlNowVB]

Small problem, The query works, but when i try to run it from the form it doesn't work this is my large query which i run from form where all the fields get feeded from that specific form.

When the user open the form, the form should be blank, form will provide the data according the mapnumber that user enter. but after adding Like '%' & [Forms]![MainSearchForm]![MapNum_Text] & '%' this to the quey I already have data at the openning of the form.

Thanks for the help

 

[PHV]

WHERE MAPNUM Like '%' & [Forms]![MainSearchForm]![MapNum_Text] & '%' AND [Forms]![MainSearchForm]![MapNum_Text] & ' ' > ' '

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[JavaToPerlNowVB]

WOW PHV You are the best, This forum is lucky to have a smart person like you. Once again Thanks alot