[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 384
  • Last Modified:

Obnoxious Access VBA variable declaration problem

I am passing a variable between subs with an Optional switch set such that
example(data as string, optional err_code as single) is what is expected.

The receiving sub runs a series of proceedures depending on whether the err_code variable is populated. The problem I am having is referring to the err_code variable when it is empty. No iteration of null works and "" empty set also bombs out. I don't want to have to go back and populate all references to this sub with a 0, just so access will have a number to refer to when the field is supposed to be empty, so how do I achieve this? Thanks.
0
JP_TechGroup
Asked:
JP_TechGroup
  • 3
  • 3
  • 2
  • +1
1 Solution
 
hnasrCommented:
If type is single, then empty will be 0.
0
 
hnasrCommented:
" don't want to have to go back and populate all references to this sub with a 0"

What do you mean?

Attach a sample database. Explain the issue.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Pass the Optional variable as a Variant:

example(data as string, optional err_code as Variant)

Then use the IsMissing method to check

If IsMissing (err_code)  Then
   ' do whatever
Else
   ' do whatever else
   ' You can convert the err_code back to a Long here use CLng(err_code) if necessary
End If

Note that IsMissing ONLY works with Optional Variants

mx
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
hindtburnsCommented:
I think hnasr is correct, empty will be 0, but if you want you can change your code to read "Optional err_code as Single = 0" which will set the variable as 0 unless the calling procedure sets it as something else.
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
And variable Dimmed (in VBA) as Numeric (Long, Integer, Single etc) will default to zero (0).  Empty does not apply to Numeric Variables.  Empty applies to a String variable ("").  Numeric variables can be set to Null, String variables cannot.

mx
0
 
JP_TechGroupAuthor Commented:
And this is why I shouldn't program for 12 hours at a pop! Thanks for the head smack.
0
 
hnasrCommented:
Welcome!
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"then empty will be 0."
Empty does not apply to Numeric variables.

mx
0
 
hindtburnsCommented:
Agreed. Should read 'un-assigned = 0"
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now