By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,994 Members | 2,063 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,994 IT Pros & Developers. It's quick & easy.

Variable as a date in SQL definition

P: 98
Can anyone help me? I am getting a syntax error on my strSQL statement. I think it doesn't like the way I am trying use my variable mNewDate. The user originally types the date as a string (102006). I thought I needed to convert to a date to use in the SQL statement. I tried creating a string and it didn't work. Thanks for any help!

Private Sub cmbCaptureLSData_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRate As Variant
Dim strSymbol As String
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim mDate As String
Dim mNewDate As Date

Set db = CurrentDb()
Set rst = db.OpenRecordset(tblName)
Set qdf = CurrentDb.QueryDefs("qryDummy")

mDate = Left(MyValue, 2) & "/" & Mid(MyValue, 3, 2) & "/" & Right(MyValue, 2)
Debug.Print mDate
mNewDate = CDate(mDate)
strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.LocateDate) = #" & mNewDate & "#)) " &
"ORDER BY DailyPrice.Symbol;"

qdf.SQL = strSQL
'DoCmd.OpenQuery ("qrydummy")
If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else

rst.MoveFirst
Do Until rst.EOF
strSymbol = rst!Symbol '** If symbol is field in recordset but use ! **
varRate = DLookup("[MarketPrice]", " & qryDummy & ", "[symbol] = '" & strSymbol & "'")

If IsNull(varRate) Then
rst.Edit
rst!LSRate = 0
rst.Update
Else
rst.Edit
rst!LSRate = varRate
rst.Update
End If

rst.MoveNext
Loop

End If

rst.Close
qdf.Close
Set rst = Nothing
Set db = Nothing
Set qdf = Nothing

End Sub
Oct 30 '06 #1
Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,347
You would be better off using the string var rather than any Date var.
The date in SQL MUST be in US M/D/Y format. I think that the year must be four digits.
Going by the example you gave (102006), I'm guessing you're using D/M/Y format already so try extending the year.

Expand|Select|Wrap|Line Numbers
  1. mDate = Left(MyValue, 2) & "/" & Mid(MyValue, 3, 2) & "/20" & Right(MyValue, 2)
  2. Debug.Print mDate
  3. strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
  4. "FROM DailyPrice WHERE (((DailyPrice.LocateDate) = #" & mDate & "#)) " &
  5. "ORDER BY DailyPrice.Symbol;"
Oct 30 '06 #2

P: 98
My line of code:

mDate = Left(MyValue, 2) & "/" & Mid(MyValue, 3, 2) & "/" & Right(MyValue, 2)
Debug.Print mDate
mNewDate = CDate(mDate)

creates a date of 10/20/2006. Isn't this what you were talking about? Thanks!
Oct 30 '06 #3

NeoPa
Expert Mod 15k+
P: 31,347
But the point is that the date is not important - it's the string it resolves to that's important.
That will be dependant on your system settings which you haven't shared - so I tried to explain all that and warn you away from code that is dependant on such things.
It is, of course, your choice what you do with it.
Oct 30 '06 #4

Andrew Thackray
P: 76
This function will take a date string formatted with DD,MM, YY or D,M,YYYY in any recognised order along with the time in AM/PM or 24hr formt with any kind of delimiter except a space betwween the elements and return a date string that can be used in ans SQL statement enclosed in #'s. IIf it cannot resolve the string to a valid date it returns the string "Invalid"

It will solve your problem

