7 Replies - 913 Views - Last Post: 18 April 2013 - 06:03 PM Rate Topic: -----

#1 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Dealing with dates in the SQL strings

Posted 15 April 2013 - 11:22 PM

I am looking at adjusting my SQL string to deal with dates but somehow I keep getting errors and recently I am getting the type mismatch error. The error shows up in this line ".Open "Select Distinct ShiftNumber , [date] From Outwards where ShiftNumber >= " & txtStartShift.Text And Date >= " & ReportStartDate & """ "and I am suspecting that its something to do with my colons and apostrophees any help on how I can go about this?("" and ')


 Set rs = New ADODB.Recordset
With rs
.ActiveConnection = Con
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic

Dim ReportStartDate As Date
If txtStartDate.Text <> "" Then
ReportStartDate = txtStartDate.Text
End If
.Open "Select Distinct ShiftNumber , [date] From Outwards where ShiftNumber >= " & txtStartShift.Text And Date >= " & ReportStartDate & """

End With


Set DataGrid2.DataSource = rs





Is This A Good Question/Topic? 0
  • +

Replies To: Dealing with dates in the SQL strings

#2 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Dealing with dates in the SQL strings

Posted 15 April 2013 - 11:41 PM

where ShiftNumber >=" & txtStartShift.Text " And Date >=" & ReportStartDate & "


if your (shift number) and (date) field is not numeric and also if date is a textfield so uhave to use .text
than use


where ShiftNumber >='" & txtStartShift.Text & "'And Date >='" & ReportStartDate & "'



thats for vb10

This post has been edited by kai_itz me: 15 April 2013 - 11:50 PM

Was This Post Helpful? 1
  • +
  • -

#3 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: Dealing with dates in the SQL strings

Posted 15 April 2013 - 11:52 PM

Hey thanks, you pointed me in the right direction and it eventually worked out and I got some help Here


Private Sub DataGrid1_DblClick()
txtStartShift.Text = DataGrid1.Columns(0).Text
txtStartDate.Text = DataGrid1.Columns(1).Text

 
 Set rs = New ADODB.Recordset
With rs
.ActiveConnection = Con
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic

Dim ReportStartDate As Date
If txtStartDate.Text <> "" Then
ReportStartDate = txtStartDate.Text
End If

.Open "Select Distinct ShiftNumber , [date] From Outwards where ShiftNumber >= " & txtStartShift.Text & " And [date] >= #" & ReportStartDate & "# "

End With


Set DataGrid2.DataSource = rs


End Sub




Was This Post Helpful? 0
  • +
  • -

#4 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Dealing with dates in the SQL strings

Posted 16 April 2013 - 09:33 PM

It's important to mention which database you are using when you have a problem. The SQL syntax differs a bit between them. For example, the # date delimiter works in Access but not in SQL Server. Keep that in mind if you decide to migrate.
Was This Post Helpful? 2
  • +
  • -

#5 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: Dealing with dates in the SQL strings

Posted 17 April 2013 - 07:23 AM

Okay, so I fixed that one but now on another attempt I have question with a sslightly different emphasis. So in select statement I formatted the dates in my column but now when I want to use those dates in my other select statement then i get a syntax error. The string I used is shown below

    strSQL = "Select Distinct ShiftNumber & '     ' & format(date, 'ddd dd mmm yyyy') as yre  From Outwards"   ' set ascending order



and the string thats giving the syntax error is:
    strSQL = "Select Distinct ShiftNumber & '     ' & format([date], 'ddd dd mmm yyyy') as yre  From Outwards where ShiftNumber>" & txtStartShift.Text & " AND [date] > #" & Text1.Text & "#"



An example of the date I am dealing with is : Sat 06 Apr 2013. Is there any way for me to convert this date to a short date of ms access accceptable format i.e that can work in my code
Was This Post Helpful? 0
  • +
  • -

#6 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Dealing with dates in the SQL strings

Posted 17 April 2013 - 11:28 PM

Once you use the format function, the result is a string, not a date. So, the type of the field in your result set is actually a string. So, you need to convert it back to a date to perform any type of date logic on it.

Look into the CDate function in Access SQL (CONVERT function in SQL Server).
Was This Post Helpful? 0
  • +
  • -

#7 kai_itz me  Icon User is offline

  • D.I.C Head

Reputation: 28
  • View blog
  • Posts: 159
  • Joined: 03-August 12

Re: Dealing with dates in the SQL strings

Posted 17 April 2013 - 11:45 PM

Format(Now, "short date")


in place of now you can use your date field.

it gives the result like what you said (Sat,06 Apr, 2013 to shorter format) in sql.
i hope it works for you

This post has been edited by kai_itz me: 18 April 2013 - 09:13 PM

Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 574
  • View blog
  • Posts: 2,989
  • Joined: 19-May 09

Re: Dealing with dates in the SQL strings

Posted 18 April 2013 - 06:03 PM

I think the OP is asking if he can turn the formatted date back into a date so he can stick it in a date field. Not sure, though.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1