Unwanted Chr() conversion in query

[spikepl]

Access 2007, Win XP.

Displays one value in query, but calculates using another!

I have an option group on the main form. The option group can have value 51, 52 or 53, and after update the subform is requeried.

I query this table in the subform:

tblStatus    
StatusID StatusDate 
51______07/07/2013     
52______07/07/2013     
53______ 
54______

Using

SELECT tblStatus.StatusID, tblStatus.StatusDate, [Forms]![frmStatus]![optStatus] AS Expr1
FROM tblStatus
WHERE ((Not (tblStatus.StatusDate) Is Null) AND (([Forms]![frmStatus]![optStatus])=52)) OR (((tblStatus.StatusDate) Is Null) AND (([Forms]![frmStatus]![optStatus])=53)) OR ((([Forms]![frmStatus]![optStatus])=51));

and obtain

sfrmStatus

StatusID_StatusDate_Expr1      
51_____07/07/2013_3
52_____07/07/2013_3
53________________3
54________________3

when optStatus = 51. Column Expr1 should display [Forms]![frmStatus]![optStatus], i.e. 51, but displays 3, which is Chr(51)! And so on. Why is this happening and why do I not see the 51 but 3? Especially since the query matches the optStatus in the criterion to 51, 52, 53 as required, but just displays something else.

To see this you just have to open the attached db.

 

[Galaxiom]

The Chr(51) would be a coincidence.

I expect you have set up the option group incorrectly. Your label names would 51, 52, 53 but the Values are the defaults, 1, 2 and 3.

Change the Values.

 

[ChrisO]

I can’t really say why but it also does that in Access 2003.

Wrapping the option group return value in the Val function seems to fix it:-

Val(Forms!frmStatus!optStatus)

Chris.

 

[ChrisO]

A small Access 2003 demo is attached.

Suggest contacting Microsoft.

Chris.

 

[Galaxiom]

Well how very peculiar. Unusual (perhaps even unique) for the value property of a control to return something different in VBA and a query.

I guess it is a quirk of the OptionGroup.

BTW Out of curiosity, I tried it with a Bound option group to see if it made any difference and got the same result.

 

[spikepl]

Thanks for the fix ... and for the verification that I am not mad

I wonder if this feature is still present in A2013? If it is, unfortunately I have no idea whatsoever how to contact Microsoft with an error report.