Compile Error: Expected: line number or label or...

[rabozza]

Good afternoon all, I'm running into this error and can't for the life of me figure out how to fix it.

Compile Error: Expected: line number or label or statement or end of statement

This is the code I am using (its in an if statement if that helps).

"Update tblLogTemp set LogOffDate(Sql1) = LOD(sql2), LogOffTime = LOT(sql2)" &_
"where (SELECT top 1 dbo_Logoffs.Operdate as LOD, dbo_Logoffs.opertime as LOT" &_
"FROM dbo_Logoffs INNER JOIN tblLogTemp ON dbo_Logoffs.FullName = tblLogTemp.User" &_
"Where dbo_Logoffs.OperDate >= tblLogTemp.LogOnDate and dbo_LogOffs.OperTime >= tblLogTemp.LogOffTime);"

Any ideas?

[ajetrumpet]

immediately I see 2 things:

• replace "&_" with "& _"
• at the end of each line, add a space before you close the statement portion with a double quote:
  
  

  "statement " & _

that's the cause of the error

[rabozza]

I did both of those things (i tried the & _ as opposed to the &_ previously but forgot to change it back), but the space before the " I didn't realize mattered.

But I am getting the same error still.

[ajetrumpet]

what's the entire code? and what line errors out? by default, it should highlight one in yellow. If it doesn't, go into the options and push the radio button that says "break on all errors".

looking at the statement itself, I've never seen anything like it. what are you doing exactly? It looks like you're trying to update a function WITH a function??

And why is there no comparison value on the right side of your first WHERE clause?

I'm having a heck of a time reading and interpreting this sql. I might not be the best person to help you here...

[rabozza]

I don't use VBA often (in all honesty I haven't written anything serious in the access VBA in 6 years so my VBA knowledge, at best, can be considered rusty. At worst it can be considered beginner level) so I am kind of grasping at straws with what i'm trying to do..

Quick breakdown of what I am trying to do.

I need to pull Logon Dates/TImes from Table1, and Logoff dates/times from Table2. There is no direct corellation between the 2 so basically what i'm doing in order to create a report listing them both is using a temp table to house all 4 dates/times and create a report based on it.

So Sql1 fills the temp table with user, logon date, logontime. And the Sql i'm attempting to write is (theoretically) going to update that table with the logoff date, and logofftime.

So in theory

if recordset.eof = false
then Update TempTable set Logoffdate = Table2.logoffdate, logofftime = table2.logofftime where temptable.user = table2.user, logoffdate >= date and <= date


Since there is no direct link between the records basically what I am having to do is select the first matching record (Select Top 1 assuming it exists) where the dates kind of match up.

The date field houses the date, but the time field is formatted incorrectly and houses the date and time both (unfortunately the date it shows though is the default date which is 1/1/1900. Realistically the date/time should just be formatted into 1 field, but whoever designed it set it up in 2 different fields with the wrong formats so its causing me a terrible terrible headache.)

Sorry about this wall of text. Maybe I'm just overcomplicating it?

*edit* the comparison value after the first 'where' clause should be covered with the select statement inside the parethesis unless i am mistaken.

[ajetrumpet]

I think you are overcomplicating, actually.

I might have a better suggestion. can you post screenshots of both tables' structures?

you said there's no correlation between the two? WHY are you doing it? preparing data for periodic paychecks for staff? I would assume with something like that, the consolidation of the data would have to be correlated somehow, like one record per punch in, punch out?

regardless, post those tables and let us have a look. post ss's of both the originals, as well as what you're new consolidated one is supposed to look like.

[rabozza]

I'm doing it because the owner of the company I work for wants a report to show the hours of the salary employees are logged in as a 'check' to see if they're coming in or not. The report will be unreliable, inaccurate, and the dates will be assumed at best, but he doesn't care he wants to see it.

The reason I say there is no correlation is because the user can login multiple times without having logged out once. The record input is based on login and logoff scripts. If neither is run then it won't record a new record.

Here is the layout of the tables. Both tables have the same layout.

ID as Autonumber (PK)
OperDate as Date/Time
Opertime as Date/Time
FullName as Text
Host as Text
IP as Text
Asset as Text
Oper as Text

Now there is no direct relation between the 2 tables, but you can 'assume' that the host and ip will be the same for login/logoff's but you will run into cases where the dates don't match up (for instance on one example there were 24 logins, and 16 logoffs).

Which is why i'm running the query to pull what I would consider an 'assumed' match. To pull the match it's a simple select query that just goes 'select top 1 date, time from logoffs where logoff.date >= logondate and user = user). The top 1 is just to it pulls the first possible match as opposed to 4 possible matches and updates itself repeatedly.

[ajetrumpet]

well first of all sir, you didn't post any screenshots, which is what I asked about.

is that not possible? simply copy the tables, delete all the data and print the screen. that would help, but the explanation works fine I suppose. But what about the 3rd table? I personally still can't my head around it.

how about posting the script you're using to do this with? You said it was in the middle of an ''if'' statement. post it please.

If you're wanting to execute UPDATE statements everytime you go through a loop, I would be more inclined to tell you to open 2 recordsets and write values from one to the other, simply because it probably uses less memory stacking and processor resource. If too much resource is requested from a machine too fast, strange things can come out of it, and sometimes it's a computer crash. As a matter of fact, I've had this happen to me with vba scripts and I've gotten back garbage that didn't make any sense and wasn't even related to the output I requested via the script.

[NoellaG]

Hi Rabozza,

maybe try to start a total different approach: why not make a union query combining the logins and logouts in 1 dataset. Something like:

SELECT tblLogin.Host, tblLogin.IP, tblLogin.FullName, tblLogin.OperDate, tblLogin.OperTime, "Login" as flfType
FROM tblLogin
UNION
SELECT tblLogout.Host, tblLogout.IP, tblLogout.FullName, tblLogout.OperDate, tblLogout.OperTime,  "Logout" as flfType
FROM tblLogout
ORDER BY  tblLogin.OperDate, tblLogin.OperTime, tblLogin.Host, tblLogin.IP, tblLogin.FullName;

The sorting can be different, according to your needs of course. Then you can use this dataset as a base for a report (maybe use a crosstab?)

succes
NG