dbTalk Databases Forums  

Help with OpenRecordset("Select * From tblReservations Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ")

Go Back dbTalk Databases Forums Databases comp.databases.ms-access Help with OpenRecordset("Select * From tblReservations Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ")

comp.databases.ms-access comp.databases.ms-access


Discuss Help with OpenRecordset("Select * From tblReservations Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") in the comp.databases.ms-access forum.



Reply
Page 2 of 2 < 1 2
 
Thread Tools Display Modes
  #11  
Old   
PW
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-27-2011 , 06:41 PM





On Thu, 27 Jan 2011 14:25:01 -0800 (PST), imb <imb4u (AT) onsmail (DOT) nl>
wrote:

Quote:
On Jan 27, 10:22*pm, Marshall Barton <marshbar... (AT) wowway (DOT) com> wrote:
PW wrote:
On Wed, 26 Jan 2011 12:48:45 -0800 (PST), XPS350 <xps... (AT) gmail (DOT) com
wrote:

On 26 jan, 21:14, PW <emailaddyin... (AT) ifIremember (DOT) com> wrote:
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

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)

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.

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

--
Marsh- Hide quoted text -

- Show quoted text -

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.

Thanks, never seen that before. I don't have that function (A2003).

-pw

Reply With Quote
  #12  
Old   
imb
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations WhereYear([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-28-2011 , 02:27 AM





Quote:
Thanks, never seen that before. I don't have that function (A2003).
Hi Paul,

Well, make it!

Function As_text (ThisText As String) As String
As_text = "'" & ThisText & "'"
' This reads as: " ' " & ThisText & " ' "
End Function

and place it in a Module.


Imb.

Reply With Quote
  #13  
Old   
Phil
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-28-2011 , 05:22 PM


On 28/01/2011 08:27:01, imb wrote:
Quote:
Thanks, never seen that before. I don't have that function (A2003).


Hi Paul,

Well, make it!

Function As_text (ThisText As String) As String
As_text = "'" & ThisText & "'"
' This reads as: " ' " & ThisText & " ' "
End Function

and place it in a Module.


Imb.
Hi Imb
For robustness & readability, I think this is better.

Function As_text (ThisText As String) As String
If IsNull(ThisText) = False Then
If InStr(ThisText, Chr(34)) > 0 Then
ThisText = ChangeChars(CStr(ThisText), Chr(34), "'")
End If
End If

As_text = Chr$(34) & ThisText & Chr$(34)
End Function

I hate quotes

Phil

Reply With Quote
  #14  
Old   
imb
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations WhereYear([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-28-2011 , 05:41 PM


Quote:
I hate quotes
Me too!
Except for that ONE time to hide them in a function as As_text, and
never to use them anymore.


Imb.

Reply With Quote
  #15  
Old   
imb
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations WhereYear([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-28-2011 , 05:52 PM


On Jan 29, 12:22*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Quote:
On 28/01/2011 08:27:01, imb wrote:







Thanks, never seen that before. I don't have that function (A2003).

Hi Paul,

Well, make it!

Function As_text (ThisText As String) As String
As_text = "'" & ThisText & "'"
' This reads as: " ' " & ThisText & " ' "
End Function

and place it in a Module.

Imb.

Hi Imb
For robustness & readability, I think this is better.

Function As_text (ThisText As String) As String
* *If IsNull(ThisText) = False Then
* * * If InStr(ThisText, Chr(34)) > 0 Then
* * * * *ThisText = ChangeChars(CStr(ThisText), Chr(34), "'")
* * * End If
* *End If

* *As_text = Chr$(34) & ThisText & Chr$(34)
End Function

I hate quotes

Phil- Hide quoted text -

- Show quoted text -
Hi Phil,

Some comments on your working-out of the As_text function.

- Indeed, the function was simplified to ease a quick start.
- if ThisText is a String, it has no meaning to test for a Null-value.
- In order to conserve single quotes in ThisText, you can better
double them (two single quotes) instead of removing them.

Imb.

Reply With Quote
  #16  
Old   
Phil
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations Where Year([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-30-2011 , 12:46 PM


On 28/01/2011 23:52:13, imb wrote:
Quote:
On Jan 29, 12:22*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
On 28/01/2011 08:27:01, imb wrote:







Thanks, never seen that before. I don't have that function (A2003).

Hi Paul,

Well, make it!

Function As_text (ThisText As String) As String
As_text = "'" & ThisText & "'"
' This reads as: " ' " & ThisText & " ' "
End Function

and place it in a Module.

Imb.

Hi Imb
For robustness & readability, I think this is better.

Function As_text (ThisText As String) As String
* *If IsNull(ThisText) = False Then
* * * If InStr(ThisText, Chr(34)) > 0 Then
* * * * *ThisText = ChangeChars(CStr(ThisText), Chr(34), "'")
* * * End If
* *End If

* *As_text = Chr$(34) & ThisText & Chr$(34)
End Function

I hate quotes

Phil- Hide quoted text -

- Show quoted text -

Hi Phil,

Some comments on your working-out of the As_text function.

- Indeed, the function was simplified to ease a quick start.
- if ThisText is a String, it has no meaning to test for a Null-value.
- In order to conserve single quotes in ThisText, you can better
double them (two single quotes) instead of removing them.

Imb.
Hi Imb

Can't remember the exact circumstances, but we had a lady called Mrs O'Hara.
The trouble I had with something like SELECT MyTable.* FROM MyTable where
SurName = '" & SurName & "';" Winges about 3 single quotes. So any field with
a single or double quote can be a pain in the ar.. to sort out.

Love the Irish really
Phil

Reply With Quote
  #17  
Old   
imb
 
Posts: n/a

Default Re: Help with OpenRecordset("Select * From tblReservations WhereYear([date_arriv]) = & intYear1 Or year([date_arriv]) = & intYear2 ") - 01-30-2011 , 02:46 PM


On Jan 30, 7:46*pm, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
Quote:
On 28/01/2011 23:52:13, imb wrote:





On Jan 29, 12:22*am, "Phil" <p... (AT) stantonfamily (DOT) co.uk> wrote:
On 28/01/2011 08:27:01, imb wrote:

Thanks, never seen that before. I don't have that function (A2003).

Hi Paul,

Well, make it!

Function As_text (ThisText As String) As String
As_text = "'" & ThisText & "'"
' This reads as: " ' " & ThisText & " ' "
End Function

and place it in a Module.

Imb.

Hi Imb
For robustness & readability, I think this is better.

Function As_text (ThisText As String) As String
* *If IsNull(ThisText) = False Then
* * * If InStr(ThisText, Chr(34)) > 0 Then
* * * * *ThisText = ChangeChars(CStr(ThisText), Chr(34), "'")
* * * End If
* *End If

* *As_text = Chr$(34) & ThisText & Chr$(34)
End Function

I hate quotes

Phil- Hide quoted text -

- Show quoted text -

Hi Phil,

Some comments on your working-out of the As_text function.

- Indeed, the function was simplified to ease a quick start.
- if ThisText is a String, it has no meaning to test for a Null-value.
- In order to conserve single quotes in ThisText, you can better
double them (two single quotes) instead of removing them.

Imb.

Hi Imb

Can't remember the exact circumstances, but we had a lady called Mrs O'Hara.
The trouble I had with something like SELECT MyTable.* FROM MyTable where
SurName = '" & SurName & "';" Winges about 3 single quotes. So any field with
a single or double quote can be a pain in the ar.. to sort out.

Love the Irish really
Phil- Hide quoted text -

- Show quoted text -
Hi Phil,

No problems with Irish either, and no problems at all with their
single quotes after using As_text (including the conversion of each
single to two of them).

By the way, a similar function As_date I use to never bother any more
with date formats in SQL-stirngs, or As_real to convert the european
decimal comma to a decimal point.

Imb.

Reply With Quote
Reply
Page 2 of 2 < 1 2

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top