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

Runtime error 6 Overflow

Posted on 2004-09-23
Last Modified: 2008-02-01
Am I missing something very obvious here??

strvdatenear = "20040927"
MsgBox Val(Format$(strvdatenear, "YYYYMMDD"))

gives me runtime error 6.. overflow in windows xp..

Question by:kwaish
  • 2
  • 2
  • 2
  • +5
LVL 10

Expert Comment

ID: 12136390
I don't know if it's all that obvious, but "20040927" isn't a valid date format.
LVL 32

Assisted Solution

Erick37 earned 50 total points
ID: 12136419

This works for me:

Dim strvDateNear As Date
strvDateNear = #9/27/2004#
MsgBox Val(Format(strvDateNear, "YYYYMMDD"))

this too:

Dim strvDateNear As String
strvDateNear = "2004/09/27"
MsgBox Val(Format(strvDateNear, "YYYYMMDD"))

Expert Comment

ID: 12136432
If you don't declare strvdatenear, it will be created as a Long
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LVL 32

Expert Comment

ID: 12136502
From MSDN:
If you don't use the Option Explicit statement, all undeclared variables are of Variant type unless the default type is otherwise specified with a Deftype statement.


Expert Comment

ID: 12136550
Oops! You're right.
Took one too many stupid pills today :)

Assisted Solution

Rick_Townsend earned 75 total points
ID: 12138000
Two points here, really:
1) I assume you're trying to convert a string to a date, and then output that date (which is why you're using the Val function
2) Using a dollar sign ($) after a function or variable is equivalent to declaring it as a String.  (If anyone's interested, we can discuss the merits & drawbacks to BASIC's shorthand.)

So, you're telling VB to convert a Varian, which it will treat as a String because it is storing a String value, to a String (because you used format$), with a date format.  That doesn't make sense, but the BASIC compiler will make guesses as to what you really meant, and drop the dollar sign.

As for the Overflow, the User Defined Date, Time and Number formats require some sort of separator between the formats, UNLESS the interpreter knows what the type of the incoming variable is.  In that case, it compensates by guessing where your formats are separated.

Lost?  Basically, if a String is going into the Format() function, and the format is "YYYYMMDD", VB doesn't know that the individual formats are "YYYY""MM""DD".  That's why Erick37's examples work.  In the first one, the incoming variable is a DATE, so the interpreter already has the format spelled out, and is matching date formats to your custom format.
I'm guessing that the second example is handled similarly, that the interpreter sees a String that it can easily convert to a DATE (assuming that "/" is the date separator defined for Erick37's locale), does the conversion, and is able to guess at your format.

Accepted Solution

Rick_Townsend earned 75 total points
ID: 12138051
Whoops, misread one of my own comments.  Why _are_ you using Val()?  It's designed to return numbers from a String.  If that's what you want, why use the Format() function?

Expert Comment

ID: 12139034
Dates in VB are Doubles
  38,253 Days have passed since 1900 and today is about 85.877% over

 ?.8587731481 * 24
 About 20.611 Hours have passed so far today
? format(.8587731481 ,"HH:MM")

 ? format(38253.8587731481, "YYYYMMDD HH:MM")
20040923 20:36

I don't know why you need the numeric but this might be a way to get you there.

BTW  ( 38253.8587731481 -1) = yesterday at this time

LVL 18

Expert Comment

ID: 12139129

I'm not sure what you trying to do.
The format function you have will convert a date to the format yyyymmdd. It will not take a string in the format yyyymmdd and convert it to a date!

The following will work:

strvdatenear = "2004-09-27"
MsgBox Val(Format$(strvdatenear, "YYYYMMDD"))

strvdatenear = "27 Sep 04"
MsgBox Val(Format$(strvdatenear, "YYYYMMDD"))

If you want to conver a string to a date then the following will work

MsgBox CDate("2004-09-27")

LVL 10

Expert Comment

ID: 12140171
hi  kwaish

may i know what exacly u want to achieve with the above code of urs??

Author Comment

ID: 12144459
Guys.. thanks for all the comments... Basically I am trying to debug an age old application which is huge in size and they are doing this at many places.. Someone else's code.. I clearly do not know what they r trying to do.. Anyways

I have the value initially as "20040927".. And after all that it does what it returns is the same "20040927"..

So basically msgbox strvdatenear will give me what i want.. No need for val. No need for Format.. Other than that I can only think of checking with the person who wrote it..

Eric and  Rick.. Thanks for ur inputs..

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Problem to skip loop 6 64
Determine Range to Select 5 52
Spell Check in VB6 13 126
VB 6 error 5 in windows 10 but not in XP 7 63
Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

856 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