Need to load records between dates

I am looking for what is wrong with my syntex for a form that is to load all records for a specified name and between the dates that are "Start_Date" and "End_Date".

So far I have the following code:

ZID = Form_Completed_Projects![ZID_Choice].Value
Start_Date = Form_Completed_Projects![Start_Date].Value
End_Date = Form_Completed_Projects![End_Date].Value

SQLstatement = "SELECT * FROM dbo_cur_A2_Projects " & _
"WHERE ([Responsible_Party]='" & ZID & "') and " & _
" ([Actual_Completed] between '" & Start_Date & "' and '" & End_Date & "' )"

Form_Completed_Projects.RecordSource = SQLstatement

It works if I am looking for just the name, but when I add the "between the dates" line it gives me "You have canceled the previous operation". What is it that I am not understanding here? The dates in [Actual_Completed] are like "4/1/2008" and I am entering the same format in the start and end fields.


Can anyone point me in the right direction?

Thanks

if it is a date/time field ... then you need to use hash marks around the date ... try this

Ok it works but have another question.

I was told that if you are trying to touch a SQL table you have to replace the hash marks with a single quote for dates. Since this statement has "dbo_cur..." rather than dbo.cur..." I assume that this means it is treating the link to the SQLServer as a table rather than going to the Server itself.

Are there problems with doing this? I have tested it and am able to enter and retrieve records. I know we have to do dim and set statements in the reports we write but is that unneccesary within the Access tables since they have links?

So which is better within Access? Make the code go straight to the Server or let Access use the link as the table? Or am I looking at it all wrong? I dont want it to break down and have to do it over again (and make my boss mad in the process, you wouldn't like her when she gets mad!)

Even with a linked SQL table you are still going directly against the information on your server. A link is dynamic and will show all records, even the most recent items. I think you may be asking about bound and unbound forms.

A bound form is one that is directly linked to your data source, whether that be a query or table. When you make changes to your data in a bound form they are automatic in your tables and require no special steps on the users part to update, add or delete data.

An unbound form is not directly linked to your data source. This means that for everything you want to do with your data you have to provide it with code. All new data has to be added with an append query, any changes have to be performed with an update query, etc.

With an unbound data entry field you, theoretically, do not need to have any of your SQL tables linked into your access DB. Doing things this way also has a tendency to make your db expand a little quicker (how fast depends on how bit your data sets are) as well so your Access front end will need to be compacted regularly. Unless you are super comfortable with the code in access it's really not of any benefit to do things this way.

Your original problem with the date field is a relatively common one because Access does not always handle date/time fields in SQL correctly and tends to link them as text fields unless you specifically modify the link spec to treat it otherwise.