sql with between

I have a table with these

...| info | birth | death | ...

where birth and death represent a range of time

and a variable

Dim mydate
mydate = Date

with date of my system

I want select from table only the records where mydate belong to range [birth death]

---------------

sql = "SELECT * FROM table WHERE mydate BETWEEN #"& birth &"# AND #"& death &"#"

but i have error:

Microsoft OLE DB Provider for ODBC Drivers errore "80040e14'

[Microsoft][Driver ODBC Microsoft Access] Errore di sintassi nella data nell'espressione della query 'mydate BETWEEN ## AND ##'.

I have cheked that birth and death are not empty.

thank you

Looks like birth and death are losing the values by the time they get to the SQL statement. Did you Dim them both?

ALSO, try using single quotes around the value as well, i.e.:

'" & birth & "'

For MS access you connect to a DB using this driver.

"PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" & server.mappath("/experiments/data/biblio.mdb") & ";"


How might I be able to connect to an SQL db?
What is the code for it?

It uses ODBC not OLEDB in some way. This is all I know.. anyone help me?
PLEASE?