Access 2000 DTPicker on a form not playing nice!

[nowanda]

Howdy!

I'm having tons of trouble with the DTPicker. I have the user select a from_date and a to_date because I want to create a new table including only the records which fall between the to and from dates.

Here's the code:

dtStartDate = DateValue(Me.dtRetrieveFrom.Value)
dtEndDate = DateValue(Me.dtRetrieveTo.Value)

If Me.dtRetrieveFrom.Value > Me.dtRetrieveTo.Value Then
MsgBox ("The _from_ date must be before the _to_ date")
Exit Sub
End If

' This variable is set up to be a part of the where clause
dateBetween = "(qryRetrieveBase.DATE_REC Between #" & DateValue(dtStartDate) & "# And #" & DateValue(dtEndDate) & "#) AND "

' This is how I'll create the table
stmt = "SELECT 0 As chk, qryRetrieveBase.ID, qryRetrieveBase.PROJECT_ID, qryRetrieveBase.DATE_REC, qryRetrieveBase.TRACKING_NO, qryRetrieveBase.SAMPLE_ID INTO xtblRetrieve "

' These are the where clauses including datebetween
whr = " FROM qryRetrieveBase WHERE " & dateBetween & OutputProject & " ORDER BY qryRetrieveBase.TRACKING_NO;"

DoCmd.RunSQL (stmt & whr)

Note:
1-the query qryRetrieveBase has a field DATE_REC which is a Date/Time.
2-on the form there is a second listbox which allows the user to select a project name.

Please help!

Nowanda

 

[nowanda]

I Think I found my issue!!!

After further head-banging against the desk... please disregard my issues with the DTPicker. It's the underlying database that caused the problem..

It seems like the DTPicker uses the following date format:

06/04/2004 for April 6th

And my database has :

04/06/2004 for April 6th

The OS where the database resides I believe is in another language and has another standard. Does anyone have any further tips for competing date formats?

Nowanda