Getting the Quote Marks right....

[susieqtotallynew]

Hi, all,
The following line of VB code is supposed to concatenate fields to create a sql string:

Where = Where & " AND [Year] Between + Me![Year] + And " ""+ Me![Year 2] +""

Year and Year 2 are text fields.

I keep getting errors associated with the quotation marks. Can someone let me know how the quotation marks SHOULD go? I'm cross-eyed looking at it!
Thanks again.

 

[dhookom]

Did you have a specific reason for using + rather than &? Try:

Where = Where & " AND [Year] Between '"  & _
    Me![Year] & "' And '" & Me![Year 2] & "' "

Duane
Hook'D on Access
MS Access MVP

 

[susieqtotallynew]

When I use the following
Where = Where & " AND [Class Year] Between '" & Me![Class Year] & "' And '" & Me![Class Year 2] & "' "
and do not enter anything into the class year or class year 2 fields, the query formats as follows:
Between '' And ''
I would like for NOTHING to show up if there is no entry to the 2 fields.
I think we may be off a little on the quotation marks?

 

[smedvid]

I once has a dynamic query sooooo complex I got lost in the code with all the quotes. To make life easier I used the chr(39) and other chr values...

Also for last names like O'Brien, I would swap out the single quote after the O with ` symbol, which went un-noticed as a change...

htwh,


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[PHV]

I would like for NOTHING to show up if there is no entry to the 2 fields

Where = Where & " AND [Year] Between '" + Me![Year] + "' And '" + Me![Year 2] + "' "

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[smedvid]

I think you want the variable Where, which may not be a good field name to use per reserved names, to be empty if both Me![Year] and Me![Year 2] are empty or null...

If that is the case...

<code>
IF (Me![Year] and Me![Year 2] are empty or null)
Where = ""
ELSE
Where = Where & " AND [Year] Between '" + Me![Year] + "' And '" + Me![Year 2] + "' "
END IF

</code>

You will need to lookup syntax for:
IF (Me![Year] and Me![Year 2] are empty or null)

Remember if you have text fields, users may enter an space to make an empty string which will is not a Null value. So, you can check ISNull or LEN(TRIM([year]))< 2...

htwh,


Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[susieqtotallynew]

I think you misunderstand. This is used to create a SQL statement, in which the WHERE clause is a necessary component.

 

[randy700]

So, simply change
Where = ""
with
Where = Where & ""


Randy