Expand|Select|Wrap|Line Numbers
  1. Function CorrectDateString(Dte As String) As String
  2.  
  3. ' This function corrects date formats
  4.  
  5. Dim Cnt, brk, tlen As Integer
  6. Dim Dateprt, Timeprt As String
  7. Dim D As Date
  8.     If Len(Dte) < 6 Then
  9.         GoTo ErrorTrap
  10.     End If
  11.     brk = InStr(Trim(Dte), " ")
  12.     If brk = 0 Then
  13.         Dateprt = Dte
  14.         Timeprt = ""
  15.     Else
  16.         Dateprt = Left(Dte, brk - 1)
  17.         Timeprt = Right(Dte, Len(Dte) - brk)
  18.     End If
  19.     For Cnt = 1 To Len(Dateprt)
  20.         If Mid(Dateprt, Cnt, 1) < "0" Or Mid(Dateprt, Cnt, 1) > 9 Then
  21.             Mid(Dateprt, Cnt, 1) = "/"
  22.         End If
  23.     Next Cnt
  24.     If UCase(Right(Timeprt, 2)) = "AM" Or UCase(Right(Timeprt, 2)) = "PM" Then
  25.         tlen = Len(Timeprt) - 2
  26.     Else
  27.         tlen = Len(Timeprt)
  28.     End If
  29.     For Cnt = 1 To tlen - 1
  30.         If (Mid(Timeprt, Cnt, 1) < "0" Or Mid(Timeprt, Cnt, 1) > 9) Then
  31.             Mid(Timeprt, Cnt, 1) = ":"
  32.         End If
  33.     Next Cnt
  34.     On Error GoTo ErrorTrap
  35.     D = CDate(Dateprt)
  36.     On Error GoTo 0
  37.     Dateprt = Trim(CStr(Month(D))) & "/" & Trim(CStr(Day(D))) & "/" & Trim(CStr(Year(D)))
  38.     If Len(Timeprt) > 0 Then
  39.         CorrectDateString = Dateprt & " " & Timeprt
  40.     Else
  41.         CorrectDateString = Dateprt
  42.     End If
  43.     Exit Function
  44.  
  45. ErrorTrap:
  46.  
  47.         CorrectDateString = "Invalid"
  48.  
  49. End Function
  50.  
Oct 30 '06 #5

Expert 5K+
P: 8,434
What about using Format(mNewDate,"Short Date")? That way, it's not dependent on a specific format, but is based on Windows settings.

I can't guarantee this will work, of course - would have to be tested.

Even simpler - use mDate instead of mNewDate in your SQL.
Oct 31 '06 #6

Andrew Thackray
P: 76
The problem with putting a date string in an SQL statement is that it only accepts formats of the type

#MM/DD/YYYY HH:MM:SS#

or that will resolve into that type.

using the system setting provides a sring in the local date format wihich may not be suitable for SQL
Oct 31 '06 #7

Expert 5K+
P: 8,434
The problem with putting a date string in an SQL statement is that it only accepts formats of the type #MM/DD/YYYY HH:MM:SS# ...
Sorry, I didn't realise it was so inflexible. What a pain!
Oct 31 '06 #8

NeoPa
Expert Mod 15k+
P: 31,347
Sorry, I didn't realise it was so inflexible. What a pain!
Didn't read my earlier post then Killer :(
This is part of the ANSI standard for SQL I'm afraid.
Oct 31 '06 #9

P: 98
Thank you for all your help!! The code I have included below actually works. The date is subbed in fine as is the "qryDummy" reference. My "problem" now is that when I run it with RST having 6250 records and qryDummy having 5000 records, it ran/worked for almost 5 minutes and only got thru 1000 records of updating. Is my coding inefficient? I checked and there is no unnecessary looping. Could the DLOOKUP function be a time intensive function? Any help here would be appreciated. Maybe this is just how long it takes, but it doesn't seem right on my brand new laptop. Thank you again!!

Private Sub cmbCaptureLSData_Click()

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim varRate As Variant
Dim strSymbol As String
Dim strSQL As String
Dim qdf As DAO.QueryDef
Dim mDate As String
Dim mNewDate As Date
mDate = Left(MyValue, 2) & "/" & Mid(MyValue, 3, 2) & "/" & Right(MyValue, 2)
Debug.Print mDate
mNewDate = CDate(mDate)
strSQL = "SELECT DailyPrice.LocateDate, DailyPrice.Symbol, DailyPrice.MarketPrice " & _
"FROM DailyPrice WHERE (((DailyPrice.LocateDate) = #" & mNewDate & "#)) " & _
"ORDER BY DailyPrice.Symbol;"
Debug.Print strSQL

Set db = CurrentDb()
Set rst = db.OpenRecordset(tblName)
Set qdf = CurrentDb.QueryDefs("qryDummy")
qdf.SQL = strSQL

If rst.BOF And rst.EOF Then
MsgBox "No records to process"
Else

rst.MoveFirst
Do Until rst.EOF
strSymbol = rst!Symbol '** If symbol is field in recordset but use ! **
varRate = DLookup("[MarketPrice]", "qryDummy", "[symbol] = '" & strSymbol & "'")

If IsNull(varRate) Then
rst.Edit
rst!LSRate = 0
rst.Update
Else
rst.Edit
rst!LSRate = varRate
rst.Update
End If

rst.MoveNext
Loop

End If

rst.Close
qdf.Close
Set rst = Nothing
Set db = Nothing
Set qdf = Nothing

End Sub
Oct 31 '06 #10

Post your reply

Sign in to post your reply or Sign up for a free account.