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

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

Code:

---

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 :rolleyes:
  
• if the dates are same in both the DTPs no record is displayed :eek:

Could someone help..:)

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") & "#" &));

Quote:

---

Originally Posted by Ignorant

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

Code:

---

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 :rolleyes:
  
• if the dates are same in both the DTPs no record is displayed :eek:

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]));

Thanks HiTech I copy pasted your code...

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..:mad:

Quote:

---

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)

Quote:

---

Originally Posted by Ignorant

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

Quote:

---

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..:rolleyes:
Ok how to store date only in a date/time field. Note at this moment the date is being directly entered into the table...:D

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

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