SQL/Query for populating various ComboBoxes 1

[Aubs010]

Hopefully this will be easy for someone to answer!!

I have a form called "a" with 4 combos (well, more than that, but if I get the hang of 4, I can do the rest!):
"Property_Address1_Item"
"Property_Address2_Item"
"Property_Address3_Item"
"Property_Town_Item"

I have a table called "tbl_PropertyDetails" with the following fields:
"Property_Address1"
"Property_Address2"
"Property_Address3"
"Property_Town"

I have a query called "quy_SearchCriteria", with all the fields from the above table.

What I need is the form to be populated from the query, but the query to be populated from the form.

For example, if I selected "London" in the Town combo, the other combos will change to only list those properties with "London" in the Town field.

I have been trying with the following SQL:

SELECT tbl_PropertyDetails.Property_Address1, tbl_PropertyDetails.Property_Address2, tbl_PropertyDetails.Property_Address3, tbl_PropertyDetails.Property_Town
FROM tbl_PropertyDetails
WHERE (((tbl_PropertyDetails.Property_Address1)=IIf(IsNull([Forms]![a]![Property_Address1_Item]),[tbl_PropertyDetails].[Property_Address1],[Forms]![a]![Property_Address1_Item]))) OR
 (((tbl_PropertyDetails.Property_Address2)=IIf(IsNull([Forms]![a]![Property_Address2_Item]),[tbl_PropertyDetails].[Property_Address2],[Forms]![a]![Property_Address2_Item]))) OR
 (((tbl_PropertyDetails.Property_Address3)=IIf(IsNull([Forms]![a]![Property_Address3_Item]),[tbl_PropertyDetails].[Property_Address3],[Forms]![a]![Property_Address3_Item]))) OR
 (((tbl_PropertyDetails.Property_Town)=IIf(IsNull([Forms]![a]![Property_Town_Item]),[tbl_PropertyDetails].[Property_Town],[Forms]![a]![Property_Town_Item])))
ORDER BY tbl_PropertyDetails.Property_Address1, tbl_PropertyDetails.Property_Address2, tbl_PropertyDetails.Property_Address3, tbl_PropertyDetails.Property_Town;

I know it is not correct, but I can't see a way of doing it!

I probably need something else, but I can't think of anything!

I'm planning on having a reset button or something to clear the form so they can select a different search criteria.

Any help greatly appreciated.

Aubs

 

[PHV]

Have you tried this ?
SELECT Property_Address1, Property_Address2, Property_Address3, Property_Town
FROM tbl_PropertyDetails
WHERE Property_Address1=Nz([Forms]![a]![Property_Address1_Item],[Forms]![a]![Property_Address1_Item],Property_Address1)
AND Property_Address2=Nz([Forms]![a]![Property_Address2_Item],[Forms]![a]![Property_Address2_Item],Property_Address2)
AND Property_Address3=Nz([Forms]![a]![Property_Address3_Item],[Forms]![a]![Property_Address3_Item],Property_Address3)
AND Property_Town=Nz([Forms]![a]![Property_Town_Item],[Forms]![a]![Property_Town_Item],Property_Town)
ORDER BY 1,2,3,4;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aubs010]

Hi,

Thanks for that PHV, I have tried your suggestion, but kept getting an invalix syntax message.

I have altered your code slightly as:

SELECT tbl_PropertyDetails.Property_Address1, tbl_PropertyDetails.Property_Address2, tbl_PropertyDetails.Property_Address3, tbl_PropertyDetails.Property_Town
FROM tbl_PropertyDetails
WHERE (((tbl_PropertyDetails.Property_Address1)=Nz([Forms]![a]![Property_Address1_Item],[Property_Address1])) AND ((tbl_PropertyDetails.Property_Address2)=Nz([Forms]![a]![Property_Address2_Item],[Property_Address2])) AND ((tbl_PropertyDetails.Property_Address3)=Nz([Forms]![a]![Property_Address3_Item],[Property_Address3])) AND ((tbl_PropertyDetails.Property_Town)=Nz([Forms]![a]![Property_Town_Item],[Property_Town])))
ORDER BY 1,2,3,4;

This still doesn't produce what I want, unfortunately.

If all the fields on the form are blank, then I want it to return all the property details.

If one of the fields has an item selected, then the rest of the fields should only offer choices relating to the field with selected info.

e.g. 3 properties:
[tt]
| Ref | Add1 | Add2 | Add3 | Town |
|-----|---------|---------|-------|-------|
| (1) | House1 | Street1 | Area1 | Town1 |
| (2) | House2 | Street2 | Area2 | Town2 |
| (3) | House3 | Street3 | Area3 | Town1 |
[/tt]

In the above table, if you selected Town as "Town1"
then Add1, Add2, Add3 would only return the info for (1) & (3) because they share the same Town.

