Need help with SQL statement 1

[clapper62]

Someday maybe I'll get the hang of SQL statements until then could someone help me with this one

I'm trying to select records between two dates and also where
preparer = strPrep

Dim dbs As Database
Dim rst As DAO.Recordset

strSQL = "SELECT * FROM saved_ships WHERE preparedate BETWEEN ' # dateStartDate # ' And ' # dateEndDate # ' AND preparer = ' & strPrep & '"

Set rst = dbs.OpenRecordset(strSQL)

this statement returns 0 records



"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson

 

[Andrzejek]

You are almost there:

strSQL = "SELECT * FROM saved_ships WHERE preparedate BETWEEN '[blue]#" &[/blue] dateStartDate [blue] & "#[/blue]' AND '[blue]#" & [/blue] dateEndDate [blue]& "#[/blue]' AND preparer = '[blue]"[/blue] & strPrep & '"

You can find all of these by

Debug.Print strSQL

Have fun.

---- Andy

 

[SkipVought]

Hi,

It's your string concatenation...

strSQL = "SELECT * FROM saved_ships WHERE preparedate BETWEEN [b][red]#" &[/red][/b] dateStartDate [b][red]& "#[/red][/b] And [b][red]#" &[/red][/b] dateEndDate [b][red]& "#[/red][/b] AND preparer = [b][red]'" &[/red][/b] strPrep & [b][red]"'[/red][/b]"

Skip,

_[red][/red]

 

[SkipVought]

Andy,

Don't believe that you want BOTH the single quote AND # delimiters on the dates.

Skip,

_[red][/red]

 

[Andrzejek]

Sorry, skip the quotes.



Have fun.

---- Andy

 

[clapper62]

Thanks Skip,
That String Worked.

However Andy your tip about the debug.print helped me realize something I've missed somehow. I now realize that I have to make the Debug window look exactly like the SQL statement I am trying to create (with the real values of the variables in there place). Maybe this will help me biuld SQL satements in the future. If not then I'll be back

Thank you both



"There is no pleasure in having nothing to do; the fun is having lots to do and not doing it.
." - Andrew Jackson