SQL... is the syntax correct? 2

[dodgyone]

Could somebody please check my SQL code...

MySql = "SELECT tblTimeSheets.StartingDate "
MySql = MySql & "FROM tblUser INNER JOIN tblTimeSheets ON "
MySql = MySql & "tblUser.UserID = tblTimeSheets.UserID "
MySql = MySql & "WHERE tblUser.Username=" & CurrentUser
MySql = MySql & " AND tblTimeSheets.StartingDate=" & DateChosen & ";"

Does it seem right or are there any errors?

Many thanks...

 

[dodgyone]

Cheers Taff...

datechosen ...comes from a calendar OCX (MSCAL.Calendar.7)

CurrentUser ... takes the username of the person who has logged onto the database (using security)

Hope this helps...

 

[dodgyone]

Cheers Taff...

datechosen ...comes from a calendar OCX (MSCAL.Calendar.7)

CurrentUser ... takes the username of the person who has logged onto the database (I'm using security)

Hope this helps...

 

[Taff07]

The name doesn`t matter to much. It comes as a string. The calender is slightly different. Try the following bit of code to define the date at the start of your routine.

Dim ProperDate as Date

properdate = format([Datechosen],"DD MMM YYYY"

then replace your date call within the sql to proper date.

This is the code that worked on my DB.

Sub tests()
'create the connection object
Dim daTechosen As Date
Dim cuRrentuser As String
Dim db As Database
Dim rst As Recordset
Dim MySql As String
'sets the current database
Set db = CurrentDb()

cuRrentuser = "Ian" ' Replace this with your User Call
daTechosen = #1/13/02# ' Put the Date line above in here

MySql = "SELECT tblTimeSheets.StartingDate FROM " & _
"tblUser INNER JOIN tblTimeSheets ON " & _
"tblUser.UserID = tblTimeSheets.UserID WHERE " & _
"tblUser.Username=" & "'" & cuRrentuser & "'" & " AND tblTimeSheets.StartingDate=" & "#" & daTechosen & "#" & ";"

MsgBox MySql

'runs the sql statement
Set rst = db.OpenRecordset(MySql)

If rst.RecordCount = 0 Then
MsgBox "it worked"
Else
MsgBox "Record found"
End If

End Sub

Hope this works

Ian

 

[dodgyone]

Tried it Taff but the same thing... the recordcount still doesn't get a value other than 0. Obviously the sql is wrong or the DAO is wrong... not sure what though!

I'm referencing DAO 3.6 Object Library... is that the correct one?

I just cannot understand what's going on.... the project deadline is looming and I can't get over this hurdle ;o(

 

[Taff07]

Ok just to check the way the date is being taken. After you have set your sql put in the following line and see what is shown.

msgbox chosendate
msgbox format(chosendate,"DD MMMM YYYY&quot


If this comes out as expected then I have no idea without seeing it myself.

Any way in which you can export teh relevent form and stuff into a seperate Db to send to me?

Ian

 

[RichardF]

going back to ians code above, have you tried this :

Set rst = db.OpenRecordset(MySql, dbOpenSnapshot)
rst.MoveFirst
rst.MoveLast

if rst.RecordCount ....

also i try to refrain from using # for dates, i prefer using datevalue(string), like :

MySql = "SELECT tblTimeSheets.StartingDate FROM " & _
"tblUser INNER JOIN tblTimeSheets ON " & _
"tblUser.UserID = tblTimeSheets.UserID WHERE " & _
tblUser.Username = '" & cuRrentuser & "' AND "
& tblTimeSheets.StartingDate & " = " & _
"datevalue('" & format$(daTechosen,"dd/mm/yy&quot & _
"');"

good luck.

 

[dodgyone]

Thanks RichardF that did the trick... you are a star ;o)

Cheers Taff as well for having the patience to help and follow this problem through to the end...

Stars for the both of you ;o)