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...

 

[jhall156]

I think you might need CHAR(34)'s around your text and #'s around your date variables but I'm not certain. JHall

 

[a9]

I have found using single quotes within the SQL strings easier than using the Chr(34) method Especially for multiple selection criteria. For instance:

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 & ";"

I agree you'll need the # to define a date.

 

[mscraig]

If you are using Access 95 or newer put your SQL in one string instead of concantonating several MySql strings:

MySql = "SELECT tblTimeSheets.StartingDate " & _
"FROM tblUser INNER JOIN tblTimeSheets " & _
"ON tblUser.UserID = tblTimeSheets.UserID " & _
"WHERE tbUserID.Username='" & CurrentUser & "'" & _
"AND tblTimeSheets.StartingDate=" & DataChosen & ";"

 

[dodgyone]

Thanks for the help... the SQL now seems fine and the output seems to be correct. The problem is that I'm using DAO to connect to the table but it cannot find the date even though it is there for that user. Does anyone have any suggestions? Cheers...

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

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.EOF = False Then
i = MsgBox("You have already used this date on a previous Time Sheet." & Chr(13) & Chr(13) & "The date chosen cannot be used again.", vbExclamation, "Date Chosen Invalid...&quot
Else
MsgBox "it worked..."
End If

 

[Taff07]

Mscraig is right, alot easier to view in this style.

MySql = "SELECT tblTimeSheets.StartingDate " & _
"FROM tblUser INNER JOIN tblTimeSheets " & _
"ON tblUser.UserID = tblTimeSheets.UserID " & _
"WHERE tbUserID.Username='" & CurrentUser & "' SpaceHere" & _
"AND tblTimeSheets.StartingDate=#" & DataChosen & "#;"

TYhe only adjustments I think are the bold bits above.

Ian

 

[dodgyone]

Re: My last posting... I'm still having troubles with this... I don't think it's the SQL as it appears to look correct when outputted to a msgbox.

Any ideas? Thanks...

 

[a9]

Return to basics - try creating the query using the query designer and get that working. Once that is ok you can view the SQL and copy to your code.

 

[Craig0201]

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 & "#;"

is correct assuming you've got the Username as a text field and startingdate as a date field.

Craig

 

[dodgyone]

The query seems alright but I cannot seem to be able topmove to the record that has the date.

I need to do this as I cannot have duplicate dates for each user. If it isn't EOF then I know it has found something and a warning message will come up telling the user why they couldn't have that date. Is my DAO code alright... this is really stressing me out ;o(

 

[Taff07]

Use rst.recordcount instead. if the recordset has no entries then this will initially be a zero. I think this is what you needed to workout?

Ian

 

[dodgyone]

Thanks Taff... I have used rst.recordcount in an If statement to read:

If Not rst.RecordCount > 1 Then
MsgBox "There was a record found..."
Else
MsgBox "it worked..."
End If

But no matter if there is a date or not in the table it still gives the record found message. Is my DAO alright?

I'm totlaly stuck now... I can't think of anything else to try ;o(

 

[dodgyone]

Oh.. I've noticed the accidental 'Not' in the If statement... it doesn't make any difference though...


Hellllp ;o(

 

[Taff07]

Try it as:

if rst.recordcount = 0 then

msgbox "it worked"

else

msgbox "Record found"

end if

Ian

 

[dodgyone]

Cheers Taff...

Obviously it isn't finding the record from earier on in the code as it cannot find the date in that field... either the sql statement is wrong or the DAO code is wrong!

I'm confused ;o/

 

[Taff07]

What version of Access are you using? if it`s `97, send me the relevent bits of your DB and I`ll have a fiddle with it

Ian
ian.oldreive@barclays.co.uk

 

[Taff07]

I`ve just used the code within a temp db I set up with your tables and fields and it seemed to work fine.

The only thing I can think of that may be going haywire within your side would be that the VB is switching the format of the date. it doesn`t matter what you tell it it reverts to american format date of mm/dd/yy. If this is the case then I have got around it myself but is a little longwinded.

Ian

 

[dodgyone]

I'm using Access 97.... I'm using security though so that wouldn't be straight forward...

Why on earth can't it find the date in the table then ;o/

When using a regular query it does find the data (no probs with the date etc... I'm in the UK) but if this query is put in place of mysql then it says 1 more parameter is required.

Could you possibly send me your temp database?

marcus.jehan@gov.gg

I really appreciate you taking the time to help me ;o)

 

[Taff07]

Before I send you my database, let me check where the date and user variables are derived from and I`ll inlcude a bit of code, to make the date be interpretted correctly.
Do the variables come from a form?, an input box? or a set login variable?

Ian

 

[dodgyone]

Cheers Taff...

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

CurrentUser ... takes the name of the person who has logged onto the database.

Hope this helps...