how to use OR in vba (1 Viewer)

J

jjake

Registered User.
Local time
Yesterday, 21:52
Joined
Oct 8, 2015
Messages
291
Hello,

I'm trying to use the following statement from a continuous form but im getting a type missmatch error

Code: 
Private Sub VisitorName_AfterUpdate()

If Me.VisitorType.Value = 3 And CDate(Me.TrainingDate) < Date - 180 Or CDate(Me.TrainingDate) = "" Then
MsgBox "This contractor requires training"
End If

End Sub


I have a text box on my form that can be blank or have a date in it and if the current date is 6 months past or the textbox is empty then msg prompt.
 
June7

June7

AWF VIP
Local time
Yesterday, 18:52
Joined
Mar 9, 2014
Messages
5,465
CDate() on Null or "" will error. Why do you need to use CDate()? A date/time field cannot hold an empty string anyway. Test for Null. Provide alternate value in case of Null.

If Me.VisitorType = 3 And CDate(Nz(Me.TrainingDate, Date())) < Date - 180 Or IsNull(Me.TrainingDate)
 
Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 03:52
Joined
Sep 21, 2011
Messages
14,231
Firstly, use brackets () for the AND and OR to separate the logic
Then Cdate cannot be "", that is for strings.

Try ISNULL(Me.TrainingDate)

HTH
 
J

jjake

Registered User.
Local time
Yesterday, 21:52
Joined
Oct 8, 2015
Messages
291
June7 said:
CDate() on Null or "" will error. Why do you need to use CDate()? A date/time field cannot hold an empty string anyway. Test for Null. Provide alternate value in case of Null.

If Me.VisitorType = 3 And CDate(Nz(Me.TrainingDate, Date())) < Date - 180 Or IsNull(Me.TrainingDate)
Click to expand...

It's just something i stumbled across that worked until i tried to add another criteria. i wasn't sure how it worked.

also the code worked if the field had a date but if it was empty i got the same type mismatch error
 
June7

June7

AWF VIP
Local time
Yesterday, 18:52
Joined
Mar 9, 2014
Messages
5,465
So is TrainingDate a Date/Time type field in table? The suggested code is correct syntax.

Which is the 'another criteria' - what worked before?
 
J

jjake

Registered User.
Local time
Yesterday, 21:52
Joined
Oct 8, 2015
Messages
291
TrainingDate is a text box with short date formatting using the following control source =VisitorName.column(3)

This worked before,

Code: 
If Me.VisitorType.Value = 3 And CDate(Me.TrainingDate) < Date - 180 Then
MsgBox "This contractor requires training"
End If

End Sub

then i realized i also need to cover null values.
 
June7

June7

AWF VIP
Local time
Yesterday, 18:52
Joined
Mar 9, 2014
Messages
5,465
Okay, you have an unbound textbox pulling value from combobox column. The column value will be a string, not true date/time. This explains why you are using CDate() and why IsNull() and Nz() won't work - have to deal with string.

If Me.VisitorType = 3 And CDate(IIf(Me.TrainingDate="", Date, Me.TrainingDate)) < Date - 180 Or Me.TrainingDate = "" Then

Without parentheses, the AND condition will evaluate first and if that passes, the OR condition will evaluate. If that is not what you want, use ().

If Me.VisitorType = 3 And (CDate(IIf(Me.TrainingDate="", Date, Me.TrainingDate)) < Date - 180 Or Me.TrainingDate = "") Then

The second one doesn't really seem appropriate to me.
 
Last edited:
Cronk

Cronk

Registered User.
Local time
Today, 12:52
Joined
Jul 4, 2013
Messages
2,771
Are you sure?


You are wanting to check that the person has had training within the last 6 months. Using
Code: 
.... IIf(Me.TrainingDate="", Date, Me.TrainingDate) < Date - 180
will mean that a blank TrainingDate will pass the training requirements.

I'd suggest
Code: 
If Me.VisitorType = 3 And  Me.TrainingDate & "" <  Date - 180 Then

NB adding the & "" will handle nulls
 
