dataaccess

Hi to everyone

I have records like this M1I1A1,M1I2A2,M1I33A3,M2I1A4,M2I22A7 etc

I want to reterive records of type M1I and not A*

I tried like this to reterive(Select * from table where field like M1I[0-100][!A*]) but i was unsuccessfully

please can anyone give me solution

thank u

[Lothar Haensler]

Select * from table where field like M1I*
or
...M1I% (SQLServer)
by selecting only fields that match the "m1i*" specification you won't get any records that match "A*"

[sanjaymasina]

I am using access front end is VB

As u have answered M1I% then i am not getting the value M1I33 as in question,because I% takes only one value.In access it is ? and not %.
And if i put M1I* i get even the A* values
I want to reterive this in a string
strresult=M1I*[!A*] I tried like this but did not work.

This string I use in Sql Statement

("select * from table where field like " + strresult)

the answer I want in a string to concatnate to the statement.

Answered By U Sir

Select * from table where field like M1I*
or
...M1I% (SQLServer)
by selecting only fields that match the "m1i*" specification you won't get any records that match "A*"

[Lothar Haensler]

ok, how about this one:
select.. where field like "M1I*"
And field NOT LIKE "*A*"?

[BrewGuru99]

SELECT * FROM [Table] WHERE ([Field] Like "M1I*") And ([Field] Not Like "*A*")

Having the "*A*" will exclude any record with an "A" in it at all. Using "A*" would only exclude records that began with "A", which is redundant, since using "M1I*" includes only records that begin with "M1I".

The way you have this written ("M1I[0-100][!A*]") makes the SQL think that you are looking for records with "M1I" and the 'Field' [0-100] as well as the 'Field' [!A*]. Square brackets delliniate different field names.

Also, the Like operator needs your wild cards (*) to be within quotes (a string). That's why I say: "M1I*" Instead of just plain M1I* or [M1I*]. (In the latter case, the SQL would expect that to be the name of a field.

BrewGuru99

[sanjaymasina]

As u have replied( I* and field not like "*A*") This whole thing i should put in a string.

strResult = "'" & rsTask1(1) & "I#" & "'"
Set rsTask2 = dbMSP.OpenRecordset("Select name from Task_Information where notes like " + strResult)

How to put as in string (strResult) The particular string above is working fine but If I is more than number 9(like 10,11,12 etc) than the problem arises.

strResult = "'" & rsTask1(1) & "I#" & " And field not like "*A*" & "'"
If i put like this it says A variable not found.Please help me?


bye
thank u