How to retreive a specific date from an Access database table?

[netharam]

Hi, I tied a lot to retreive a set of records which fall into a range of two dates. Actually my application is to diaplay a set of transcations that has happened in the mentioned dates.
Details:
Msaccess 2002 table (bills) with fields
Billno Long
billdate Date/time (General Date)
billamount. long

Now I'm accessing this table using vb 6.0. Everything is fine & the table is also opened.
When I get from & To dates from the user using the VB form & execute the query I receive an error message.
The query is
" select * from bills where billdate > '" & fr_dt & "' and billdate < '" & to_dt &"'"
I receive an error message "Datatype mismatch when I execute
rs.open.

the from date is for eg. " 09/02/2002 "
I also tired " 09/02/2002 01:00:00 AM"

Help required very urgently.;)

Advanced Thanks.

[classicsoftware]

I am a FoxPro programmer, not an Access porgrammer but when you get a data type mismatch like you have in this case, the variable being created in the form and asked for in the querry is a in character form and the data table is in date form. You need to change the querry to date form though I don't know how you would do it, I just know that's what you have to do.

[sleddog]

The query:

" select * from bills where billdate > '" & fr_dt & "' and billdate < '" & to_dt &"'"

uses numerical operators (greater than, less than). It can only return results if run against numerical data. But your dates are in the format "09/02/2002", which is not numerical data.

You need to read up on how date strings are used in VB6. I have no idea :)

[classicsoftware]

Sleddog:
Since Access borrowed it's main database engine technology from FoxPro, though not its file structure, I am almost certain (since you can do it in FoxPro) you can use <> and perform date arithmetic on fields that contain date data.

For example in FoxPro

You can create a date variable by putting it in brackets

start_date=[12/01/2002]
end_date=[12/31/2002]

and then pose the querry: ?start_data>end_date
and FoxPro will respond .f. for false.

There are also built in fucntions will convert date strings to charachter and vice-versa.

If you have a date filed called dob and want to convert into a character string let's say for a mail merge you would use the following command:
DTOC(dob) and FoxPro would output the contents of the field in character format

Also if you want to convert a charcater string of "12/31/2002" into a date form for comparison with date data in a data table the command would be:

CTOD("12/31/2002")

There must be similar functions/commands in access or VB that can be called to change the character that is being unputted in VB to date format so it can be combared to the date data in the access database.

[sleddog]

Yup, that's what I was getting at when I said the OP needs to read up on how date strings work with VB6 :) For whatever reason his date data seems to be treated as character data. I was wrong to give the impression that *only* numerical data can be handled with these operators.

But I'm way in over my dogears here. I haven't touched Access or VB for years and promised myself I never would again :D

I leave this in your capable hands, classic....