Fieldname is not exciting

[jammerdk]

Hi

I'm trying to get a query of mine running hope you guys can help out.

I'm trying to return a fieldname if the fieldname doesn't excist.

I've tried this

IIF(QryReturnVal.SD Is Null,QryReturnVal.SD, "NA")

but without any luck

 

[Remou]

I think you are not saying what you mean.

IIf(Criteria, Value if True, Value if False)

So you are saying:
If SD is null, null, NA

I think you mean:
IIF(QryReturnVal.SD Is Null, "NA", QryReturnVal.SD)

I hope that is more exciting

 

[jammerdk]

it's the lack of fieldname that causing me trouble

I've createed a crosstab query that have the fieldnames

No, Out, In, Surface (SD)

and sometimes just

No, Out, In

So i'd like to override any Fieldnames that isn't there

 

[Remou]

You will have to:

* dynamically build your query, or
* use a wild card for field (Select *), or
* Always include SD (Select in, out, "" as SD)

If you want to give a few details of the background, such as where No, Out, In, Surface (SD) are coming from, I may be able to help.

 

[jammerdk]

They are coming of a crosstab query

TRANSFORM First(Round(QryGetVal.Value,2)) AS Values
SELECT QryGetVal.No
FROM QryGetVal
GROUP BY QryGetVal.No
PIVOT IIF(QryGetVal.Title,QryGetVal.Title,"");

D

 

[Remou]

How about:

'References Microsoft DAO x.x Object Library
Dim rs As DAO.Recordset
Dim db As database

Set db = CurrentDb
Set rs=db.OpenRecordset("xtabQueryNameHere")

For i=0 To rs.Fields.Count-1
   strSQL=",[" & rs.Fields(i).Name & "]"
Next
'Check if SD field exists
If Instr(strSQL,"[SD]")=0 Then
   strSQL=",'' As [SD]" & strSQL
End If

strSQL="SELECT " & mid(strSQL,2) & " FROM xtabQueryNameHere"

db.CreateQueryDef("ANewQueryName", strSQL)

 

[PHV]

Have you tried to replace this:
PIVOT IIF(QryGetVal.Title,QryGetVal.Title,"");
with this ?
PIVOT QryGetVal.Title In ('No', 'Out', 'In', 'Surface (SD)');

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[jammerdk]

PHV it works like a charm !!!

 

[genomon]

You should reconsider using reserved words as field names. It will cause you problems in the future.

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."