Solved

how do i define variable Date in Visual Basic?

Posted on 2014-08-01
7
213 Views
Last Modified: 2014-08-03
i have the following select statement:

Dim fpick&
Dim lpick&


" select invoiceno, invoicedate, sent_810, sent_211 as sent_mail from invheader where (sent_211 = 'N' or sent_211 is null) and (sent_810 = 'N' or sent_810 is null) and invoicedate between ? and ? "
  ' On Error GoTo UserError:
   fpick = InputBox("Select First Invoice# in Range")
    comm.Parameters(0) = fpick
   lpick = InputBox("Select Last Invoice# in Range")
    comm.Parameters(1) = lpick

i am receiving error when entering the date to message box
i know that the type is incorrect what should be the right type that the select will run fine with date?

thanks
gvilbis
0
Comment
Question by:gvilbis
  • 3
  • 2
7 Comments
 
LVL 4

Expert Comment

by:Chris Watson
Comment Utility
DateTime.Parse(fpick)

DateTime.Parse Method
0
 
LVL 32

Accepted Solution

by:
Stefan Hoffmann earned 500 total points
Comment Utility
First of all: VBScript or Visual Basic for Applications (VBA)?

As you mentioned Excel, I guess you mean VBA. Then it is simply Dim variableName AS Date. But interestingly, why do you pick numbers ??

Your code has more problems. InputBox returns only strings. You may use something like:
Dim instring as String
Dim fpick As Date
Dim lpick As Date

On Local Error Resume Next

instring = InputBox("Select First Invoice# in Range")
Err.Clear
fpick = CDate(instr)
If Err.Number <> 0 Then
  MsgBox "Cannot convert input to date."
End If

lpick = InputBox("Select Last Invoice# in Range")
Err.Clear
lpick = CDate(instr)
If Err.Number <> 0 Then
  MsgBox "Cannot convert input to date."
End If

On Local Error GoTo 0

comm.Parameters(0) = fpick
comm.Parameters(1) = lpick

Open in new window

0
 

Author Comment

by:gvilbis
Comment Utility
its VBA i am not sure how to correct according the above, here is the code can you rewrite it?

Thanks

Dim fpick As Date
    Dim lpick As Date
    Dim conn As New Connection, rec As New Recordset
    Dim ws As Worksheet
    Dim comm As New Command


     Sheets("Invoice Mailed").Select
    Range("A2").Select
    Set ws = ThisWorkbook.Worksheets("Invoice Mailed")
    conn.Open "Provider=MSDASQL;DRIVER=SQL SERVER;SERVER=199.27.203.74,1433;DNS=wf10taa;UID=xx;PWD=xxxxx;DATABASE=MyDatabase"
    Set comm.ActiveConnection = conn
    comm.CommandText = _
    " select invoiceno, invoicedate, sent_810, sent_211 as sent_mail from invheader where (sent_211 = 'N' or sent_211 is null) and (sent_810 = 'N' or sent_810 is null) and invoicedate between ? and ? "
  ' On Error GoTo UserError:
   fpick = InputBox("Select First Invoice# in Range")
    comm.Parameters(0) = fpick
   lpick = InputBox("Select Last Invoice# in Range")
    comm.Parameters(1) = lpick
   
    rec.Open comm
   
    Sheets("Invoice Mailed").Select
    Range("A:Z").Clear
   
    ws.[A1].CopyFromRecordset rec
    rec.Close: conn.Close
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
CHANGE IMMEDIATLY YOU SQL SERVER CREDENTIALS !
0
 

Author Comment

by:gvilbis
Comment Utility
I've requested that this question be deleted for the following reason:

not good question
0
 
LVL 32

Expert Comment

by:Stefan Hoffmann
Comment Utility
CHANGE IMMEDIATLY YOU SQL SERVER CREDENTIALS !

CHANGE IMMEDIATLY YOU SQL SERVER CREDENTIALS !

CHANGE IMMEDIATLY YOU SQL SERVER CREDENTIALS !

Deleting the question is not sufficient
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now