Query statement

[paul29berks]

I have written the statement below:

IIF([type]=*B and [startdate]>Date(), "X", "Y")

My results however return all types with a start date > todays date with X and not restricted to those like B.

Can anyone suggest something in my statement I have missed?

Thanks

 

[paul29berks]

Many thanks

There are definately types ending with b and no other statements excluding b.

My full SQL for the query is below.

SELECT Missing_Client_Codes_Ext.organisationCode, Missing_Client_Codes_Ext.organisationClientAccount, Missing_Client_Codes_Ext.organisationName, Missing_Client_Codes_Ext.Year, Missing_Client_Codes_Ext.Month, Missing_Client_Codes_Ext.organisationRef, Missing_Client_Codes_Ext.Address, Missing_Client_Codes_Ext.Add2, Missing_Client_Codes_Ext.Add3, Missing_Client_Codes_Ext.invoiceStatus, Missing_Client_Codes_Ext.Contact, Missing_Client_Codes_Ext.eventRef, Missing_Client_Codes_Ext.Division, IIf([dbo_vwPlacementSheets].[contact1Fax]="",[dbo_vwPlacementSheets].[contact2Fax],0) AS ContactFax, ([dbo_vwPlacementSheets].[contact1Tel]) AS ContactTel, ([dbo_vwPlacementSheets].[contact1position]) AS ContactTitle, Missing_Client_Codes_Ext.[Organisation Character Count], IIf([type] Like "*B",IIf([startdate]>Date(),"EBNIBKOT","BACKOUT"),"") AS Backouts, Missing_Client_Codes_Ext.type, Missing_Client_Codes_Ext.startDate
FROM Missing_Client_Codes_Ext
WHERE (((Missing_Client_Codes_Ext.Year)=[Forms]![Interface]![Year]) AND ((Missing_Client_Codes_Ext.Month)=[Forms]![Interface]![month]));

many thanks

 

[Andrew Fergus]

Hi Paul

I can't see anything wrong with the SQL (but would be happy to be proved wrong by others!).

Per my previous post did you try changing the "" part to "Z" - and if so, did you see all Z's?

If you change this part
[type] Like "*B"
to this
Right([type],1)="b"
does that make any difference?

Andrew

 

[Andrew Fergus]

*The penny dropped*

Are there trailing spaces on the type field? If so try using :
Right(Trim([type]),1)="b"
or
Trim([type]) Like "*B"

HTH, Andrew

 

[SydneyGeek]

This worked for me...

Backouts:IIf([type] Like "*b",IIf([startdate]>Date(),"X","Y"),"Y")

Query...

Book1
	A	B	C	D
1	Type	StartDate	Backouts
2	comb	22-Sep-06	Y
3	bomb	01-Oct-06	X
4	camera	01-Sep-06	Y
5	halo	27-Sep-06	Y
Sheet5

Denis