Solved

how do i define variable Date in Visual Basic?

Posted on 2014-08-01
7
215 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
ID: 40234192
DateTime.Parse(fpick)

DateTime.Parse Method
0
 
LVL 33

Accepted Solution

by:
ste5an earned 500 total points
ID: 40234194
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
ID: 40234233
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 33

Expert Comment

by:ste5an
ID: 40234286
CHANGE IMMEDIATLY YOU SQL SERVER CREDENTIALS !
0
 

Author Comment

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

not good question
0
 
LVL 33

Expert Comment

by:ste5an
ID: 40234348
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ADD New Entries 7 16
Excel macro runs twice 13 51
Filling Blank Cells 14 19
Input Data Using a unique ID from another sheet in Excel 3 16
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

895 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

13 Experts available now in Live!

Get 1:1 Help Now