Search results

m'I yrev yppah

It Works!! Thanks namliam. Thank You - Thank You - Thank You etc.

Sorry I figured it out where. I will go on!

namliam. I'm sorry that I'm inexperienced and please don't get frustrated with me. I am trying my best to do what you want and I appreciate your help and patience. The example you gave me - where do i put Sum(IIf([StatFlag]="W",1,0)) AS StatW and Sum(IIf([StatFlag] Is Null,1,0)) AS StatNull. If...

YES!!!! That is exactly what I want. Is it possible?

YES!!!! That is exactly what I want. Can it be done?

Yes you are right, but I want both unique Author and unique book to go with it. That's been my problem all along. I have tried many many ideas but no success!

Thanks namliam. I did as you said but still getting same results. 2 records of each authoe that has both statflags and 1 record for ones with 1 kind of ststflag. Here is the SQL of the query SELECT DISTINCT Authors.AuthorID, Authors.LastName, Authors.FirstName, Books.Title, Books.Category...

I gave you data in xls file. A couple of doz records. It should produce a list containing 1 record of each author with either a StatFlag of 'W' or if there is none then a record with StatFlag of IsNull. as it stands it gives both so a max of 2 records for each author. In the data I provided...

Try Again. http://www.access-programmers.co.uk/forums/attachment.php?attachmentid=50184&stc=1&d=1379017486

Not sure if it uploaded. It's Sample.xls

By giving you the whole query then I guess I'm giving you the table names and field names. I hope it's enough. SELECT Authors.AuthorID, Authors.LastName, Authors.FirstName, Books.Title, Books.Category, Books.CopyrightYear, Books.Series, Books.EditionNumber, Books.Notes, Books.field2...

I have a query that keys off on a status field. I just use Nz(status) and filter out all other types but 'W'. This allows me to show any record for each ID with that status (there is only one or none per each ID) but it also shows the records of ID's that have no status (one record per each ID)...

Thanks for advise. This is going as an expression in a Query. I have shortened it to test as spikspl suggested and fixed the reference and the Nz and came up with this. Where (If (Dcount("[Author]","Books","[StatFlag]='W'")) >'0' ([StatFlag])="W") This is how it looks in SQL but error...

Error I'm getting 'The expression you entered contains invalid syntax, or you need to enclose your text data in quotes. I can't figure out where it's wrong If Dcount(Nz([StatFlag]),[Books],[StatFlag]="W") >0 , ((Nz([StatFlag])) ="W" , (((Nz([StatFlag]))<>"R" And (Nz([StatFlag]))<>"M" And...

I guess writing all that down inspired me. I have solved my problem. If I have one blank record for each Author and make the queries using Nz(StatFlag) I can then set criteria as <>"R' etc. and eliminate all StatFlag codes I don't need but it will still show the blank one with the one I do need...

Ok. Here goes. I have a program that keeps records of books. There is an Authors table and a Books table (among others). There are 4 different status flags [Books].[StatFlag] that can be used. They are 'R' for books read, 'W' for books to read next (only one 'W' per Author, 'M' for more books...

Can I send you an email explaining what I'm trying to do because I'm sure there is an easier and better way to do what I need.

AuthorId is a number abd statflag is a string. Tried Message Box. no go. I know it's not working because it's actually in a If Then statement and the field is always set to the Then value.

Sorry but still not working. ??