Query Problems!

[darthsiddeous]

I'm a junior programmer trying to get into a company and they sent me a database to work on. I can't get the queries to work. I'll give a sample of what is there. Could someone please explain what it's doing?? Thank you!!<br>
<br>
SELECT (TblMembership.PrefixMember) & UCase(TblMembership.LastName) & &quot;, &quot; & StrConv(TblMembership.FirstName,3) & &quot; &quot; & IIf(IsNull(TblMembership.MiddleName),&quot;&quot;,UCase(Mid(Trim(TblMembership.MiddleName),1,1)) & &quot;. &quot AS NAME, IIf(IsNull(TblAddress.Address_1),&quot;&quot;,TblAddress.Address_1) AS Address, IIf(IsNull(TblAddress.Address_2),&quot;&quot;,TblAddress.Address_2) AS Address, IIf(IsNull(TblAddress.Address_3),&quot;&quot;,TblAddress.Address_3) AS Address1, IIf(IsNull(TblAddress.City),&quot;&quot;,TblAddress.City & &quot;, &quot & IIf(IsNull(TblAddress.[Province/State]),&quot;&quot;,TblAddress.[Province/State] & &quot;, &quot & IIf(IsNull(TblAddress.Country),&quot;&quot;,TblAddress.Country & &quot; &quot & IIf(IsNull(TblAddress.[Postal/Zip]),&quot;&quot;,TblAddress.[Postal/Zip]) AS Address2, IIf(IsNull(TblAddress.[Area_Code]),&quot;&quot;,TblAddress.[Area_Code] & &quot;-&quot & IIf(IsNull(TblAddress.Telephone),&quot;&quot;,TblAddress.Telephone & &quot;; &quot & IIf(IsNull(TblAddress.Extension),&quot;&quot;,&quot;Ext. &quot; & TblAddress.Extension & &quot;, &quot & IIf(IsNull(TblAddress.Fax),&quot;&quot;,&quot;Fax: &quot; & TblAddress.Fax) AS Phone, IIf(IsNull(TblAddress.[E-mail]),&quot;&quot;,&quot;E-mail: &quot; & TblAddress.[E-mail]) AS Email<br>
FROM TblMembership, TblAddress, TblMember_Type<br>
WHERE (((TblMembership.MemberID)=[TblAddress].[MemberID] And (TblMembership.MemberID)=[TblMember_Type].[MemberID]) AND ((TblAddress.AdressType) In (&quot;Primary&quot;,&quot;Secondary&quot) AND ((TblMember_Type.MemberType)=&quot;Member&quot);<br>
<br>
<br>

 

[DougP]

Is this a query made with the query design grid<br>
Or <br>
Is this code you cut and pasted from a function or sub.<br>
I pasted it in my sample db <br>
and can see it <br>
I had to create 3 tables <br>
also there is a conflict on this portion below<br>
<br>
TblAddress.Address_2) AS Address, <br>
<br>
the error reads 'duplicate output alias Address'<br>
Which means Address is in there twice, I just re-named it to Address4<br>
Is that you problem.<br>
I'm not sure what it's propose is.<br>
It is a select query so it will not modify anything if you run it.<br>
Try creating a new query if it coming from code<br>
then click the SQL tab and paste all of this code in there.<br>
&quot;IIf Isnull&quot; means if a field is NULL (blank to you and me)<br>
then substitute another value in it<br>
i.e. <br>
Email: IIf(IsNull(TblAddress.[E-mail]),&quot;&quot;,&quot;E-mail: &quot; & TblAddress.[E-mail])<br>
<br>
this line is saying if TblAddress.[E-mail] is NULL then make the e-mail field blank which to Access is &quot;&quot; <br>
Else make the e-mail equal TblAddress.[E-mail]<br>
Now this is a lot of code here for which there is an easier answer.<br>
simply open the table and find the e-mail field and set the<br>
&quot;Allow zero length&quot; property to &quot;Yes&quot; instead of &quot;NO&quot;<br>
If you do that to all of the fields then your query will be about half as much code.<br>
the default setting is NO for this property and in some case you want to use it so the user has to fill in something.<br>
But I usually make it yes especially like in this case the Programmer wrote a lot of code to go around it.<br>
<br>
<br>
<br>

 

[JimHorton]

Darth,<br>
Don't forget to either explicitely initialize, or better, set Default Value on those &quot;Allow Zero Length&quot; fields to &quot;&quot; (empty string), else the Default Value will be Null, not &quot;&quot;.<br>
--Jim