Docmd.Openform with Date Criteria problem 1

[Taff82]

Hi all,

Have a problem trying to open a form with certain dates.

I have the following information:-

tblReviews
Learn_ID -Text
Provi_ID - Text
Lprog_ID - Text
PlannedDate - Date/Time (Short Date Format)
ReviewType - Text

FrmReviewList
txtLearn_ID
txtProvi_ID
txtLProg_ID
txtPlanned
txtType

The above fields are unbound text boxes which retrieve the information from a listbox listReviews.

I have a command button which when I click opens frmEditReview using the following code:

Dim stDocName As String
Dim stLinkCriteria As String

    stDocName = "FrmEditReview"
    stLinkCriteria = "[Learn_id] = '" & Me![txtLearn_id] & "'" & " " & " and [provi_id] = '" & Me![txtProvi_id] & "'" & " " & " and [lprog_id] = '" & Me![txtLprog_id] & "'" & " " & " and [ReviewType] = '" & Me![txtType] & "'" & " " & " And [PlannedDate]= #" & CDate(Me![txtPlanned]) & "#"
    
    DoCmd.OpenForm stDocName, , , stLinkCriteria

If I try to open a record which includes a date such as 15/03/2005 then their is no problem, but if I try to open a record with something like 01/02/2005 then it will not open and opens a blank form. It only happens with dates where the dd part (Format dd/mm/yyyy) is less than or equal to 12.

Does anyone have any ideas?

Thanks in advance for any help.

Taff.

 

[KenReay]

Hi

Try

And [PlannedDate]= #" & Format(CDate(Me![txtPlanned]),"yyyy/mm/dd") & "#"

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Taff82]

Hi Ken,

Thanks for the reply, worked great.

However, why would I need to format it as (yyyy/mm/dd) as apposed to (dd/mm/yyyy).

Taff.

 

[KenReay]

Because our American cousins do not us dd/mm/yyyy for dates. In Access (SQL) you have to use the American format or a non ambiguous format such as yyyy/mm/dd

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[Taff82]

Thanks Ken.

 

[JerryKlmns]

This format yyyy/mm/dd is the ANSI standard, as PHV had recently posted. Nothing to do with cousins or brothers! Or has it? American National Standards Institute

 

[PHV]

The REAL ansi satandard is yyyy-mm-dd

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

 

[KenReay]

OOPS sorry, I obviously him a sense of humour black hole there, ANSI Standard it is from now on promise

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[JerryKlmns]

I rest my case........