T
Tony Vrolyk
Access 2002, distributed an MDE and ran under Access Runtime 2002.
I have a query that I call to fill a list box. The form has textboxes and
combo boxes that the users changes to filter the list box. From the controls
I query the query to fill the list box.
Up until this version of my program it has worked fine. However now the
query is failing and the listbox is empty regardless of the options the user
selects. I have made changes to the app for this version but nothing
directly to the underlying tables and not even to the form where the problem
shows up.
I suspected that a number of calculated fields were causing the problem.
They are mostly other fields aggregated together - i.e. City, State Zip - or
formatted fields - formatting phone from just numbers to include
punctuation. So I removed them all and tested - it worked. The I started
adding them back in one by one and testing. I found that when I add the
formatted phone field back in that is when it fails. This seems odd since
there is nothing fancy in that field or the format command. I am thinking
that the formatted field is not the problem but is a symptom. But I have no
idea where to look right now so here I am asking for suggestions.
BTW I did import all objects to a new MDB but still have the same problem.
Below are both the query and the code where the query is called. Sorry they
are both long.
Thanks
Tony V
********QUERY BEGIN********
SELECT Customers.CustomerID, [LastName] & ', ' & [FirstName] &
IIf(IsNull([MI]),'',' ' & [MI]) AS LastFirst, [Address1] &
IIf(IsNull([Address2]),"",", " & [Address2]) AS Address, Customers.City,
Customers.State, [City] & ", " & [State] & " " &
fCustomers_FormattedZip([Country],[Zip]) AS CityStateZip,
Format([PhoneHome],"(000) 000-0000") AS Phone, Customers.PhoneHome,
Customers.PrimaryEmail, Customers.Active, Customers.ContactOnly,
Customers.PotentialHost, Customers.PotentialRecruit, Customers.Demonstrator,
Customers.MailingList, Customers.EmailList, Count(Events.Hostess) AS Host
FROM Customers LEFT JOIN Events ON Customers.CustomerID = Events.Hostess
GROUP BY Customers.CustomerID, [LastName] & ', ' & [FirstName] &
IIf(IsNull([MI]),'',' ' & [MI]), [Address1] & IIf(IsNull([Address2]),"",", "
& [Address2]), Customers.City, Customers.State, [City] & ", " & [State] & "
" & fCustomers_FormattedZip([Country],[Zip]), Format([PhoneHome],"(000)
000-0000"), Customers.PhoneHome, Customers.PrimaryEmail, Customers.Active,
Customers.ContactOnly, Customers.PotentialHost, Customers.PotentialRecruit,
Customers.Demonstrator, Customers.MailingList, Customers.EmailList;
********QEURY END********
In the query if I remove "Format([PhoneHome],"(000) 000-0000") AS Phone" it
works. Put it back in and it fails. Since the phone field is just text I
also tried it as "Format([PhoneHome],"(@@@) @@@-@@@@") AS Phone" but it
still fails.
'**********CODE BEGIN***********
Private Sub lst_Fill(SearchString As String)
strSelect = "SELECT CustomerID, LastFirst, Address, CityStateZip, Phone,
PrimaryEmail, Active, ContactOnly FROM qry_Customers_Selector"
'WHERE
strWhere = " WHERE (LastFirst) Like '*" & SearchString & "*'"
If Not IsNull(Me.cboCity) Then strWhere = strWhere & " AND (City) Like
'*" & Me.cboCity & "*'"
If Not IsNull(Me.cboState) Then strWhere = strWhere & " AND (State) Like
'*" & Me.cboState & "*'"
Select Case Me.cboType
Case "Active"
strWhere2 = " AND (Active)=-1 AND (ContactOnly)=0"
Case "Inactive"
strWhere2 = " AND (Active)=0 AND (ContactOnly)=0"
Case "Mailing List"
strWhere2 = " AND (MailingList)=-1 AND (ContactOnly)=0"
Case "Email List"
strWhere2 = " AND (EmailList)=-1 AND (ContactOnly)=0"
Case "Potential Hosts"
strWhere2 = " AND (PotentialHost)=-1 AND (ContactOnly)=0"
Case "Hosts"
strWhere2 = " AND (Host)>0 AND (ContactOnly)=0"
Case "Potential Recruits"
strWhere2 = " AND (PotentialRecruit)=-1 AND (ContactOnly)=0"
Case "Demonstrators"
strWhere2 = " AND (Demonstrator)=-1 AND (ContactOnly)=0"
Case "Contact Only"
strWhere2 = " AND (ContactOnly)=-1"
Case Else
strWhere2 = ""
End Select
'ORDER BY
Select Case Me.frmSort
Case 1 'Name
strOrder = " ORDER BY LastFirst"
Case 2 'Address
strOrder = " ORDER BY Address, LastFirst"
Case 3 'City State, Zip
strOrder = " ORDER BY CityStateZip, LastFirst"
Case 4 'Phone
strOrder = " ORDER BY Phone, LastFirst"
Case 5 'Mobile
strOrder = " ORDER BY PrimaryEmail, LastFirst"
End Select
strListSource = strSelect & strWhere & strWhere2 & strOrder
Me.lstCustomers.RowSource = strListSource
Me.txtSql = strListSource
Me.ListCount = Me.lstCustomers.ListCount
fCustomers_Selector_Buttons
End Sub
'**********CODE END***********
I have a query that I call to fill a list box. The form has textboxes and
combo boxes that the users changes to filter the list box. From the controls
I query the query to fill the list box.
Up until this version of my program it has worked fine. However now the
query is failing and the listbox is empty regardless of the options the user
selects. I have made changes to the app for this version but nothing
directly to the underlying tables and not even to the form where the problem
shows up.
I suspected that a number of calculated fields were causing the problem.
They are mostly other fields aggregated together - i.e. City, State Zip - or
formatted fields - formatting phone from just numbers to include
punctuation. So I removed them all and tested - it worked. The I started
adding them back in one by one and testing. I found that when I add the
formatted phone field back in that is when it fails. This seems odd since
there is nothing fancy in that field or the format command. I am thinking
that the formatted field is not the problem but is a symptom. But I have no
idea where to look right now so here I am asking for suggestions.
BTW I did import all objects to a new MDB but still have the same problem.
Below are both the query and the code where the query is called. Sorry they
are both long.
Thanks
Tony V
********QUERY BEGIN********
SELECT Customers.CustomerID, [LastName] & ', ' & [FirstName] &
IIf(IsNull([MI]),'',' ' & [MI]) AS LastFirst, [Address1] &
IIf(IsNull([Address2]),"",", " & [Address2]) AS Address, Customers.City,
Customers.State, [City] & ", " & [State] & " " &
fCustomers_FormattedZip([Country],[Zip]) AS CityStateZip,
Format([PhoneHome],"(000) 000-0000") AS Phone, Customers.PhoneHome,
Customers.PrimaryEmail, Customers.Active, Customers.ContactOnly,
Customers.PotentialHost, Customers.PotentialRecruit, Customers.Demonstrator,
Customers.MailingList, Customers.EmailList, Count(Events.Hostess) AS Host
FROM Customers LEFT JOIN Events ON Customers.CustomerID = Events.Hostess
GROUP BY Customers.CustomerID, [LastName] & ', ' & [FirstName] &
IIf(IsNull([MI]),'',' ' & [MI]), [Address1] & IIf(IsNull([Address2]),"",", "
& [Address2]), Customers.City, Customers.State, [City] & ", " & [State] & "
" & fCustomers_FormattedZip([Country],[Zip]), Format([PhoneHome],"(000)
000-0000"), Customers.PhoneHome, Customers.PrimaryEmail, Customers.Active,
Customers.ContactOnly, Customers.PotentialHost, Customers.PotentialRecruit,
Customers.Demonstrator, Customers.MailingList, Customers.EmailList;
********QEURY END********
In the query if I remove "Format([PhoneHome],"(000) 000-0000") AS Phone" it
works. Put it back in and it fails. Since the phone field is just text I
also tried it as "Format([PhoneHome],"(@@@) @@@-@@@@") AS Phone" but it
still fails.
'**********CODE BEGIN***********
Private Sub lst_Fill(SearchString As String)
strSelect = "SELECT CustomerID, LastFirst, Address, CityStateZip, Phone,
PrimaryEmail, Active, ContactOnly FROM qry_Customers_Selector"
'WHERE
strWhere = " WHERE (LastFirst) Like '*" & SearchString & "*'"
If Not IsNull(Me.cboCity) Then strWhere = strWhere & " AND (City) Like
'*" & Me.cboCity & "*'"
If Not IsNull(Me.cboState) Then strWhere = strWhere & " AND (State) Like
'*" & Me.cboState & "*'"
Select Case Me.cboType
Case "Active"
strWhere2 = " AND (Active)=-1 AND (ContactOnly)=0"
Case "Inactive"
strWhere2 = " AND (Active)=0 AND (ContactOnly)=0"
Case "Mailing List"
strWhere2 = " AND (MailingList)=-1 AND (ContactOnly)=0"
Case "Email List"
strWhere2 = " AND (EmailList)=-1 AND (ContactOnly)=0"
Case "Potential Hosts"
strWhere2 = " AND (PotentialHost)=-1 AND (ContactOnly)=0"
Case "Hosts"
strWhere2 = " AND (Host)>0 AND (ContactOnly)=0"
Case "Potential Recruits"
strWhere2 = " AND (PotentialRecruit)=-1 AND (ContactOnly)=0"
Case "Demonstrators"
strWhere2 = " AND (Demonstrator)=-1 AND (ContactOnly)=0"
Case "Contact Only"
strWhere2 = " AND (ContactOnly)=-1"
Case Else
strWhere2 = ""
End Select
'ORDER BY
Select Case Me.frmSort
Case 1 'Name
strOrder = " ORDER BY LastFirst"
Case 2 'Address
strOrder = " ORDER BY Address, LastFirst"
Case 3 'City State, Zip
strOrder = " ORDER BY CityStateZip, LastFirst"
Case 4 'Phone
strOrder = " ORDER BY Phone, LastFirst"
Case 5 'Mobile
strOrder = " ORDER BY PrimaryEmail, LastFirst"
End Select
strListSource = strSelect & strWhere & strWhere2 & strOrder
Me.lstCustomers.RowSource = strListSource
Me.txtSql = strListSource
Me.ListCount = Me.lstCustomers.ListCount
fCustomers_Selector_Buttons
End Sub
'**********CODE END***********