Query Problem

[TheAceMan1]

Howdy All . . . . .

Have the following query setup:

[blue][tt]Field:
******
Code   as Long(PK)
Flg:   IIf(IsNull([Idx]),2,1) Sort Ascend1
Idx:   idxBuild([Code])       Sort Ascend2
Name   as Text                Sort Ascend3[/tt][/blue]

The [blue]sort order[/blue] above is [blue]required[/blue]. Problem is, [purple]as soon as I sort on Flg, I get a parameter popup for Idx![/purple]

I've gotton around this by making a [blue]seperate query[/blue] (lets say Query1) [blue]without Flg[/blue], [purple]so the Idx function can be fulfilled.[/purple] Then making a [blue]main query[/blue] ([purple]using Query1 in design view[/purple]), [blue]adding the Flg field[/blue] an performing my sorting.

What I'd like is to [blue]have it all in one query[/blue] so I can convert to SQL and put it in code (the Code & Name fields are propritary).

For reference, the SQL from the two queries are:

Query1

[blue]SELECT tblSSSites.Code, idxMSBuild([Code]) AS Idx, tblSSSites.Name FROM tblSSSites;[/blue]

MainQuery

[blue]SELECT Query1.Code, IIf(IsNull([Idx]),2,1) AS Flg, Query1.Idx, Query1.Name FROM Query1 ORDER BY IIf(IsNull([Idx]),2,1), Query1.Idx, Query1.Name;[/blue]

[purple]Any Ideas? . . . . .[/purple]

See Ya! . . . . . .

 

[TheAceMan1]

To All . . . .

Got It . . . . SQL turns out to be:

[blue][tt]SELECT Q1.Code, IIf(IsNull([Idx]),2,1) AS Flg, Q1.Idx, Q1.Name
FROM [SELECT tblSSSites.Code, idxMSBuild([Code]) AS Idx,
             tblSSSites.Name
      FROM tblSSSites
      WHERE (((tblSSSites.subgrp)="ms"))]. AS Q1
ORDER BY IIf(IsNull([Idx]),2,1), Q1.Idx, Q1.Name;[/tt][/blue]

See Ya! . . . . . .