In VBA, you delimit a string with quotes, e.g.:
strWhere = "The value goes in quotes."
If you want quotes inside the string, you cannot use:
"This "value" is in quotes in the string"
becuase as soon as the VBA interpreter gets to the quotes before Value it
considers the string is closed and has no idea what to do with the rest of
the line.
The agreed solution is to double-the quotes up inside the string. So, to
get:
This "value" is in quotes in the string
you use:
strWhere = "The ""value"" is in quotes in the string."
If you happen to end the string with the closing quote as well, it looks
like 3 in a row:
strWhere = "The ""value"""
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
LFNFan said:
Genius. Thank you!
Can you explain the need for three (!) sets of quotes around the
Me.cboSeason? Why doesn't (SeasonID = & "Me.cboSeason" & ) do the job?
Paul
Allen Browne said:
If you open your table in design view, what is the Data Type of the
SeasonID
field?
If Number, then it contains a number, even if you see the text "Season 1"
in
the field. To see the value of the Number, in the lower pane in table
design, the Lookup tab, change the Column Widths property so the first
column is not zero-width.
If the field is of type Text, so what you see is what is really there,
then
you need extra quotes:
strWhere = "WHERE (SeasonID = """ & Me.cboSeason & """)"
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Thanks for the help, and sorry for the long hiatus.
I am 'almost there' with this code, however the cboSeason AfterUpdate
event
seems to be giving me a problem - when I run the form, and choose a
value
from the cbo, I get this: 'Run-time error 3075 - Syntax Error (missing
operator) in query expression 'SeasonID = Season 1'.
I was wondering if there should be " "s around the Season 1 ? If I do
put
additional " "s in the code you supplied below ( strWhere = "WHERE
(SeasonID
= " & _"_Me.cboSeason _"_ & ")" ), I am prompted to enter a parameter
when
I make a selection in the cbo. If I enter the seasonID, the records
are
duly
filtered, but I don't understand why it won't take the value from the
cbo?
Hope I've described my problem clearly enough.... Any further advice
much
appreciated.
Best Regards
:
Use the AfterUpdate event of the combos to set the RecordSource
property
of
the subform.
This kind of thing:
Private Sub cboSeason_AfterUpdate()
Dim strWhere As String
Const strcStub = "SELECT * FROM Table1 "
Const strcTail = " ORDER BY Table1.ID;"
If Not IsNull(Me.cboSeason) Then
strWhere = "WHERE (SeasonID = " & Me.cboSeason & ")"
End If
Me.[YourSubformNameHere].Form.RecordSource = strcStub & strWhere &
strcTail
End Sub
Using Access 2003.
I have a form displaying TV show name, season, episode no., title,
synopsis
(all these from one main table) and 'memorable quotes' via a subform
(and
a
related table). The main table has a stack of records, so I created
2
linked
comboboxes on Show name and season, idea being that the user selects
show
and
season which cuts down the records they then have to move through to
arrive
at the target episode, to add/view a 'memorable quote'.
Any guidance on approach to filtering the underlying records in line
with
values of the two linked cbo's would be much appreciated. At
present,
all
records are displayed, not just those relating to the aforementioned
cbo
values....
