Dropdown with Union Query on form not working

[pavarga]

I have a form that i want to allow searching on several fields. I have dropdowns set up for this. All work fine except one. It is for Parking Permits. People may have one or two vehicles. Rather than having a search box for vehicle one and vehicle two I wanted just one to search both V1 and V2. I can make this work using a query of both, but it shows as two columns. I made a query so it is all in one column but cannot figure out how to make it work. It returns nothing.


Grateful for any advice or guidance. I haven't touched Access in years. We use Oracle and SQL Developer for most things. But one user needed this.

Thanks

Query:
SELECT dbo_parkingpermit.UniqueId, dbo_parkingpermit.PermitNumberV1
FROM dbo_parkingpermit
ORDER BY 1 Asc
UNION
SELECT dbo_parkingpermit.UniqueId, dbo_parkingpermit.PermitNumberV2
FROM dbo_parkingpermit


WHERE dbo_parkingpermit.PermitNumberV2 IS NOT NULL;

Had a condition for both fields, this is just showing one as I deleted it.

="[PermitNumberV1] = " & "'" & [Screen].[ActiveControl] & "'"

[jojowhite]

on design view of your form, click on the combo.
on it's property->Format->Column Widths: 0, 1" (or 2.54 if cm).

[CJ_London]

Looks like denormalised data but a union query should work - syntax would be the same as sql server.

Not clear about your condition or why you have order by in the first part

you will have duplicate ID’s if there is a second permit, so the combo will always select the first one assuming that is the bound colunn

[June7]

That ORDER BY clause will be ignored. ORDER BY will only be honored when it is in the last SELECT and it will apply to the entire UNION dataset.