J

jjake

Registered User.
Local time
Yesterday, 21:52
Joined
Oct 8, 2015
Messages
291
June7 said:
Okay, you have an unbound textbox pulling value from combobox column. The column value will be a string, not true date/time. This explains why you are using CDate() and why IsNull() and Nz() won't work - have to deal with string.

If Me.VisitorType = 3 And CDate(IIf(Me.TrainingDate="", Date, Me.TrainingDate)) < Date - 180 Or Me.TrainingDate = "" Then

Without parentheses, the AND condition will evaluate first and if that passes, the OR condition will evaluate. If that is not what you want, use ().

If Me.VisitorType = 3 And (CDate(IIf(Me.TrainingDate="", Date, Me.TrainingDate)) < Date - 180 Or Me.TrainingDate = "") Then

The second one doesn't really seem appropriate to me.
Click to expand...

I actually had to add one more line in because if visitor type = 1 or 2 and the training date was empty i would still get the message box.

If Me.VisitorType = 3 And CDate(IIf(Me.TrainingDate = "", Date, Me.TrainingDate)) < Date - 180 Or Me.VisitorType = 3 And Me.TrainingDate = "" Then
 
J

jjake

Registered User.
Local time
Yesterday, 21:52
Joined
Oct 8, 2015
Messages
291
This was working then i changed my form slightly because of an unrelated issue.

i'm now getting the error "invalid use of null" using the following code,

Code: 
ElseIf Me.VisitorType = 3 And CDate(IIf(Me.LastTrainingDate = "", Date, Me.LastTrainingDate)) < Date - 180 Or Me.VisitorType = 3 And Me.LastTrainingDate = "" Then


on my form i have a bound combo box "VisitorName"

then i have a hidden combo box (cboLastTrainingDate) based on a different query with a control source =[visitorname]

then i have a hidden unbound textbox (LastTrainingDate) with control source =[cboLastTrainingDate].[column](1)

i get the error when i have a null value, other then that it works fine.
 
P

plog

Banishment Pending
Local time
Yesterday, 21:52
Joined
May 11, 2011
Messages
11,638
Explicitly seperate your ANDs and ORs:

True OR False AND True AND False = ?

True OR (False AND True AND False) = True

(True OR False) AND (True AND False) = False

Parenthesis Matter
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 10:52
Joined
May 7, 2009
Messages
19,231
Code: 
ElseIf Me.VisitorType = 3 And CDate(IIf(Trim(Me.LastTrainingDate & "") = "", Date, Me.LastTrainingDate)) < Date - 180 Or Me.VisitorType = 3 And Trim(Me.LastTrainingDate & "") = "" Then
 
J

jjake

Registered User.
Local time
Yesterday, 21:52
Joined
Oct 8, 2015
Messages
291
Perfect,

Plog - Thanks for the explanation

arnelgp - Thanks for the code
 
P

plog

Banishment Pending
Local time
Yesterday, 21:52
Joined
May 11, 2011
Messages
11,638
Code: 
ElseIf Me.VisitorType = 3 And CDate(IIf(Me.LastTrainingDate = "", Date, Me.LastTrainingDate)) < Date - 180 Or Me.VisitorType = 3 And Me.LastTrainingDate = ""

Now I'm looking at your logic and my brain hurts more. I believe you can combine your LastTrainingDate expressions. If its "" instead of setting it to the current date, set it to (Date -181) and it passes the first test. No need for the second test. No need for any OR in there. Just 2 comparisons seperated by an AND
 
You must log in or register to reply here.

Similar threads

N
Solved How to convert "" to null in MS Access VBA
2
Replies
22
Views
346
NauticalGent
NauticalGent
J
Solved Removing pages in report with Module
Replies
3
Views
319
jazsriel
J
K
Form blank when no records found
Replies
9
Views
463
kevinh2320
K
B
Avoid duplicate records (overlapping dates)
Replies
18
Views
617
arnelgp
arnelgp
O
  • Solved
VBA Outlook SQL problem
2
Replies
20
Views
916
olxx
O

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom