sql error

adzinthepub
04-13-2004, 07:44 AM
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

reboot
04-13-2004, 07:59 AM
What is the database?

adzinthepub
04-13-2004, 08:05 AM
Its access 2000

Cheers

reboot
04-13-2004, 08:07 AM
"SELECT * FROM tblPrivelageAndBank WHERE holDate BETWEEN #" & Format ('12/12/03', "dd-mm-yyyy") & "# And #" & Format ('12/12/03', "dd-mm-yyyy") & "#"

adzinthepub
04-13-2004, 08:25 AM
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

MKoslof
04-13-2004, 08:59 AM
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

adzinthepub
04-13-2004, 09:14 AM
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

MKoslof
04-13-2004, 09:17 AM
No problem. Good luck

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum