Query is failing in Runtime

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***********
 
K

Ken Snell \(MVP\)

Assuming that the unique field is CustomerID, and that is really the only
field on which you need to group, let's try a slight modification to the
query that uses First aggregate function to eliminate the need to group on
all those other fields (essentially, make them "lookup" fields); and let's
use Nz function for the PhoneHome field to replace a Null with a default
string value:

SELECT Customers.CustomerID, FIrst([LastName] & ', ' & [FirstName] &
IIf(IsNull([MI]),'',' ' & [MI])) AS LastFirst, First([Address1] &
IIf(IsNull([Address2]),"",", " & [Address2])) AS Address,
First(Customers.City) AS TheCity,
First(Customers.State) AS TheState, First([City] & ", " & [State] & " " &
fCustomers_FormattedZip([Country],[Zip])) AS CityStateZip,
First(Format(Nz([PhoneHome],"0000000000"),"(000) 000-0000")) AS Phone,
First(Customers.PhoneHome) AS ThePhoneHome,
First(Customers.PrimaryEmail) AS ThePrimaryEmail,
First(Customers.Active) AS TheActive,
First(Customers.ContactOnly) AS TheContactOnly,
First(Customers.PotentialHost) AS ThePotentialHost,
First(Customers.PotentialRecruit) AS ThePotentialRecruit,
First(Customers.Demonstrator) AS TheDemonstrator,
First(Customers.MailingList) AS TheMailingList,
First(Customers.EmailList) AS TheEmailList,
Count(Events.Hostess) AS Host
FROM Customers LEFT JOIN Events ON Customers.CustomerID = Events.Hostess
GROUP BY Customers.CustomerID;

--

Ken Snell
<MS ACCESS MVP>


Tony Vrolyk said:
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***********
Click to expand...
 
T

Tony Vrolyk

Sorry for the late response. I also use the Sagekey Security module which is
used to lock access to the program so I can distribute it as a trial and
then require they request an unlock code to continue using the program. It
turns out that is causing the problem and I have contacted Sagekey for help.

Tony



Ken Snell (MVP) said:
Assuming that the unique field is CustomerID, and that is really the only
field on which you need to group, let's try a slight modification to the
query that uses First aggregate function to eliminate the need to group on
all those other fields (essentially, make them "lookup" fields); and let's
use Nz function for the PhoneHome field to replace a Null with a default
string value:

SELECT Customers.CustomerID, FIrst([LastName] & ', ' & [FirstName] &
IIf(IsNull([MI]),'',' ' & [MI])) AS LastFirst, First([Address1] &
IIf(IsNull([Address2]),"",", " & [Address2])) AS Address,
First(Customers.City) AS TheCity,
First(Customers.State) AS TheState, First([City] & ", " & [State] & " " &
fCustomers_FormattedZip([Country],[Zip])) AS CityStateZip,
First(Format(Nz([PhoneHome],"0000000000"),"(000) 000-0000")) AS Phone,
First(Customers.PhoneHome) AS ThePhoneHome,
First(Customers.PrimaryEmail) AS ThePrimaryEmail,
First(Customers.Active) AS TheActive,
First(Customers.ContactOnly) AS TheContactOnly,
First(Customers.PotentialHost) AS ThePotentialHost,
First(Customers.PotentialRecruit) AS ThePotentialRecruit,
First(Customers.Demonstrator) AS TheDemonstrator,
First(Customers.MailingList) AS TheMailingList,
First(Customers.EmailList) AS TheEmailList,
Count(Events.Hostess) AS Host
FROM Customers LEFT JOIN Events ON Customers.CustomerID = Events.Hostess
GROUP BY Customers.CustomerID;

--

Ken Snell
<MS ACCESS MVP>


Tony Vrolyk said:
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***********
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Combox box value used in Query 1
Stored procedure exception in .NET works fine in Query Analyzer 9

Top