UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Text Box Filter    
 
   
MeMedea
post Jun 1 2009, 08:51 PM
Post#1



Posts: 243
Joined: 26-February 07
From: Ohio


Hello,
I have a listbox that filters a report based on a text field. When I click the command button, I get a Missing syntax error. I think the error is in my strwhere - putting either " ' in the wrong spot!
THere is the code.
**********************************************
Dim strWhere As String, varItem As Variant
' Request to edit items selected in the list box
' If no items selected, then nothing to do

If IsNull(Me.txt_Report) Then
MsgBox "Please select a report.", vbOKOnly, "CRS"

Else
If Me!lst_City.ItemsSelected.Count = 0 Then
MsgBox "Please select a city.", vbOKOnly, "CRS"
'End If
Exit Sub
Else
' Loop through the items selected collection
For Each varItem In Me!lst_City.ItemsSelected
' Grab the City Name column for each selected item
strWhere = strWhere & Me!lst_City.Column(0, varItem) & "','"
Next varItem
' Throw away the extra comma on the "IN" string
strWhere = Left$(strWhere, Len(strWhere) - 1)
' Open the Report form filtered on the selected city
strWhere = "[City] IN (" & strWhere & ") "
DoCmd.OpenReport Me.txt_Report, acPreview, , WhereCondition:=strWhere
End If
End If

ExitHandler:
Exit Sub
ErrorHandler:
' Output to Error message
If Err = 2501 Then
Resume ExitHandler

Else
MsgBox Err.Description
Resume ExitHandler
End If

End Sub
***************************************************************
Any suggestions?
Thanks,
Trish
Go to the top of the page
 
Larry Larsen
post Jun 2 2009, 02:23 AM
Post#2


UA Editor + Utterly Certified
Posts: 24,350
Joined: 26-August 02
From: Melton Mowbray,Leicestershire (U.K)


Hi
You seem to have extra quotes in here:
CODE
trWhere = strWhere & Me!lst_City.Column(0, varItem) & "','"

ry:
CODE
trWhere = strWhere & Me!lst_City.Column(0, varItem) & ","

thumbup.gif
Go to the top of the page
 
MeMedea
post Jun 2 2009, 03:35 PM
Post#3



Posts: 243
Joined: 26-February 07
From: Ohio


Hello,
Thank you. The code you provided (" . ") works when I use a numeric value. I'm trying to use this one with a text field so I know I need to change the quotes, just not sure where!!!
I'll keep playing with it.
Thanks again!
Trish
Go to the top of the page
 
Jack Cowley
post Jun 2 2009, 04:04 PM
Post#4


Retired Moderator
Posts: 37,716
Joined: 11-February 00
From: The San Francisco Bay Area


Is your City list box actually using a CityName to find the City or are you using the PK of the City? If you have a table/List Box of Cities then each City should have a PK and that is the value that should be stored in the related tables and your IN() code should work. Your strWhere string should look like: 3,7,9,11 or does it look like:"Baltimore","New York", "Cleveland"?
Yur City list box actually using a CityName to find the City or are you using the PK of the City? If you have a table/List Box of Cities then each City should have a PK and that is the value that should be stored in the related tables and your IN() code should work. Your strWhere string should look like: 3,7,9,11 or does it look like:"Baltimore","New York", "Cleveland"?
Your City table should look like:
tblCities
CityID (PK and auto)
CityName
Your list box should have two columns, the first being CityID and the second CityName an you should be searching on CityID...
hth,
Jack
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    16th June 2019 - 07:44 PM