Using DatePart in Query - help 1

[cuok]

Hi friends!

DatePart("yyyy",[A].[DateCheck])=DatePart("yyyy",Date())

I know that this is the criteria to get 2003 which is this year, but how i can get the past year 2002?

down this post is the whole query i want to use to get the records if present in at least one time in past year.

I tried many options of -1 and using A.DateCheck)=(select max(datecheck)

with no success

SELECT A.Id, A.DateCheck, A.Tobramycin, A.Ciprofloxacin, A.Meropenem
FROM Tbl_SputuomCulture AS A
WHERE (((A.DateCheck)=(select max(datecheck)
                   from Tbl_SputuomCulture B
                   where A.id = B.id   and DatePart("yyyy",[A].[DateCheck])=DatePart("yyyy",Date() ))));

 

[cuok]

CONTINUSE....
and named it: DBMAXforPseuAeruGinosisFIRSTQuery
Then im using this query:

SELECT A.Id, A.Meropenem, A.Tobramycin, A.Ciprofloxacin, A.DateCheck,
 [A].[Meropenem]+[A].[Tobramycin]+[A].[Ciprofloxacin] AS xxx, 

      IIf(([A].[Meropenem]+[A].[Tobramycin]+[A].[Ciprofloxacin]="SSS")
 Or ([A].[Meropenem]+[A].[Tobramycin]="SS") Or ([A].[Meropenem]+[A].[Ciprofloxacin]="SS")
 Or ([A].[Tobramycin]+[A].[Ciprofloxacin]="SS"),3,1) AS Pseu_aeruginosa_Sens
    
  FROM DBMAXforPseuAeruGinosisFIRSTQuery AS A;

hope im more clearly now!
Thank in advance for any idea.
CUOK

 

[dodge20]

I am not exactly sure on this syntax, but something like this.

Use the DateAdd() function to subtract from the year DatePart

DateAdd('y', -1, DatePart("yyyy",[A].[DateCheck])=DatePart("yyyy",Date()))



Dodge20

 

[Golom]

This is a bit unclear. The clause

DatePart("yyyy",[A].[DateCheck])=DatePart("yyyy",Date())

will ALWAYS restrict you to values in the current year because "Date()" returns the current date. If you want the year before that then try

DatePart("yyyy",[A].[DateCheck])=DatePart("yyyy",Date()) - 1

 

[cuok]

Hi Friends!

Dear golom:
as i wrote i already did what yuo adviced but this gives me only one correct row inspite of i have another row.
MayBe somthing wrong in other place in my query.

Dear dodge20:
your answer returns ALL the records but one from 2003 and the other of 2002 (the correct one).
I have to check my self now and if you are in and solved it before me i eill be more than glade and thank you very much.

CUOL

 

[cuok]

Friends sorry!

dodge20, your answer gives only the last records of 2003 and not as i wrote.

cuok

 

[GoDawgs]

Golom's answer is correct...I think your query is off. Basically the first query you posted will find the latest date from last year in the table (that's the subquery portion) and then return only the records that have that specific date (probably why you're only getting one record back). Try something like this:

SELECT A.Id, A.DateCheck, A.Tobramycin, A.Ciprofloxacin, A.Meropenem
FROM Tbl_SputuomCulture AS A
WHERE DatePart("yyyy",[A].[DateCheck])=DatePart("yyyy",Date()) - 1

Hope that helps.

Kevin

 

[cuok]

Thank u very much GoDawgs !

its realy gives all 2002 records.
i tried many options and only this worked.
what i need now is TO RETRIEVE only the records OF Max(dateChek) - here also i tried many options based on yuor solutions but without any success.

i would appriciate any help!
thanks
CUOK

 

[GoDawgs]

Umm...I'd sort it by the date field and then use a Top statement (basically just limits the records returned to whatever number you put in...so Top 1 in this case). Then you can pretty much set up an easy query returning all records by date...and just add in the Top 1 portion. Check it out in help, it's not tough...especially with the Access query designer.

Kevin