I hope this helps

Thanks again,




Aubs

 

[Aubs010]

Right,

Thanks for your help PHV, I think what you gave was right! Slight problem I am having though is that if one of the fields is blank, nothing at all is returned for that record.

From my last example, if we had:
[tt]
| Ref | Add1 | Add2 | Add3 | Town |
|-----|---------|---------|-------|-------|
| (1) | House1 | Street1 | | Town1 |
| (2) | House2 | Street2 | Area2 | Town2 |
| (3) | House3 | Street3 | Area3 | Town1 |
[/tt]

When I use the following code:

SELECT tbl_PropertyDetails.Property_Address1, tbl_PropertyDetails.Property_Address2, tbl_PropertyDetails.Property_Address3, tbl_PropertyDetails.Property_Town
FROM tbl_PropertyDetails
GROUP BY tbl_PropertyDetails.Property_Address1, tbl_PropertyDetails.Property_Address2, tbl_PropertyDetails.Property_Address3, tbl_PropertyDetails.Property_Town
HAVING (((tbl_PropertyDetails.Property_Address1)=nz([Forms]![a]![Property_Address1_Item],[tbl_PropertyDetails].[Property_Address1])) AND ((tbl_PropertyDetails.Property_Address2)=nz([Forms]![a]![Property_Address2_Item],[tbl_PropertyDetails].[Property_Address2])) AND ((tbl_PropertyDetails.Property_Address3)=nz([Forms]![a]![Property_Address3_Item],[tbl_PropertyDetails].[Property_Address3])) AND ((tbl_PropertyDetails.Property_Town)=nz([Forms]![a]![Property_Town_Item],[tbl_PropertyDetails].[Property_Town])));

Because Add3 in Ref(1) is blank, it doesn't return Ref(1), only Ref's (2) & (3)

Is there a simple way round it?

Thanks in advance



Aubs

 

[PHV]

Have you tried something like this ?
WHERE (
((Property_Address1 IS NULL AND Len(Trim(Nz([Forms]![a]![Property_Address1_Item],"")))=0)
OR Property_Address1=Nz([Forms]![a]![Property_Address1_Item],""))
AND ((Property_Address2 IS NULL AND Len(Trim(Nz([Forms]![a]![Property_Address2_Item],"")))=0)
OR Property_Address2=Nz([Forms]![a]![Property_Address2_Item],""))
...
)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aubs010]

Your help is much apreciated PHV.

Unfortunately, when I used:

SELECT tbl_PropertyDetails.Property_Address1, tbl_PropertyDetails.Property_Address2, tbl_PropertyDetails.Property_Address3, tbl_PropertyDetails.Property_Town
FROM tbl_PropertyDetails
WHERE (((Property_Address1 IS NULL AND Len(Trim(Nz([Forms]![a]![Property_Address1_Item],"")))=0) OR Property_Address1=Nz([Forms]![a]![Property_Address1_Item],"")) AND ((Property_Address2 IS NULL AND Len(Trim(Nz([Forms]![a]![Property_Address2_Item],"")))=0) OR Property_Address2=Nz([Forms]![a]![Property_Address2_Item],""))AND ((Property_Address3 IS NULL AND Len(Trim(Nz([Forms]![a]![Property_Address3_Item],"")))=0) OR Property_Address3=Nz([Forms]![a]![Property_Address3_Item],"")) AND ((Property_Town IS NULL AND Len(Trim(Nz([Forms]![a]![Property_Town_Item],"")))=0) OR Property_Town=Nz([Forms]![a]![Property_Town_Item],"")));

and viewed it, it didn't return anything at all (I mean it only displayed the column names in result of the query) - Whether or not the form was open... I think it may have something to do with Nz? (Might be completely wrong!!)

By the way, is this the best forum to ask the question, or should I use another one, such as forms etc?

Thanks again



Aubs

 

[PHV]

And what about this ?
WHERE (
(IsNull([Forms]![a]![Property_Address1_Item]) OR
Property_Address1=Nz([Forms]![a]![Property_Address1_Item],""))
AND (IsNull([Forms]![a]![Property_Address2_Item]) OR
Property_Address2=Nz([Forms]![a]![Property_Address2_Item],""))
AND (IsNull([Forms]![a]![Property_Address3_Item]) OR
Property_Address3=Nz([Forms]![a]![Property_Address3_Item],""))
AND (IsNull([Forms]![a]![Property_Town_Item]) OR
Property_Town=Nz([Forms]![a]![Property_Town_Item],""))
);

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Aubs010]

My god, thank you PH, that worked!!!

I'm now trying to implement your code with the remaining fields.

Thanks ever so much for your help, it has been appreciated.

Here, have a

on me!

Thanks again,



Aubs