Dim intYear1 As Integer
Dim intYear2 As Integer

intYear1 = Me.txtYear
intYear2 = intYear1 - 1

Set db = CurrentDb()

Set rstSuppressList = db.OpenRecordset("Select * From tblReservations
Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = &
intYear2 ")

With the above I am getting an error "can't find the field '|'
referred to in your expression" with the above statement.

intYear(2011) is an integer as is intYear2. date_arriv is a date
field in table tblReservations.

How do I get the OR statement to work in this case? I want all
records where the year is 2011 or 2010 in this instance.

What am I doing wrong? I need the " at the end of the Select
statement, right? I have fooled around with various combinations and
can't get it to work.

Any ideas?

-paulw

On 26 jan, 21:14, PW <emailaddyin... (AT) ifIremember (DOT) com> wrote:
Because intYear1 and intYear2 are variables they should not be
included in a string between " ".

Try:
"Select * From tblReservations Where Year([date_arriv]) =" & intYear1
& " Or year([date_arriv]) =" & intYear2)

Groeten,

Peter
http://access.xps350.com

On Wed, 26 Jan 2011 12:48:45 -0800 (PST), XPS350 <xps350 (AT) gmail (DOT) com>
wrote:


Thanks Peter. It worked.

The only book that I have (out of a dozen or so) that even attempts to
explain syntax is Getz's 97 Developers Guide (I using Access 2003)
and it doesn't do much there. I just can't get it through my head.

I don't understan why the "& before intYear1 and why the &" after it
but no ending quotes after intYear2.

-paul

PW wrote:


You build this string with fixed parts and variable parts (the years).
So the parts are:
- "Select * From tblReservations Where Year([date_arriv]) ="
- intYear1
- " Or year([date_arriv]) ="
- intYear2

The parts are connected with &.

In the "& before intYear1, " is ending the string starting with Select
and the & is connecting this part to the next part (intYear1).

Groeten,

Peter
http://access.xps350.com

--
Groeten,

Peter
http://access.xps350.com


--- news://freenews.netfront.net/ - complaints: news (AT) netfront (DOT) net ---

On Wed, 26 Jan 2011 12:48:45 -0800 (PST), XPS350 <xps350 (AT) gmail (DOT) com>
wrote:


Now I have to add another OR clause to:

Set rstSuppressList = db.OpenRecordset("Select * From tblReservations
Where Year([date_arriv]) =" & intYear1 & " Or year([date_arriv]) =" &
intYear2):

strStatus = "Q"

Adding:

OR "[status] = '" & strStatus & " ' "

does not work.

Any ideas?

-paulw

On 27 jan, 19:32, PW <emailaddyin... (AT) ifIremember (DOT) com> wrote:
" OR [status] = '" & strStatus & " ' "

or

" OR [status] = 'Q'"

On 27 jan, 19:32, PW <emailaddyin... (AT) ifIremember (DOT) com> wrote:
Sorry. Adding an extra part requires an extra & aswell

& " OR [status] = '" & strStatus & " ' "

or

& " OR [status] = 'Q'"

Are you sure this is what you want? It means any record with status Q
will be selected. No matter which year.

PW wrote:


THere's an extra space and a quote in the wrong place:
" OR [status] = '" & strStatus & "' "

--
Marsh

On Jan 27, 10:22*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
Readability (and robustness) is very much enhanced by using a simple
function:

“ OR [Status] = “ & As_text(strStatus)

The function As_text just returns strStatus surrounded with single
quotes.


Imb.

On Thu, 27 Jan 2011 13:17:08 -0800 (PST), XPS350 <xps350 (AT) gmail (DOT) com>
wrote:

I do not understand why the & and " before the OR status!! Seems
crazy to me!

It appears to be working though.

That is what my wife tells me! I would love to prove her wrong
though. She hates that :-)

-paulw