Why won't this "Select Case" work?

[Nick Mirro]

Regardless of the date, the first form opens (> 8/31/3, frmReceivePayments).
If I switch form names, then the other form opens, like it can't get to the
second case. I tried an if/then with the same result. The entire sub is
below.

DateOfVisit and the command button running sub are on a subform.


Case 2

Me.Parent!FormToOpen = 2

Select Case Me!DateOfVisit

Case Is > 8 / 31 / 3

DoCmd.OpenForm "frmReceivePayments", _
WhereCondition:="VisitID=" & Me!VisitID

Case Is < 8 / 31 / 3

DoCmd.OpenForm "Dates and Correspondence - Form", _
WhereCondition:="VisitID=" & Me!VisitID

End Select








Private Sub btnGOTO_Click()
On Error GoTo Err_btnGOTO_Click

Select Case Me.Parent!FormToOpen

Dim frm As Form

Case 1

Me.Parent!FormToOpen = 1

DoCmd.OpenForm "frmCollections", _
WhereCondition:="PatientID=" & Me!PatientID

Set frm = Forms!frmCollections!sbfrmCollections.Form

With frm.RecordsetClone
.FindFirst "VisitID=" & Me!VisitID
If Not .nomatch Then
frm.Bookmark = .Bookmark
End If
End With

Set frm = Nothing

Case 2

Me.Parent!FormToOpen = 2

Select Case Me!DateOfVisit

Case Is > 8 / 31 / 3

DoCmd.OpenForm "frmReceivePayments", _
WhereCondition:="VisitID=" & Me!VisitID

Case Is < 8 / 31 / 3

DoCmd.OpenForm [Dates and Correspondence - Form], _
WhereCondition:="VisitID=" & Me!VisitID

End Select

End Select

DoCmd.Close acForm, "frmRecentBilling", acSaveNo

Exit_btnGOTO_Click:
Exit Sub

Err_btnGOTO_Click:
MsgBox Err.Description
Resume Exit_btnGOTO_Click

End Sub

 

[Wayne Morgan]

You may notice the spaces placed between the characters in your dates. Access is taking
these as a division problem, not as dates. Use "#" as a date delimiter.

Example:
Case Is > #8/31/3#

If you still have problems, you may need to format the date into m/d/yyyy format, but I
suspect the #'s will do it.

Also, you have no option for the date =8/31/3.

 

[Wayne Morgan]

ps. I guess you are using the m/d/yy format and because they are being treated as numbers,
the leading zero in the year is being removed. Don't forget to put it back in. Also, four
digit years are less likely to cause you a problem. It may not matter in this case, but
sooner or later you'll run across something where it will.

 

[Graham Mandeno]

Hi Nick

You need to enclose the date in # signs, or else use CDate("8/31/2003").

VBA is interpreting 8/31/3 as "8 divided by 31 divided by 3". This is a
Very Small Number (somewhat less than 1), so when it is type-cast as a date
in the comparison, it is interpreted as sometime near 2:04 am on "zero
date", which is 30 Dec 1899. I guess you don't have many patients that old!

 

[Emilia Maxim]

Regardless of the date, the first form opens (> 8/31/3, frmReceivePayments).
If I switch form names, then the other form opens, like it can't get to the
second case. I tried an if/then with the same result. The entire sub is
below.

DateOfVisit and the command button running sub are on a subform.


Case 2

Me.Parent!FormToOpen = 2

Select Case Me!DateOfVisit

Case Is > 8 / 31 / 3

DoCmd.OpenForm "frmReceivePayments", _
WhereCondition:="VisitID=" & Me!VisitID

Case Is < 8 / 31 / 3

DoCmd.OpenForm "Dates and Correspondence - Form", _
WhereCondition:="VisitID=" & Me!VisitID

End Select

Nick,

sorry, but the characters 8 / 31 / 3 are not a date. The / character
is the division operator, so VBA most probably first divides 8 thru
31, then the result thru 3 and then compares the date of visit with
the final result (dates are handled in the background as doubles).
Plus, the year 3 would give pretty bizarre results in date
calculations anyway

If you need an explicit date value in code, you must use the
DateSerial function which returns a date type variant for given year,
month and day values:

Case Is > DateSerial(2003, 8, 31)

Please see also Help on DateSerial.

However, it bad programming practice to hard code explicit values
which may change some time. Better store the value in a table, or at
least define a public constant for it.

Best regards
Emilia

Emilia Maxim
PC-SoftwareService, Stuttgart
http://www.maxim-software-service.de

 

[Nick Mirro]

That fixed that. Thank you!