problem selecting date
I trying to goto a record matching a couple criteria, one of them is a date but I keep getting this error:
"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."
I'm working with an .adp file. This is what I have:
Dim lrs As New ADODB.Recordset
Set lrs = Me.Recordset.Clone
lrs.Find "[PondName] = 'Yuch-001' And [SampleDate] = #8/3/2002#"
Me.Bookmark = lrs.Bookmark
I'll replace the values 'Yuch-001' and the date with values from a combo box. This all works fine in Access. Any help is appreciated.
Thanks,
Doug
[659 byte] By [
dtwilder] at [2007-11-11 6:59:59]
# 1 Re: problem selecting date
Is SampleDate defined as a Date data type in the database?
# 2 Re: problem selecting date
It's a "datetime" data type.
# 3 Re: problem selecting date
When you clone to an ADO Recordset, from what object (Me?) are you cloning?
# 4 Re: problem selecting date
The code is in a form so I guess the "Me" is the form whose source is the table with the date field.
-----
Doug
# 5 Re: problem selecting date
I think the problem is that an Access Form uses a DAO Recordset and you're using an ADO Recordset. AFAIK you cannot clone a Recordset from DAO to ADO.
# 6 Re: problem selecting date
I originally was using DAO:
Dim lrs As Recordset
Set lrs = Me.Recordset.Clone
lrs.Find "[PondName]= '" & cbo_site_date.Column(0) & "' And [SampleDate] = #" & cbo_site_date.Column(1) & "#"
Me.Bookmark = lrs.Bookmark
But was getting an error on the line with ".Find" in it after I upsized to SQL Server. So I tried ".FindFirst" and got a different error message. Then I tried switching to ADO and got around the .Find problem but, as you know, ran into other problems.
"AFAIK"?
# 7 Re: problem selecting date
The Find method is not supported by the DAO Recordset object. What was the error that was generated when using FindFirst?
AFAIK (As Far As I Know)
# 8 Re: problem selecting date
"Type Mismatch" on the "set lrs..." line when using DAO
"Method or Data member not found" on the ".FindFirst" line when using ADO
# 9 Re: problem selecting date
If your querying SQL Server, you delimit dates with single quotes ['] not [#].
I originally was using DAO:
Dim lrs As Recordset
Set lrs = Me.Recordset.Clone
lrs.Find "[PondName]= '" & cbo_site_date.Column(0) & "' And [SampleDate] = #" & cbo_site_date.Column(1) & "#"
Me.Bookmark = lrs.Bookmark
But was getting an error on the line with ".Find" in it after I upsized to SQL Server. So I tried ".FindFirst" and got a different error message. Then I tried switching to ADO and got around the .Find problem but, as you know, ran into other problems.
"AFAIK"?
# 10 Re: problem selecting date
"Type Mismatch" on the "set lrs..." line when using DAO
"Method or Data member not found" on the ".FindFirst" line when using ADO
I think we have our wires crossed. FindFirst is supported by a DAO Recordset and Find an ADO Recordset.
I would also check Joe's suggestion because it wasn't clear to me whether you were querying an Access or SQL Server database.
# 11 Re: problem selecting date
Okay, sorry, I get this error on the ".Find" line:
"Arguments are of the wrong type, are out of range or are in conflict with one another."
When I have:
Dim lrs As New ADODB.Recordset
Set lrs = Me.Recordset.Clone
lrs.Find "[PondName]= '" & cbo_site_date.Column(0) & "' And [SampleDate] = #" & cbo_site_date.Column(1) & "#"
Me.Bookmark = lrs.Bookmark
I get the same error if I try delimiting the date with a single quote.
# 12 Re: problem selecting date
See if the following helps:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vbcon98/html/vbconspecialcondsiderationswhenwritinginternationalcode.asp
# 13 Re: problem selecting date
I think I have the locale stuff correct. I tried the code with pound signs (#) and single quotes (').
Here's another thought, I tried building a query in SQL Server (Enterprise Manager) to select from the table in question a particular record with PondName and SampleDate specified. In SQL, the syntax for date looks like:
CONVERT(DateTime, '2004-08-03 00:00:00', 102)
So I tried:
lrs.Find "[PondName] = 'Yuch-001' And [SampleDate] = CONVERT(DateTime, '2004-08-03 00:00:00', 102)"
and I still get the "Arguments are of wrong type..." error.
I can't believe this hasn't been worked out somewhere, somehow! I really appreciate all your help.
Doug Wilder
Fairbanks, AK
# 14 Re: problem selecting date
I'm not sure that you can call the CONVERT function from a Filter property. Just out of curiosity, if you remove the date criteria does the Find method execute w/o an error. I just want to make sure that it is the date criteria that is generating the error.
# 15 Re: problem selecting date
Yes, if you remove the date query, it works fine. I tried that soon after the problem cropped up. There's just got to be a way! It's just a date!
# 16 Re: problem selecting date
Ugh! My thread seems to have died with the problem unresolved!
# 17 Re: problem selecting date
Actually I was going to take a look at this but I had to re-install SQL Server on my machine. Hopefully I'll be able to try some test scenarios in the next day or so.
# 18 Re: problem selecting date
But it really has died...sad...and my issue will crop up again for me. I suppose I'll start a new post. Thanks for all the input so far. Yer da best, Paul.
Doug
# 19 Re: problem selecting date
Yeah, sorry, I said I was going to look into this now that I've SQL Server installed and I haven't gotten around to it.
I'll see what I can come up with this afternoon (if I don't get sidetracked).
# 20 Re: problem selecting date
Doug, if you try:
"[PondName] = 'Yuch-001' And [SampleDate] = '08/03/2004'"
does it work? Also in your Column() references make sure your looking at the correct column.
I think I have the locale stuff correct. I tried the code with pound signs (#) and single quotes (').
Here's another thought, I tried building a query in SQL Server (Enterprise Manager) to select from the table in question a particular record with PondName and SampleDate specified. In SQL, the syntax for date looks like:
CONVERT(DateTime, '2004-08-03 00:00:00', 102)
So I tried:
lrs.Find "[PondName] = 'Yuch-001' And [SampleDate] = CONVERT(DateTime, '2004-08-03 00:00:00', 102)"
and I still get the "Arguments are of wrong type..." error.
I can't believe this hasn't been worked out somewhere, somehow! I really appreciate all your help.
Doug Wilder
Fairbanks, AK
# 21 Re: problem selecting date
For some reason this limitation completely slipped my mind:
PRB: ADO Find Method Only Supports One Criteria ( http://support.microsoft.com/default.aspx?scid=kb;en-us;195222)
# 22 Re: problem selecting date
THANK YOU! The filter option worked! This is what works:
Dim lrs As New ADODB.Recordset
Set lrs = Me.Recordset
lrs.Filter = "[PondName]= '" & cbo_site_date.Column(0) & "' And [SampleDate] = #" & cbo_site_date.Column(1) & "#"
Me.Bookmark = lrs.Bookmark
Thanks for all your help!
Doug
