Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

how do i define variable Date in Visual Basic?

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
gvilbis
Asked:
gvilbis
  • 3
  • 2
1 Solution
 
Chris WatsonSoftware DeveloperCommented:
DateTime.Parse(fpick)

DateTime.Parse Method
0
 
ste5anSenior DeveloperCommented:
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
 
gvilbisAuthor Commented:
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 does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
ste5anSenior DeveloperCommented:
CHANGE IMMEDIATLY YOU SQL SERVER CREDENTIALS !
0
 
gvilbisAuthor Commented:
I've requested that this question be deleted for the following reason:

not good question
0
 
ste5anSenior DeveloperCommented:
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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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