sql error

Hi everyone,
i have a database with a visualbasic front end. I get an error when i execute some sql saying:

Too few parameters. Expected 1.

this is the sql:


SELECT * FROM tblPrivelageAndBank WHERE holDate BETWEEN Format ('12/12/03', "dd-mm-yyyy") And Format ('12/12/03', "dd-mm-yyyy")


Can anyone see where i'm goin wrong?

Thanks for the help

Cheers, Adam

What is the database?

Its access 2000

Cheers

"SELECT * FROM tblPrivelageAndBank WHERE holDate BETWEEN #" & Format ('12/12/03', "dd-mm-yyyy") & "# And #" & Format ('12/12/03', "dd-mm-yyyy") & "#"

Hi reboot,
I now have the query:
SELECT * FROM tblPrivelageAndBank WHERE holDate BETWEEN # Format ('12/12/04', "dd-mm-yyyy") # And # Format ('12/12/04', "dd-mm-yyyy") #

but now i get this error

Syntax error in date in query expression 'holDate BETWEEN # Format ('12/12/04', [dd-mm-yyyy]) # And # Format ('12/12/04', [dd-mm-yyyy]) #'.


I cant see whats wrong. any ideas?

Thanks a lot for the help.

Cheers, Adam

OK, lets clean this up a little bit...say you need to pass hard coded values into your sql statement...you want to start with strings and cast them. See below. The same would apply if you decide to do this dynamically, taking text1.text values or parameters from the user. And do you want the same To and From date (12/12/04?). I assume you want one to be greater than the other..see below:



Dim myDate, myDate1 as Date

myDate = Format(CDate("12/12/03"), "dd-mm-yyyy")
myDate1 = Format(CDate("12/12/04"), "dd-mm-yyyy")

sSQL = "SELECT * FROM myTable WHERE myDate BETWEEN #" & myDate & "# AND #" & myDate1 & "#"


rs.Open sSQL, cn, adOpenKeyset, adLockOptimistic, adCmdText

Thanks for that,
in the past i have been able to format the date with sql (not the vb format function). but for some reason it doesnt like it today.

Thanks again for the help

Cheers, Adam

No problem. Good luck