openform where clause with date

[Wysy]

Hi,
I am seeking help for the following problem.
I would like to open a form to a specific date that is taken from another form. Date field format is short date and looks like yyyy.mm.dd
I have figured it out that it only works if date format is yyyy/mm/dd
I know format function can do this, but i do not know how to write the code with correct syntax.
For example i have tried:
DoCmd.OpenForm "TreatmentsTB1", , , "DOV= #"& Format(2015.01.11,"yyyy/mm/dd")&"#"
it does not work.
Any idea?
Thanks
Andy

 

[jdraw]

Format deals with strings, not Dates.
IF DOV is a Date/Time datatype, then you could use CDate function to convert a string to a Date.

Try

 DoCmd.OpenForm "TreatmentsTB1", , , "DOV= [COLOR="Red"]Cdate[/COLOR] (Format([COLOR="Red"]"[/COLOR]2015.01.11[COLOR="Red"]"[/COLOR],"yyyy/mm/dd"))

Good luck.

 

[MarkK]

But if you are hard-coding the date, why bother with Format(). Just hard-code it in the format you need. . .

DoCmd.OpenForm "TreatmentsTB1", , , "DOV=#2015/01/11#"

But the other thing that is a factor is, what is the type of DOV in the table? If it is a date, your filter has to use date delimiters. But maybe your date is stored as a string, which is more likely if you must use a format like yyyy/mm/dd, and then your filter should be . . .

"DOV='2015/01/11'"

 

[vbaInet]

I don't think that Format() will return a properly formatted date with such a string.

Use the raw date value from the field and format your date as a US date:

DoCmd.OpenForm "TreatmentsTB1", , , "DOV = " & Format(Forms![COLOR=Blue]FormName[/COLOR]![COLOR=Blue]DateField[/COLOR], "\#mm\/dd\/yyyy\#")

Or:

DoCmd.OpenForm "TreatmentsTB1", , , "DOV = Forms![COLOR=Blue]FormName[/COLOR]![COLOR=Blue]DateField[/COLOR]")

Or:

DoCmd.OpenForm "TreatmentsTB1", , , BuildCriteria("DOV", dbDate, Forms![COLOR=Blue]FormName[/COLOR]![COLOR=Blue]DateField[/COLOR])