Access question for any nerds out there

[Groove Fetish]

hi all...I tend not to post too often but I'm having troubles learning access and want to see if anyone can help me out.

I am trying to get a query to return some data and I can seem to get it to do what I want. I have a last name field and I want the datasbe to return to me any records that have certain names in them. But it is not just an equals to statement it should be a equals to or equals to or equals to....know what I mean??

so example. I have a record that says macdermid and a record that says baptiste and a record that says bosch. I want the baptiste records and macdermid records but not the bosch records. how does that statement look like.

this might be a really rudimentary question but I'm trying to teach myself access and its an uphill battle so far.

Thanks in advance for any help.

[ollie]

so example. I have a record that says macdermid and a record that says baptiste and a record that says bosch. I want the baptiste records and macdermid records but not the bosch records. how does that statement look like.

I'm not sure if you're looking for something specific or generic but this would work:

select * from tablename where lastname in ('baptiste ', 'macdermid')

OR

select * from tablename where lastname = 'baptiste' or lastname = 'macdermid'

[Groove Fetish]

i'm using this

=('macdermid','baptiste') but its not returning anything???

[Groove Fetish]

no sorry its telling me that there is a syntax problem with a comma??

[Groove Fetish]

here is the SQL string

WHERE (((['C].[Award Date])<="2007") AND ((['C].[End Date])>="2007"));(['C]. [PI-lastname] = 'baptiste' or [PI-lastname] = 'macdermid')

something is wrong with it but I can figure out what??

thanks for the help!

[AD]

Del *.*

[Groove Fetish]

oh and I know there are records to return on this string so I should get results

[Groove Fetish]

its saying there are characters left after the end of the SQL statement

[Groove Fetish]

I just took that "." out and its still saying there are characters left after the SQl string

[Groove Fetish]

ok I added an AND and took out the . but I am still not there:

WHERE (((['C].[Award Date])<="2007") AND ((['C].[End Date])>="2007")) AND (['C][PI-lastname] = 'baptiste' or [PI-lastname] = 'macdermid')

now its saying missing operator

[ollie]

First, take a closer look at my use of the "IN" keyword in my example.

As for this...

WHERE (((['C].[Award Date])<="2007") AND ((['C].[End Date])>="2007"));(['C] . [PI-lastname] = 'baptiste' or [PI-lastname] = 'macdermid')

I see a few things wrong here...

1. quotes used as delimiters should be single

i.e. '2007' and NOT "2007"

2. I'm not sure why you have a single quotation mark in this part: ['C].[Award Date]

3. Style note: although Access lets you, I'd avoid putting spaces in my field names; you'll avoid potential pitfalls

4. Remove that semi-colon and replace it with AND

Try those fixes and report back.

[Groove Fetish]

so statement looks like this now:

WHERE (((['C].[Award Date])<='2007') AND ((['C].[End Date])>='2007')) AND (['C][PI-lastname] = 'baptiste' or [PI-lastname] = 'macdermid')

but its still saying syntax error.

I do not know how to remove the spaces from the field names?? just go into design view and delete them?

[Groove Fetish]

oh the 'C is the name of the table not sure why the ' is there but Acceess renamed it that itself

[ollie]

Leave the spaces for now, that was just a style note, you can still get your query to work. Try this:

WHERE C.[Award Date] <= '2007' AND C.[End Date] >= '2007' AND (C.[PI-lastname] = 'baptiste' OR C.[PI-lastname] = 'macdermid')

[ollie]

P.S. If this is still not working can you send the entire query, including the FROM clause? Thanks!

[Groove Fetish]

so what happened when I switched to that was that it asked me for start dates parameters and end date parameters and last name parameters. but I thought those parameters were already in the string so shoudln't it have know that I wanted all dates >2007 and all dates ,2007 and macdermid and baptiste??? when I put in the parameters it actually retruned all the records in the database. so that one doesn't seem to work.

all this help is appreciated.

[Groove Fetish]

here is the whoel statement:

SELECT ['C].[Award Date], ['C].[End Date], ['C].[PI- LastName], ['C].[PI- FirstName], ['C].[Grant Agency], ['C].[Grant Title], ['C].[$ Amount]

FROM ['C]

WHERE (((['C].[Award Date])<='2007') AND ((['C].[End Date])>='2007')) AND (['C][PI-lastname] = 'baptiste' or [PI-lastname] = 'macdermid')

[ollie]

Do you know what the actual table name? Is C the actual name?

I don't have a copy of Access handy so I'm just guessing at the syntax.

[Groove Fetish]

yah the table name is 'C

[ollie]

Try this!

SELECT [Award Date], [End Date], [PI- LastName], [PI- FirstName], [Grant Agency], [Grant Title], [$ Amount]

FROM C

WHERE [Award Date] <= '2007' AND [End Date] >= '2007' AND ([PI- LastName] = 'baptiste' OR [PI- LastName] = 'macdermid')

[Groove Fetish]

can't find the input table seems it needs the '

[ollie]

Sorry to repeat myself, but are you sure the table is named just 'C? That looks more like an alias to me.

Oh well, try changing to FROM line to

FROM 'C

[Groove Fetish]

for sure the table is called 'C and it made me put that in [] but when I did it did return some records only problem was it didn't find the baptiste ones it should have it only found the macdermid ones. Why is that its not recognizing the or statement???

[ollie]

OK, I think I've gone too far in trying to clean up the sql statement.

Can you send me a copy of the one that works (without the OR stuff on the last name) and I'll try to work from there?

[Groove Fetish]

SELECT [Award Date], [End Date], [PI- LastName], [PI- FirstName], [Grant Agency], [Grant Title], [$ Amount]

FROM ['C]

WHERE [Award Date] <= '2007' AND [End Date] >= '2007' AND ([PI- LastName] = 'baptiste' OR [PI- LastName] = 'macdermid')

so this query did return records but only found the macdermid ones.