Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Conditional logic not working

Posted on 2015-02-24
27
Medium Priority
?
112 Views
Last Modified: 2015-02-24
I have the following code that is not working.  I can't figure out why:

            SupplyODCheck = Replace(Nz(Me.ODSupply, 0), " Month", "")
            SupplyOSCheck = Replace(Nz(Me.OSSupply, 0), " Month", "")

            If SupplyODCheck >= 12 Or SupplyOSCheck >= 12 Then
                Me.YearSupply = True
            Else
                Me.YearSupply = False
            End If

When: SupplyODCheck  = 6, SupplyOSCheck  = 0

However each time it executes its hitting the first line and setting year supply True when its false.
0
Comment
Question by:thandel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 16
  • 6
  • 5
27 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 40628450
how did you declare the variables "SupplyODCheck", "SupplyOSCheck"

dim SupplyODCheck as integer, SupplyOSCheck as integer

SupplyODCheck = cint(Replace(Nz(Me.ODSupply, 0), " Month", ""))
SupplyOSCheck = cint(Replace(Nz(Me.OSSupply, 0), " Month", ""))
0
 

Author Comment

by:thandel
ID: 40628460
They are Variants.... values come from a text box on a form.
0
 

Author Comment

by:thandel
ID: 40628467
If set as a integer I get a type mismatch error when setting the value for SupplyODCheck  and SupplyOSCheck
0
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
LVL 26

Expert Comment

by:Nick67
ID: 40628476
You don't say what is in Me.ODSupply or Me.OSSupply
That doesn't make matters easy!
But you are using Replace
SupplyODCheck = Replace(Nz(Me.ODSupply, 0), " Month", "")
and that's a STRING function
Replace(expression, find, replace[, start[, count[, compare]]])
So, you are saying replace " Month" with ""
That still leaves you with a string of some sort, but I can't say because you haven't supplied the original values of Me.ODSupply or Me.OSSupply
Now you are checking against integers
If SupplyODCheck >= 12 Or SupplyOSCheck >= 12 Then

But the values in SupplyODCheck  and SupplyOSCheck are -- because of Replace -- strings
Option Explicit should be at the top of each module and each variable declaration should have a type
Dim SupplyODCheck as String

But, supply more code and more detail of the values involved
If set as a integer I get a type mismatch error when setting the value for SupplyODCheck  and SupplyOSCheck
Absolutely.
Replace is a string function.
What are you replacing?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628481
@thandel

did you use the code i posted above?
0
 

Author Comment

by:thandel
ID: 40628489
The text boxes have values like "XX Month" where XX and be a value in .5 increments from 1 to 24.  I was just trying to pull the value from the text box to get the number by removing the " Month"... it is returning the correct value but I guess as a string???
0
 

Author Comment

by:thandel
ID: 40628491
Can then change the value from a string to an number? Or is there a better solution?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628507
<Can then change the value from a string to an number? Or is there a better solution? >

@thandel

did you use the code i posted above?
0
 

Author Comment

by:thandel
ID: 40628513
I'm sorry did you mean:

Option Explicit
Dim SupplyODCheck as String

Let me try.....
0
 

Author Comment

by:thandel
ID: 40628522
Using Option Explicit causes other errors in the code.  Am I understanding correctly?
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628534
see my post at http:#a40628450
0
 

Author Comment

by:thandel
ID: 40628569
Did you want them set as an integer?  I still have an error that was as well.
0
 

Author Comment

by:thandel
ID: 40628572
Sorry I'm slow.... just a moment let me try your full solution.....
0
 

Author Comment

by:thandel
ID: 40628576
Still having a type mismatch
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40628580
Using Option Explicit causes other errors in the code
It doesn't cause them, it exposes them.
What Option Explicit does, is it REQUIRES you to declare all variables before you use them

works without Option Explicit:
'------------------------
sub MySub
x = 1
Msgbox x
end sub

works with Option Explicit
'--------------------
Option Explicit
sub MySub
Dim x as Integer 'declaration is required!
x = 1
Msgbox x
end sub

'Another post coming shortly
0
 

Author Comment

by:thandel
ID: 40628584
Its a type mismatch when the text box is null
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40628590
Now--to your problem
The text boxes have values like "XX Month"
Ok, replace is fine
dim SupplyODCheck as integer
dim  SupplyOSCheck as integer
SupplyODCheck = CInt(Replace(Nz(Me.ODSupply, 0), " Month", ""))
SupplyOSCheck = CInt(Replace(Nz(Me.OSSupply, 0), " Month", ""))

CInt will coerce the string from your Replace to an integer
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40628596
Its a type mismatch when the text box is null
Where?
Nz(Me.ODSupply, 0) should take care of ODSupply (or OSSupply with its Nz) being null
0
 

Author Comment

by:thandel
ID: 40628658
Yes that is there the error is occuring, of type mismatch.  I know doesn't make sense given the NZ function.
0
 

Author Comment

by:thandel
ID: 40628664
Actually not null but "".
0
 

Author Comment

by:thandel
ID: 40628667
I tried this and it seems to be working:

            If Len(Me.ODSupply) > 1 Then SupplyODCheck = CInt(Replace(Nz(Me.ODSupply, 1), " Month", ""))
            If Len(Me.OSSupply) > 1 Then SupplyOSCheck = CInt(Replace(Nz(Me.OSSupply, 1), " Month", ""))
0
 

Author Comment

by:thandel
ID: 40628672
In fact this works with SupplyOD/OSCheck as a string:

            If Len(Me.ODSupply) > 1 Then SupplyODCheck = Replace(Nz(Me.ODSupply, 0), " Month", "")
            If Len(Me.OSSupply) > 1 Then SupplyOSCheck = Replace(Nz(Me.OSSupply, 0), " Month", "")
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40628678
Revising my codes

dim SupplyODCheck as integer
dim  SupplyOSCheck as integer
SupplyODCheck = Cint(IIF(Me.ODSupply & ""="",0,Replace(Me.ODSupply, " Month", ""))
SupplyOSCheck = Cint(IIF(Me.OSSupply & ""="",0,Replace(Me.OSSupply, " Month", ""))
0
 

Author Comment

by:thandel
ID: 40628686
I'll use your code as an Int and using Cint as that seems the better  way of coding  :)
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40628691
Actually not null but "".
Now, look at your Nz()
Nz(Me.ODSupply, 0)
You are supplying 0, not "0"
And when you feed 0 into Replace - which is looking for a string! -- bad things happen, yes?
And look at the documentation for Replace

Return Values

Replace returns the following values:

If Replace returns
expression is zero-length Zero-length string ("")
expression is Null An error.
find is zero-length Copy of expression.
replace is zero-length Copy of expression with all occurences of find removed.
start > Len(expression) Zero-length string.
count is 0 Copy of expression.

So, yes, if you feed Replace "", you are going to get ""
And then you are going to try to coerce that to integer
Cint("") and that goes bang!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40628707
When you are taking user-generated input, and doing something with it, no matter what platform, it is ALWAYS a good idea to test what the user gives you for validity!

Your textboxes may need AfterUpdate events to kick the user in the teeth if they enter garbage, or this procedure you've got should kick out if the inputs are bad.  GIGO.
0
 

Author Comment

by:thandel
ID: 40628737
Thanks that makes sense (now)  :)
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question