Acc 2003 - Forms - Problem in transferring Dates, to query

[Ignorant]

Hi
I have the below query to retrieve dates from a form named frmCaseHearingDatesRange to populate a query named QryPARAMCasesListedRangeFORM

SELECT QryCounselCaseLatestStage.counselName, QryCounselCaseLatestStage.partyNames, QryCounselCaseLatestStage.[Next Date], QryCounselCaseLatestStage.caseStage, QryCounselCaseLatestStage.remarks
FROM QryCounselCaseLatestStage
WHERE (((QryCounselCaseLatestStage.[Next Date]) Between [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgFrom] And [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgTo]));

The above code has two problems:

• if the dates are diff it does not pick up the records from the From date
  
• if the dates are same in both the DTPs no record is displayed

Could someone help..

[Allan Murphy]

When using SQL the date must in the format mm/dd/yyyy

Try this code

Between & "#" & Format([Forms]![frmCaseHearingDatesRange]![DTPDateOfHgFrom], "mm/dd/yyyy") & "#" & And [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgTo], "mm/dd/yyyy") & "#" &));

[HiTechCoach]

Hi
I have the below query to retrieve dates from a form named frmCaseHearingDatesRange to populate a query named QryPARAMCasesListedRangeFORM

SELECT QryCounselCaseLatestStage.counselName, QryCounselCaseLatestStage.partyNames, QryCounselCaseLatestStage.[Next Date], QryCounselCaseLatestStage.caseStage, QryCounselCaseLatestStage.remarks
FROM QryCounselCaseLatestStage
WHERE (((QryCounselCaseLatestStage.[Next Date]) Between [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgFrom] And [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgTo]));

The above code has two problems:

• if the dates are diff it does not pick up the records from the From date
  
• if the dates are same in both the DTPs no record is displayed

Could someone help..

Sounds like you might be storing the time with the date in the field QryCounselCaseLatestStage.[Next Date]

If this is true try this:

SELECT QryCounselCaseLatestStage.counselName, QryCounselCaseLatestStage.partyNames, QryCounselCaseLatestStage.[Next Date], QryCounselCaseLatestStage.caseStage, QryCounselCaseLatestStage.remarks
FROM QryCounselCaseLatestStage
WHERE (((DateValue(QryCounselCaseLatestStage.[Next Date])) Between [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgFrom] And [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgTo]));

[Ignorant]

Thanks HiTech I copy pasted your code...

SELECT QryCounselCaseLatestStage.counselName, QryCounselCaseLatestStage.partyNames, QryCounselCaseLatestStage.[Next Date], QryCounselCaseLatestStage.caseStage, QryCounselCaseLatestStage.remarks
FROM QryCounselCaseLatestStage
WHERE (((DateValue(QryCounselCaseLatestStage.[Next Date])) Between [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgFrom] And [Forms]![frmCaseHearingDatesRange]![DTPDateOfHgTo]));

but still the same result..

Sounds like you might be storing the time with the date in the field QryCounselCaseLatestStage.[Next Date]

I cannot say b'cos its a query from another query. however in the base table the date field is set to short date (earlier it was General date)

[HiTechCoach]

I cannot say b'cos its a query from another query. however in the base table the date field is set to short date (earlier it was General date)

Shrt date and General date are display formats. Note: data can still be stored date with ime data even is the display format is set to Short date.


Maybe this example will help: Report Dialog Examples

[Ignorant]

Shrt date and General date are display formats. Note: data can still be stored date with ime data even is the display format is set to Short date.

Oh that's a news to me..
Ok how to store date only in a date/time field. Note at this moment the date is being directly entered into the table...

[HiTechCoach]

Did you get a chance to look at the example in the link I posted?

[Ignorant]

I surely did, and still scrambling how things are being processed.
May be you could show me where to look for