Missing Operator?

[Moonshine]

Hi All, with this code..

Dim dbscurrent As Database
Dim rstUserName As Recordset

Set dbscurrent = CurrentDb
Set rstUserName = dbscurrent.OpenRecordset("select * from [tbl User] where [User Name] = " & strLogonUser & " and [Logged In] = " & strLogonTime & "")

I get an error telling me "Syntax Error (missing operator) and so on..."

Im bascially trying to get it to find all the records in tbl User that match the Field in the table named "User Name" = strLogonUser and the field in the table named "Logged In" = strLogonTime

So it should only find the one record...

Any clues as whats wrong with the coding?

 

[FoFa]

Try:
Set rstUserName = dbscurrent.OpenRecordset("select * from [tbl User] where [User Name] = '" & strLogonUser & "' and [Logged In] = '" & strLogonTime & "'")

 

[Moonshine]

Hi

That was a damn fast reply

With that one i get a data type mismatch :/ I have the fields in the table set to Date/Time and could do with it left like that..

Is it possible or should i change the fields to text ?

 

[directormac]

Try:
Set rstUserName = dbscurrent.OpenRecordset("select * from [tbl User] where [User Name] = '" & strLogonUser & "' and [Logged In] = '" & strLogonTime & "'")

May need # signs to let Access know that this is formatted date info? Try:

Set rstUserName = dbscurrent.OpenRecordset("select * from [tbl User] where [User Name] = '" & strLogonUser & "' and [Logged In] = '#" & strLogonTime & "#'")

 

[Moonshine]

jsut tried that one directormac, and it gives me the "Data type mismatch in expression)

Forgot to say if you didnt know, that the Logged In and Logged Out and the strLogonTime are Now() so it gives you 20/03/2003 15:49:24

...

 

[directormac]

Well, poop. Time to start grasping at straws:

Check the fields in your table design, make sure they are set to the types you are calling for.

Open up the VBA and try building that query in a string variable before you get to the open statement. Put a watch on the string variable and step through the code. This will make sure that you're saying what you (and we) think you're saying. If looks correct, try issuing the set command with the open property using that string instead.

Get rid of the spaces in your table names.

Cut down on confusing quote marks by adding a public constant to handle them. At the very top of your VBA, insert the line:

Public Const QUOTE As String = """"

which will allow you to rewrite the statement as:

"SELECT * from tblUser WHERE !UserName = " & QUOTE & strLogonUser & QUOTE & "AND !LoggedIn = #" & strLogonTime & "#")

Actually, having done that, it occurs to me that the whole problem may be that you're feeding SQL a date value with quotes around it. Get rid of those. Try:

Set rstUserName = dbscurrent.OpenRecordset("SELECT * from [tbl User] where [User Name] = '" & strLogonUser & "' and [Logged In] = #" & strLogonTime & "#")

--Grasping Mac

 

[Moonshine]

Actually, having done that, it occurs to me that the whole problem may be that you're feeding SQL a date value with quotes around it. Get rid of those. Try:

Set rstUserName = dbscurrent.OpenRecordset("SELECT * from [tbl User] where [User Name] = '" & strLogonUser & "' and [Logged In] = #" & strLogonTime & "#")

--Grasping Mac [/B]

Looks like that one worked fine

Just testing it fully now, thanks!!