LIKE '* [expression] '

[melanie]

hi i need something like this:

SELECT row1, row2, row3


FROM table
WHERE row3 LIKE '*[row2-row1]'

can you help me please?

[alansidman]

Does Row1, Row2 represent field names or are you trying to only bring certain records into the query. This is not very clear as to what you are attempting. Perhaps an example of what you have and what the end result should look like would help us to give you a viable answer. Use some values so it will be clear to us what your objectives are.

[melanie]

im sorry i was a bit tired when I wrote it.. by 'row' i meant column.. ie field.

i need to subtract the values from 2 fields (EndYear - StartYear) and check that the value in another field ends with that answer

[alansidman]

Is this what you are attempting to do?

SELECT Table7.StartYear, Table7.EndYear, Table7.CalcYear, [EndYear]-[StartYear] AS Difference, IIf([Difference]=[CalcYear],"Yes","No") AS Same
FROM Table7;

[melanie]

Here is exactly what I have:
a db containing info about the tv series i have on my pc. 3 of the fields are:
- Start Year: when the series started showing
- End Year: when the series ended (null if its still showing)
- Seasons: the seasons i have

Example: Friends 1994 2004 1-10

now i want to know which seasons i have missing, so from the example above i need to check 2004-1994 (10) and check that the 'Seasons' fields ends with '10' (ie '*10')

i tried something like this:

SELECT *
FROM Series
WHERE (Seasons Not Like '1*')
OR (Seasons Not Like '*' & Val([End Year])-Val([Start Year])
AND [End Year] Is Not Null);

but im getting a data type mismatch

[alansidman]

Ok. Try this:

SELECT Table7.Program, Table7.StartYear, Table7.EndYear, Table7.Seasons, [EndYear]-[StartYear] AS Tenure, Mid([Seasons],3) AS [Last], [Tenure]-[Last] AS SeasonsMissing, IIf([Tenure]=[Last],"Have All","Missing" & " " & [SeasonsMissing]) AS Missing
FROM Table7;

[melanie]

it worked thanks