Olvar
Jul 26 2006, 10:15 AM
I'm working on comparing a set of dates and while I've seen references to it, I've not been able to find out exactly to do it. I have 4 fields in the table, DtOff, DtReq, DtSch are all formatted as mm/dd/yyyy, the last SubDt is a system generated date and time field.
What I need is an output of any records that show a greater than 7 day difference between DtReq, and DtOff. This should be possible, but I can't seem to figure out how to get it to work.
niesz
Jul 26 2006, 10:16 AM
Have you looked at the DateDiff function?
fkegley
Jul 26 2006, 10:18 AM
The DateDiff function can do this:
Develop a query that uses the table and fields of interest.
Insert a calculated Field: using the DateDiff function to subtract the dates (the help files can explain better than I how to do it). In the Criteria: cell of that column put > 7.
kbrewster
Jul 26 2006, 10:20 AM
Olvar
Jul 26 2006, 10:22 AM
Thanks for the link. One of the examples I was looking at was for DateDiff, but I couldn't find any help for it, so I had no way figure out how to use it. Between that and Frank's example I should be able to figure this out, thanks again.
niesz
Jul 26 2006, 10:25 AM
You're welcome.
TIP: The best way to find syntactical and usage help for any function is to open a module, type in the function name, and with the cursor on the name press the F1 key. VBA Help will open with the info you need.
Olvar
Jul 26 2006, 11:11 AM
Ok kind of a linked question, the DateDiff is working beautifully. I'm using a Between criteria to limit what fiscal quarter we pull data from. When I use it with DtReq which is formatted as mm/dd/yyyy it works fine. When I use it for SubDt it doesn't work, SubDt is formatted as Now() and includes date and time. Is there a way to pull just the date part and have my criteria ignore the time portion?
kbrewster
Jul 26 2006, 11:14 AM
DateValue(SubDT) will get just the date.
Olvar
Jul 26 2006, 11:16 AM
Do I use that in my criteria?
Between [Enter start of Quarter.] And [Enter end of Quarter.]
kbrewster
Jul 26 2006, 11:18 AM
No...
Can you post the SQL from the query?
Olvar
Jul 26 2006, 11:22 AM
Here it is. This does not include the Between criteria as it only works in a field that I don't need it to work in.
CODE
SELECT InitialContact.Mt, InitialContact.ServiceType, InitialContact.DtTmrequest, InitialContact.DtTmoffered, InitialContact.DtTmscheduled, InitialContact.SUBMITDATE, DateDiff("d",[DtTmrequest],[DtTmoffered]) AS Expr1, InitialContact.ReasonDelay
FROM InitialContact
WHERE (((DateDiff("d",[DtTmrequest],[DtTmoffered]))>7));
Edited by: Olvar on Wed Jul 26 12:26:10 EDT 2006.
kbrewster
Jul 26 2006, 11:26 AM
Just create a new column in the query...
SubDtDateOnly: DateValue(SubDt)
and then put the between criteria under this new column
Olvar
Jul 26 2006, 11:32 AM
Hmmm that isn't working, I keep getting an error. Invalid Syntax
Edited by: Olvar on Wed Jul 26 12:33:18 EDT 2006.
kbrewster
Jul 26 2006, 11:38 AM
With the new column added...post your SQL again!
Olvar
Jul 26 2006, 11:41 AM
It won't even let me switch to SQL view. Ok, try this.
CODE
SELECT InitialContact.Medicaid, InitialContact.ServiceType, InitialContact.DtTmrequest, InitialContact.DtTmoffered, InitialContact.DtTmscheduled, DateValue([SUBMITDATE]) AS SUBMITDATE, InitialContact.SUBMITDATE, DateDiff("d",[DtTmrequest],[DtTmoffered]) AS Expr1, InitialContact.ReasonDelay
FROM InitialContact
WHERE (((DateValue([SUBMITDATE])) Between [Enter start of Quarter mm/dd/yyy.] And [Enter end of quarter mm/dd/yyyy.]) AND ((DateDiff("d",[DtTmrequest],[DtTmoffered]))>7));
Edited by: Olvar on Wed Jul 26 12:42:10 EDT 2006.
kbrewster
Jul 26 2006, 11:46 AM
hmmm...it looks ok to me. Is it giving you an error, and if so, what is the error?
Olvar
Jul 26 2006, 11:48 AM
Circular reference caused by alias, 'SUBMITDATE' in query definition's SELECT list.
kbrewster
Jul 26 2006, 11:49 AM
Wait...I see it now! You are naming the DateValue the same as one of your field names! Try this...
CODE
SELECT InitialContact.Medicaid, InitialContact.ServiceType, InitialContact.DtTmrequest, InitialContact.DtTmoffered, InitialContact.DtTmscheduled, DateValue([SUBMITDATE]) AS NewSUBMITDATE, InitialContact.SUBMITDATE, DateDiff("d",[DtTmrequest],[DtTmoffered]) AS Expr1, InitialContact.ReasonDelay
FROM InitialContact
WHERE (((DateValue([SUBMITDATE])) Between [Enter start of Quarter mm/dd/yyy.] And [Enter end of quarter mm/dd/yyyy.]) AND ((DateDiff("d",[DtTmrequest],[DtTmoffered]))>7));
Olvar
Jul 26 2006, 11:51 AM
Now I get:
" is not a valid name. Make sure that it does not include valid characters or puncuation and that it's not too long.
kbrewster
Jul 26 2006, 11:53 AM
Where is the " coming from?
Olvar
Jul 26 2006, 11:54 AM
I'm assuming off the the query name. When i saved it with the default of Query1, then it inserted "Query1" in that spot.
kbrewster
Jul 26 2006, 11:56 AM
So is the query working?
Olvar
Jul 26 2006, 11:58 AM
This appears to be working, but when i enter a date range that should pick up a record that meets this criteria, it does not.
CODE
SELECT InitialContact.Medicaid, InitialContact.ServiceType, InitialContact.DtTmrequest, InitialContact.DtTmoffered, InitialContact.DtTmscheduled, DateValue([SUBMITDATE]) AS NewSUBMITDATE, InitialContact.SUBMITDATE, DateDiff("d",[DtTmrequest],[DtTmoffered]) AS Expr1, InitialContact.ReasonDelay
FROM InitialContact
WHERE (((DateValue([SUBMITDATE])) Between [Enter start of Quarter mm/dd/yyyy] And [Enter end of quarter mm/dd/yyyy]) AND ((DateDiff("d",[DtTmrequest],[DtTmoffered]))>7));
No it's not.
Edited by: Olvar on Wed Jul 26 12:59:27 EDT 2006.
kbrewster
Jul 26 2006, 12:01 PM
How are you entering the dates into the input boxes...I just recreated this query and I have to type in the dates like this...
7/26/2006
to get the results to show for today.
Olvar
Jul 26 2006, 12:03 PM
That is how I am entering the dates, but with the data I have, I have one record that is over the 7 day mark. When i enter a date range for SUBMITDATE that should include that record, it is not showing up. I get a result that displays no records, when it should display the one.
kbrewster
Jul 26 2006, 12:04 PM
Is there anyway you could strip down your DB and post it?
Olvar
Jul 26 2006, 12:08 PM
Not really since i'm pulling data off of a remote server. At this point it has become a moot point, I just had the Director of QI come in and say he'd changed his mind he wanted it based off of DtReq, which worked just fine. So I'm off the hook. Thanks muchly for the help.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.