Select Case using query field

[focosi]

Do "Select Case" statements work with query fields ??


In a form I am trying to implement the following code to prompt users to change password :

Select Case LastPwChange![Y]
Case Is <= 90
DoCmd.OpenForm [Recipient]
Case Else
MsgBox "Password expired. You must change it"
DoCmd.OpenForm [frm_Change_Password]
End Select

but I receive runtime error 424 (object expected) and the debugger highlight the first line....
FYI,

LastPwChange is my query name
[Y] is an expression field within the query ( Y: Min(Now()-[Date] )

Please help!

[pbaldy]

Sure, but you can't get the value from a query (or table) that way. Presuming that query is not the record source of the form, you need to either open a recordset or use DLookup() to get the value from it.

[focosi]

Sure, but you can't get the value from a query (or table) that way. Presuming that query is not the record source of the form, you need to either open a recordset or use DLookup() to get the value from it.

Ok. I am trying to solve this. I have put a hidden control on the form (frmHelpAbout) which is bound to the value to [Y] and everything works with the If statement below

If Me![Y] <= 90 Then
DoCmd.OpenForm "Recipient"
ElseIf Me![Y] > 90 Then
MsgBox "blah blah...."
DoCmd.OpenForm "frm_Change_Password"
End If

The point is that when the query provides no records, I receive runtime error '2427' You have entered an expression that has no value. So I'm trying to add an if statement for when there are no records....

If Me![Y] <= 90 Then
DoCmd.OpenForm "Recipient"
ElseIf Me.RecordsetClone.RecordCount = 0 Then
MsgBox "blah...."
DoCmd.OpenForm "frm_Change_Password"
ElseIf Me![Y] > 90 Then
MsgBox "blah blah...."
DoCmd.OpenForm "frm_Change_Password"
End If

but error 2427 persists.. What's wrong with my syntax ???

[focosi]

Fixed myself! I had to change to order of the If statements and made the Me.recordset.recordcount the first one !

[pbaldy]

Glad you got it sorted out.