Date Conversion/Remove Prompt

[smorgan77]

Ok, I've looked through this forum and can't find what I really need so I'm asking for help. Below is my query...what I want to do is change the date format, currently it's in mm/dd/yyyy but I need it in yyyymm. BUT I also want to remove the prompt and make this an automatic query that will just get me the date for one month of data...here's the catch. I'm always going to need the last month's data. So we're in March now, I'll need it to be February's data. Any help would be great.

Thanks!


SELECT Count([h09_trac CH].ext_ptid) AS CountOfext_ptid
FROM [h09_trac CH] LEFT JOIN [h09_note CH] ON [h09_trac CH].ext_ptid = [h09_note CH].ext_ptid
WHERE ((([h09_note CH].diagnosis) In ("walk out","lwob","lwobs") Or ([h09_note CH].diagnosis) Like "*left without being seen*" Or ([h09_note CH].diagnosis) Is Null) AND (([h09_trac CH].stat) In ("LWBS","LWOB","LWBT","LWOT","W/O","WO","LWTC")) AND (([h09_trac CH].tri_acuity)<>" ") AND (([h09_trac CH].dat_of_ser) Between [Enter Start Date] And [Enter End Date]));

 

[PHV]

AND (([h09_trac CH].dat_of_ser) Between DateSerial(Year(Date()),Month(Date())-1,1) And DateSerial(Year(Date()),Month(Date()),0)));

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

Check out...

 From: DateSerial(Year(Date()),Month(Date())-1,1)
 To:   DateSerial(Year(Date()),Month(Date()),0)

as first and last of previous month.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[smorgan77]

Thanks guys, that helped, but I still need to convert the date to the format 200902 from 2/1/2009. I'm working on DateValue...

 

[SkipVought]

Format(AnyDate,"yyyymm")

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}