Hi,

im not entirely sure if you can do a if/then in a select case. i have a select case which looks at the current day and depending on what day it is, you get a certain message-

Select Case dteNowDate
Case 5
MsgBox "You need to complete today"
Case 6
MsgBox "you are starting a new statement"
Case Else
end Select

i need to have the code look up a query to see if the action has already been carried out-

ChkAction = Nz(Dlookup("TickField", "MyTable"), False)

added together-

If ChkAction = True Then
'move to the next case statement
End If
else
Msgbox "My Message"

i recall reading somewhere that case statements did not handle if/ then.

i am going to try later as currently on my phone btu just wanted to ask the question.

regs,

Nigel

Where did you get that idea?

You can nest If/Then within Select Case, vice versa, and several different permutations of nesting different control statements as well.

PS: I reread your question and saw something else.

If you need to test for two conditions, you can't nest it inside and expect to move to next case. You would have to either test the second condition in each case individually (too cumbersome) or nest the Select Case inside If/Then as below which is OK if you only will perform a action on a True condition and do nothing for False condition

If Condition1 Then
Select Case Condition2
Case 1
Cond2 = 1
Case 2
Cond2= 2
Case Else
Cond2 = 3
End Select
End If

Does that satisfy what you are looking for?

Either way you go it's gonna be complex, can bang it out in a readable fashion without too many forks by doing a matrix of if/then ...

Dim vChkAction As Variant
vChkAction = Nz(Dlookup("TickField", "MyTable"), False)

If dteNowDate = 5 And vChkAction = False Then
...
ElseIf dteNowDate = 5 And vChkAction <> False Then
...
ElseIf dteNowDate = 6 And vChkAction = False Then
...
ElseIf dteNowDate = 6 And vChkAction <> False Then
...
Else
...
End If


Syntax will need to be corrected especially to get away from the variant.

-dK

Hi Guys,

i was sure i read it somewhere! but hey, you live n learn.

in regard to the complex part, as you can if/then, i would think it more straight forward

ChkAction = Nz(Dlookup("TickField", "MyTable"), False)
dteNowDate = format(Date, "d")

Select Case dteNowDate
Case 5
If ChkAction = False Then
MsgBox "You need to complete today"
End If
Case 6
If ChkAction = false Then
If dteNowDate < 5 Then
MsgBox "You are over your Period"
else
MsgBox "you are starting a new statement"
end if
Case Else
end Select

i guess then, that looks about right? right?:eek:


regs,

nigel

Here's an esoteric idea. (some typecasting may be needed to make sure it works correctly)

vChkAction = Nz(Dlookup("TickField", "MyTable"), False)

If vChkAction Then
intNowDate = dteNowDate * vChkAction
Else
intNowDate = dteNowDate
End If

Select Case intNowDate
Case 5
....
Case -5
....
Case 6
....
Case -6
....
End Select

Just a point of note ... if "TickField" is of "Yes/No" datatype it will never return a Null since Yes/No datatype can only be True (-1) or False (0). As such the variable ChkAction should be declared as a Boolean. Also, what is the datatype of dteNowDate? ... If its Date, the value stored in that variable will not be the range you are testing for unless your dates are in the 1900's! ... It seems you would want to use the Weekday() function to return the day number of the current day if dteNowDate is typed as a Date

hi Dat,

My code was just an example. In the actual code, the yes/no is declared Ada Boolean and dtemydate as date. I want the day number of the month not really the weekday number.

Thanks,

Nigel

In this case, you just need to typecast both boolean and result of Day(Whateverdateyouuse) to a integer then multiply it so you can have a select case as I showed above.

Thanks for that post, Banana. Very insightful and much more clean. I know I am going to make some use out of it instead of my usual methods.

-dK

Just a point of note ... if "TickField" is of "Yes/No" datatype it will never return a Null since Yes/No datatype can only be True (-1) or False (0).

I don't think that's actually true, are you sure?

We actually had a lengthy discussion about that in another thread;

VBA boolean isn't the same thing as Access's Yes/No, and the latter cannot ever be null, but VBA's boolean can be null.


EDIT: My memory's faulty. It's not VBA Boolean that can have Null, but rather the control Checkbox. So if checkbox has a Null value, the data it is bound to will return as False... may not be what you were expected, hence Brent's caution.

Then why is there a need to wrap any function to extract the value behind the checkbox with the Nz function 'cause without it in certain circumstances it will bomb out

>> I don't think that's actually true, are you sure? <<

100&#37; certain ... but I guess there is a need for the Nz() wrapped around the DLookup() that I was forgetting ... There might not be a match for the criteria, so a Null would be returned and need to be coerced...

>> VBA boolean isn't the same thing as Access's Yes/No, and the latter cannot ever be null, but VBA's boolean can be null. <<

Well ... NEITHER one can hold a Null ...

>> Then why is there a need to wrap any function to extract the value behind the checkbox with the Nz function <<

Because a Checkbox is a control ... not a field or a variable ... and Controls can hold a value of Null ...

....

Here is the thread in which I beleive I have provided sufficient information and tests to verify each claim I have made.

http://www.access-programmers.co.uk/forums/showthread.php?t=151895

Interesting read everyone ...

In terms of statistic rules, you can't have a yes without an opposing no option. It has to be one or the other. So if i have a label and one check box (yes), I am already violating the rules of counting because I am assuming the user has chose no if the box is empty - the user might not have ever noticed the box! Data in this manner is always validated by tallying up the yes's and no's to meet the requirement of a 100% response rate.

In sticking to this rule I have always avoided any sort of rule in the past by setting the default value. The default swing to the yes or to the no depending on how I have defined question.

Example:
Component Installed - Default No
Component Not Installed - Default Yes

This tactic forces the user to mark a box once they have performed said task or verified the task. This rule is implemented whether I do this in an option group or coupled check boxes with conditional formatting.

-dK

dkinley,

I totally agree with you. I wonder if it has to do with the fact that we're referencing the object rather than the value. Even though this will give us the value implicitly:

Debug.Print Me.MyCheckbox

I wonder if this would return False when it was Null?

Debug.Print Me.MyCheckbox.Value

Will have to find time to experiment with it but I don't have Access handy.

Either way, I do think a good design includes *good defaults* which will take care of any problems that may arise from bad/missing data.

Hey banana ...

There is absolute no difference between your two posted statments ... the .Value property is the default property of a Control.

Also, just know that a Control is different than a Field. Upon review of that long thread, you will see that the ONLY way to directly reference a Field in the underlying bound recordset is through the Recordset/RecordClone property. If you reference the value through the form in any other way, you are interacting with a control ... and note that the control your dealing with may be one that Access is managing as a bridge to the underlying bound Field. For example ..

- A form, bound to a table with the yes/no field named MyBoolean ...
- A check box control name chkMyBoolean, bound to field MyBoolean

Me.chkMyBoolean ... refers to the user created check box control (which CAN hold a Null, until record commital)

Me.MyBoolean ... refers to a hidden control that Access manages in order to get to the field value, there is only one property of this type of control, .Value. The control can hold a Null until record commital

Me.Recordset.Fields("MyBoolean") ... a direct link to the field of the table, this CAN NOT hold a Null at ANY TIME ...