Parameter Query for Dates with timestamp in the field 3

[mdlaugh1]

I am trying to construct a Parameter (prompting) query in Access 2003 on a Date field that has a timestamp. The values in the field are in format of 2005-11-05 01:30:00 PM. My parameter is >=[Enter the Beginning Date] and <=[Enter the ending Date]. I want users to enter the actual date range of their report -- so for December, they would enter 2005-12-01 and 2005-12-31. But with the timestamp, they get records only through 2005-12-30. I don't want them to have to enter the full time value, and don't want them to enter 1 day greater than the real ending date. I've tried DateValue and formatting the field. Nothing works. It still sees the time of hh:mm:ss as being AFTER the ending date so ignores those records. thanks!!

 

[Lilliabeth]

Between [Enter the Beginning Date] And [Enter the Ending Date] + 1

 

[HandsOnAccess]

Have you tried like this...

SELECT FORMAT(<<Date>>,"YYYY-MM-DD") AS chkDate From <<Table>> Where FORMAT(<<Date>>,"YYYY-MM-DD") >= Format([Enter the Beginning Date],"YYYY-MM-DD") AND <= Format([Enter the Ending Date],"YYYY-MM-DD")

Hope this helps...

Regards,

 

[PHV]

Another way (SQL):
PARAMETERS [Enter the Beginning Date] DateTime, [Enter the ending Date] DateTime;
SELECT ...
FROM ...
WHERE Int([your date field]) Between Int([Enter the Beginning Date]) And Int([Enter the ending Date])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

I have tried the [End date] +1 and get "Statement too complex" error.

to all replies: do I understand that your suggestions go directly into the SQL code window... do not pass anything in the Criteria of the Query Design View screen?

thanks!

 

[mdlaugh1]

to PHV: I edited my SQL code view and added the following:
Int([SYNERGEN_SA_WORK_ORDER_TASK.actual_finish_date]) Between Int([Enter the Beginning Date]) And Int([Enter the ending Date]) and get statement incorrect or too complex... error. Have I just mis-understood or typed incorrectly? thanks!

 

[PHV]

Have you added the PARAMETERS line ?
Why not posting the whole SQL code ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

to PHV: thank you.. oops. here's the whole code, I did not have the Parameter line, lost it somehow... but now that I've added param & Int(date...) code I get ODBC call failed:

PARAMETERS [Enter the Beginning Date] DateTime, [Enter the Ending Date] DateTime;
SELECT SYNERGEN_SA_WORK_ORDER_TASK.DEPARTMENT, SYNERGEN_SA_WORK_ORDER_TASK.AREA, SYNERGEN_SA_WORK_ORDER_TASK.TYPE_OF_WORK, SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_NO, SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_TASK_NO, SYNERGEN_SA_WORK_ORDER_TASK.ACTUAL_FINISH_DATE, [SUPPLIES&MAT].SumOfACTUAL_AMOUNT AS SUP, PURCHCNTRACT.SumOfACTUAL_AMOUNT AS PURCH, Labor.SumOfREGULAR_AMOUNT AS LABOR, Labor.REG_HRS, Labor.OT_HRS, SYNERGEN_SA_WORK_ORDER_TASK.TASK_STATUS
FROM ((SYNERGEN_SA_WORK_ORDER_TASK LEFT JOIN [SUPPLIES&MAT] ON (SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_NO = [SUPPLIES&MAT].WORK_ORDER_NO) AND (SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_TASK_NO = [SUPPLIES&MAT].WORK_ORDER_TASK_NO)) LEFT JOIN PURCHCNTRACT ON (SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_NO = PURCHCNTRACT.WORK_ORDER_NO) AND (SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_TASK_NO = PURCHCNTRACT.WORK_ORDER_TASK_NO)) LEFT JOIN Labor ON (SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_NO = Labor.CHARGE_NO) AND (SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_TASK_NO = Labor.WORK_ORDER_TASK_NO)
WHERE (((SYNERGEN_SA_WORK_ORDER_TASK.DEPARTMENT)="STDT") AND ((SYNERGEN_SA_WORK_ORDER_TASK.AREA)="TRAFFIC") AND ((SYNERGEN_SA_WORK_ORDER_TASK.TYPE_OF_WORK)="SI-FABO") AND ((SYNERGEN_SA_WORK_ORDER_TASK.TASK_STATUS)="FINISHED") AND ((Int([SYNERGEN_SA_WORK_ORDER_TASK.actual_finish_date])) Between Int([Enter the Beginning Date]) And Int([Enter the Ending Date]))) OR (((SYNERGEN_SA_WORK_ORDER_TASK.TYPE_OF_WORK)="SI-WRK"))
ORDER BY SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_TASK_NO;

 

[mdlaugh1]

to PHV: I realize that I neglected to say that the Access db is linked to an Oracle 9i back end db. I removed the INT portion of the code and I no longer get the ODBC call failed msg, so I'm wondering if the "int" function is a problem with the Oracle connection?

 

[PHV]

Is it a passthru query ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

PHV: I'm sorry, some terms are new to me.. "passthru" query? I'll describe my layout.. I have 3 tables linked with an Oracle ODBC driver. have 2 tables joined... did this through Create Query in Design view. Entered Parameters in the Query, Param menu. Then switched to SQL window from the Design window and added your INT code section.

 

[PHV]

And this ?
Format([SYNERGEN_SA_WORK_ORDER_TASK.actual_finish_date],'yyyymmdd') Between Format([Enter the Beginning Date],'yyyymmdd') And Format([Enter the ending Date],'yyyymmdd')

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mdlaugh1]

to PVH: Ok, I built a new query with minimum code. At this point, I am not using the INT function at all. Changed to the "format" statements... although I had tried a format statement before, must have syntax wrong somewhere causing error...
Here's the code. Looks like I'm getting correct date range, when I enter 2005-12-30 for ending date I see those records, but now it's prompting me twice to enter the date range. i.e. I enter Beg and End date, then get prompted again for both beg and end date..

code:
PARAMETERS [Enter the Beginning Date] DateTime, [Enter the Ending Date] DateTime;
SELECT SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_NO, SYNERGEN_SA_WORK_ORDER_TASK.WORK_ORDER_TASK_NO, SYNERGEN_SA_WORK_ORDER_TASK.ACTUAL_FINISH_DATE, SYNERGEN_SA_WORK_ORDER_TASK.TASK_STATUS
FROM SYNERGEN_SA_WORK_ORDER_TASK
WHERE (((SYNERGEN_SA_WORK_ORDER_TASK.TASK_STATUS)="FINISHED")) AND Format([SYNERGEN_SA_WORK_ORDER_TASK.actual_finish_date],'yyyymmdd') Between Format([Enter the Beginning Date],'yyyymmdd') And Format([Enter the ending Date],'yyyymmdd');

 

[mdlaugh1]

to: PHV and HandsOnAccess.. thanks! with combination of your ideas I've gotten date range to pull correctly. I have build a bare bones query with just the Actual_Finish_Date, and pull records for the last date entered. Started query with following only, which makes the formated date field the first field in the Select statement...then adding aditional fields to query one at a time. (had to add the FORMAT(<<Date>>,"YYYY-MM-DD") statement before the <= Format.... section to make it work.)

"SELECT FORMAT(<<Date>>,"YYYY-MM-DD") AS chkDate From <<Table>> Where FORMAT(<<Date>>,"YYYY-MM-DD") >= Format([Enter the Beginning Date],"YYYY-MM-DD") AND FORMAT(<<Date>>,"YYYY-MM-DD") <= Format([Enter the Ending Date],"YYYY-MM-DD")"

thanks for the help!