What on earth is wrong here

Sub AppendData(TheDate As Date, TimeField As String, Watts As Integer)

Dim MyDb As Database
Dim DailySet As Recordset
Dim Criteria As String
Const conDateFormat = "\#dd\/mmm\/yyyy\ Hh:Nn:Ss#"

Set MyDb = CurrentDb
Set DailySet = MyDb.OpenRecordset("DailyOutput")

With DailySet
Criteria = "DateField = " & Format(TheDate, conDateFormat)
.FindFirst Criteria
If .NoMatch Then
.AddNew
Else
.Edit
End If
Blah Blah

DateField in the Table DailyOutput is defined as Date, Primary key
The FindFirst Criteria gives an error 3251 - Operation is not supported for
this kind of object. Changing the DateField to non primary key makes no
difference. Removing the time element from the conDateFormat also makes no
difference The Criteria looks like this DateField = #31/Jul/2011 00:00:00#
Thanks

Phil

Hi Phil,

You could try:

...
Const conDateFormat = "\dd\/mmm\/yyyy\ Hh:Nn:Ss"
...
Criteria = "DateField = #" & Format(TheDate, conDateFormat) & "#"
...


Imb.

On Aug 30, 12:49*pm, imb <im... (AT) onsmail (DOT) nl> wrote:
The above is what I would have done first. But it might also work to
move third back slash in front of the last pound sign in your format
string so it looks like this: "\#dd\/mmm\/yyyy Hh:Nn:Ss\#"

On 30/08/2011 20:49:55, imb wrote:
Hi Imb

Decided to use SQL instead.

Const conDateFormat = "\#dd\/mmm\/yyyy\#"

SQLStg = "SELECT DailyOutput.* FROM DailyOutput "
SQLStg LStg = SQLStg & "WHERE DateField = " & Format(TheDate, conDateFormat)
& ";"

Set MyDb = CurrentDb
Set DailySet = MyDb.OpenRecordset(SQLStg)

With DailySet
If .BOF Then
.AddNew
!DateField = TheDate
Else
.Edit
End If
Blah Blah.

Had trouble with FindFirst on other occasions.

Thanks agian. Problem solved

Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:j3ipce$2e7$1 (AT) speranza (DOT) aioe.org:

You shouldn't format the data value:

Criteria = "DateField = #" & TheDate & "#"

And, of course, as others have pointed out, it makes more sense for
this to be in the WHERE clause of the recordset's SQL SELECT...


--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

On 30/08/2011 22:12:34, "David-W-Fenton" wrote:
Thanks David.
Came to same solution using WHERE clause.
However, don't think the error message:
3251 - Operation is not supported for this kind of object
has anything to with semantics about formatting of the TheDate field.
I original tried Criteria = "DateField = #" & TheDate & "#" and had the same
error. Then had doubts about English date format (DD/MM/YYYY) v. American
format(MM/DD/YYYY) – both illogical, but the Americans more illogical than
the Brits Using Access 2010.
Is this a bug?
Phil

"Phil" <phil (AT) stantonfamily (DOT) co.uk> wrote in
news:j3jmt7$q09$1 (AT) speranza (DOT) aioe.org:

I didn't realize you were working in a non-US locale. Yes, you have
to format the date in a non-ambiguous format, or use DateSerial().
I'd use the latter, frankly, since I don't think it's logical to be
querying on string representations of dates, but instead should use
real date values (which is what DateSerial() returns).

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/