Converting Date & Time formats 4

[djburnheim]

I need to convert a date from dd/mm/yy to yyyy-mm-dd HH:MM:SS so I can insert it into a sql query. I've worked a pretty rude way of doing it using comboboxes for day, month & year and then creating a string in the format I want but I would prefer to use a calendar control for the user to enter the date. The code I'm using at the moment is below.



Dim Day As Variant
Dim Month As Variant
Dim Year As Variant

Day = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31&quot
Month = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12&quot
Year = Array("2001", "2002", "2003", "2004", "2005&quot

cboDay.List = Day
cboMonth.List = Month
cboYear.List = Year

Dim Query As String
Dim StartDate As String
Dim EndDate As String

StartDate = cboYear.Value & "-" & cboMonth.Value & "-" & cboDay.Value - 1 & Chr(32) & "00:00:00"
EndDate = cboYear.Value & "-" & cboMonth.Value & "-" & cboDay.Value + 5 & Chr(32) & "00:00:00"
DisplayDate = cboDay.Value & "/" & cboMonth.Value & "/" & cboYear.Value

Worksheets("Sheet1&quot.Range("B1&quot.Value = DisplayDate

Query = "SELECT Count(*) AS 'Cases Created' FROM Vantive.dbo.SW_HD_CASE SW_HD_CASE WHERE (SW_HD_CASE.swDateCreated Between {ts '" & StartDate & "'} And {ts '" & EndDate & "'}) AND (SW_HD_CASE.swCreatedBy='" & Cells(2, 1) & "')"

With ActiveSheet.QueryTables.Add(Connection:= _
"ODBC;DSN=Database;UID=user;PWD=password;APP=Microsoft® Query;WSID=QBRIK5;DATABASE=Master" _
, Destination:=Range("A4&quot)
.CommandText = Array(Query)
.Name = "Name"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

As an a side...I'm sure there has to be an easier way to populate arrays with days of the week, months etc etc.

Any help would be greatly appreciated.

Dave

 

[RobBroekhuis]

I'm not familiar with Access. In Excel, I'd use:

format(now(),"yyyy-mm-dd HH:MM:SS&quot

but I'm not sure that function is available in Access. Worth a try, anyway. Otherwise, it's easy enough to piece together using string functions operating on date functions e.g.
year(now) & "-" & mid(str(month(now)+100),3) & mid(str(day(now)+100,3)
and equivalent for the time value.
Rob

 

[xlbo]

Pretty sure that format's available in Access. Anyways - date/time is essentially identical in all M$ products and is really rooted in the serial number so pretty much any format can be applied Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[djburnheim]

I am working Excel but I keep getting a compile error when I try to use the above....this is what I have

Dim StartDate As String
Dim EndDate As Date

StartDate = dtpWeekStart.Value - 1
EndDate = dtpWeekStart.Value + 5

Format(Now(), "yyyy-mm-dd HH:MM:SS&quot

It is expecting = at the end of the format statement

 

[xlbo]

Problem 1:
You have dimmed startdate as string and then try to apply a value function to it
Dim StartDate As String
Dim EndDate As Date

StartDate = dtpWeekStart.Value - 1

Problem 2:
You need to apply the below to a variable
Format(Now(), "yyyy-mm-dd HH:MM:SS&quot

myDate = format(now(), "yyy-mm-dd hh:mm:ss&quot


HTH
Rgds
Geoff

Si hoc legere scis, nimis eruditionis habes

 

[mscallisto]

x = Format(Now(), "yyyy-mm-dd HH:MM:SS&quot

 

[djburnheim]

Working code

***code***

Dim StartDate
Dim EndDate

StartDate = Format(Cells(4, 4).Value - 1, "yyyy-mm-dd HH:MM:SS&quot
EndDate = Format(Cells(4, 4).Value + 5, "yyyy-mm-dd HH:MM:SS&quot

Thanks everybody for their help

 

[mscallisto]

Thanks Kindly for the star but it must be passed back to Rob who initiated the formula.

Thanks again