Dates in query (strings) Where do I put the quotes and #tags?

[StuartR]

Hi,



I have a Sub triggered by an on_click event which includes a string SQL ...

strSQL = "Select Member_Names.Membership_Number, Member_Names.Status ... etc ... WHERE ((Year([Last_Payment])) < Year(Date)))"

This filter works fine elsewhere (ie when the query is run as a stand alone query out with the Sub) but it is falling over on the date, as it opens up a dialog box asking for a date to be entered rather than calculating the filter itself which is meant to return every record where the year of the Last_Payment is less than the current year.

As it is a string, I have tried putting single quotes and #tags around the Year(Date) section, but am obviously putting them in the wrong place. [Last_Payment] is also a date field.

Any guidance would be appreciated.

Cheers

Stuart

[Micron]

The best way I can think of explaining the approach when writing sql in vba is, any time there is a variable or object name (such as a control or field) that part needs to be a) properly delimited and b) not contained within the quotes that are used to define the sql you're passing to your sql variable. That variable assignment starts and ends with your double quotes. The delimiters are usually date (#) and string ('). Since double quotes denote the end or start of any string, you don't want to wrap your delimited part with double quotes as it improperly terminates and starts a new string section. Doubles can be used, but it's very messy to do so.

It looks like WHERE ((Year([Last_Payment])) should work as a sql statement passed to Access, but not in vba, because year last payment is being read as some thing named "Year([Last_Payment]" not as a function expression Year([Last_Payment]. This is because it is part of the main string being passed to your sql variable. Parts like this need to be outside of the quoted parts being assigned to your sql variable. You do this for every variable or object name or function part, and string them together with the concatenation symbol (&). So your WHERE part needs to be "SELECT ... #" & Year([Last_Payment] & "# ..."
Sometimes dates are passed as strings.

[June7]

SQL recognizes Year() and Date() functions and evaluates when the query executes. Since both extractions are just numbers not dates, the # delimiters are not needed.

Since you need to extract the year for every record, not just from a control on form, no concatenation needed. Assuming Date is supposed to be the current date, include the () for the function call.

strSQL = "SELECT * FROM table WHERE Year([Last_Payment]) < Year(Date())"

Tested and verified.

Now if you want to allow user to pick a year for filter parameter, use a textbox or combobox to allow this input and then concatenate the variable reference:

strSQL = "SELECT * FROM table WHERE Year([Last_Payment]) < " & Me.cbxYear

[StuartR]

That worked a dream June,

Thanks for your help.

Stuart