SQL statement error on certain computers (1 Viewer)

[joeyo34]

I have been developing an FE/BE database that will run on multiple machines at one time to log tasks during the work day. My program has finally come around to a point where I need to begin testing it but as of yesterday the second machine I put I put it on errored. The database came up alright but when I tried to log a task (which is done, in part, with an SQL statement) it gave me an error stating;

"Function is not available in expressions in query expression '(((Sample_TM_Table_1.Date_In)=Date()) AND ((Sample_TM_Table_1.User)=CurrentUser()) AND ((Sample_TM_Table_1.Time_In) Is Not Null AND ((Sample_TM_Table_1.Time_Out) Is Null)))'.

Is there a module that this particular machine could be missing or an .ocx file? I only ask about the .ocx file because I know that is causing a problem with one of the ActiveX Controls on that same machine. MSCOMCT2.OCX and/or MSCOMCTL.OCX are the.ocx files that are not on that machine that were calling for errors on a previous database that utilized the Date/Time Picker. Could this file (or another) possibly be the reason the SQL statement is not running? The SQL statement is....

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim str_SQL1 As String
str_SQL1 = "UPDATE Sample_TM_Table_1 SET Time_Out = #" & Now() & "# WHERE IsNull(Time_Out) AND (Time_In) > 0 AND User = '" & Me.User & "';"
DoCmd.SetWarnings False
DoCmd.RunSQL str_SQL1
End Sub

The SQL statement was built off of suggestions from this forum and I really do not know much about SQL statements in general. I understand the basis of them but have a very shallow knowledge of what makes them work and what they are reliant upon so I am not real sure where to start in troubleshooting this particular problem. Any ideas?

Thansk in advance!!!

 

[joeyo34]

Also, I suppose I should note that the computer that errored is running the same version of Access as the computer that I built the database on as well!

 

[RoyVidar]

The error you're receiving, doesn't seem to come from the SQL statement you've posted.

One tip is to use the following after assigning the SQL string to a variable, but before executing it:

Debug.Print str_SQL1

Then you can pick up/study the resulting SQL in the immediate pane (ctrl+g). If it's OK, you should be able to copy/paste it into the SQL view of the query builder, and it should run.

Your SQL statement would barf where I live, because it will only run correctly on US settings. See for instance the following article by Allen Browne for why http://allenbrowne.com/ser-36.html

 

[ByteMyzer]

Try the following modification of your SQL statement declaration:

str_SQL1 = "UPDATE Sample_TM_Table_1" _
    & " SET Time_Out = " & CDbl(Now) _
    & " WHERE IsNull(Time_Out)" _
    & " AND (Time_In) > 0" _
    & " AND User = '" & Replace(Me.User, "'", "''") _
    & "';"

This will take care of two things:
* eliminate regional date difference discrepancies
* accommodate User Names with an apostrophe in the name, like Mike O'Shea

 

[RoyVidar]

Assigning CDbl(Now) for a date into a string passed to Jet will will barf several places in the world, including where I live. Allen Brownes formatting are safe, the same is ISO 8601 (yyyy-mm-dd hh:nn:ss).

Possibly Str(CDbl(Now)) would work.

 

[joeyo34]

Thanks for the quick replies all. I understand the SQL statement is less than perfect. Luckily for me this is strictly an internal database only used in the U.S. (by a very small group of individuals) so there shouldn't be any problem coming from the formatting. After a discussion with our IT guy yesterday it turns out that the computer that had the errror is a bit of a hand-me-down in that it wasn't setup by him and all the necessary modules and packages were not downloaded with Office. Still looking for the specific reason for this problem but are getting closer. I will take a look at my SQL statement anyhow and work on building better ones in the future! Thanks everyone....keep it coming.

 

[joeyo34]

ByteMyzer,

I made the suggested changes. I haven't had a chance to test it on the erroring computer yet, however. What does CDbl(Now) mean? What situations should I use this? Thanks again!

 

[ByteMyzer]

Assigning CDbl(Now) for a date into a string passed to Jet will will barf several places in the world, including where I live.

No, it won't. I've written MS Access applications that I've deployed to run in US, UK and Malaysia, and this technique works for all three regional settings. In the example I used, Visual Basic automatically converts the value from CDbl(Now) to a string when it assigns it to a string variable.

 

[RoyVidar]

Using the CDbl function on a date/time, converts it to a numeric, where the integer part, represents the number of days since 12/31/1899, and the decimal part the hour of day, say 0.5 is noon.

> No, it won't. I've written MS Access applications that I've deployed to run in US, UK and Malaysia, and this technique works for all three regional settings. In the example I used, Visual Basic automatically converts the value from CDbl(Now) to a string when it assigns it to a string variable.

If you'd also deployed in Norway, you would have known that you are wrong. If you ask, I will tell why.

 

[ByteMyzer]

If you'd also deployed in Norway, you would have known that you are wrong. If you ask, I will tell why.

The implication being: If I don't ask, you won't tell why. That's a rather snooty attitute to take, I must say.

In any event, as joeyo34 has stated that this is "strictly an internal database only used in the U.S. (by a very small group of individuals)", CDbl(Now) will work perfectly for this particular application.

 

[RoyVidar]

> The implication being: If I don't ask, you won't tell why. That's a rather snooty attitute to take, I must say.

Snooty?

I tell you what I am. I am Norwegian. That is listed in my profile, and has been, I think since I registered here back in 2000. Even though I didn't start with Access until 1993/94, I think it would be fair to say I have some experience with Access/Jet. Since I am Norwegian, I do know the Norwegian date and decimal number formats, and how they work if you stuff them into a string and feed them to the Jet engine without proper preparations (it doesn't).

You could have chosen a more professional approach, for instance by asking or requesting what the problem with your "solution" was, or asked for steps to reproduce the behaviour, but no. You chose to flat out refuse there could be anything wrong with your complete and final solution to the whole date problem.

In doing so, you are calling me either a liar or an ignorant, with respect to regional settings I deal with every day, and has practically grown up with. Your list of settings that you have experience with, does not include Norway, nor does it include any of the many countries where this will barf.

While you do not back up your "solution" at all, you just state that it's supposed to "eliminate regional date difference discrepancies" (which it doesn't), I at least backed up my statement with saying it doesn't work where I live, with my Regional Settings, which I incidently am very familiar with.

You are rather correct that this does not make me jump up an down in excitement at the prospect of explaining this to you, at least not without you moderating your statements a bit. In addition to not being very comfortable with your attitude and refusal of the possibility that you might be wrong, this is a quite common issue for those of us who have to deal with international settings, it is discussed throughout fora and the usenet by more people than me. If I recall correct, I've mentioned it here during the last week in some thread.

And - it's so easy to test - simply open any database having such statement. Open Control Panel and Regional whatever it's called these days, and switch to Norwegian (Bokmal).

Then run your statement - BAM - get your RT 3144/3075...

Or try some settings a bit closer to home, what about, say French-Canadian, Costa Rica, Venezuela ... (for the latter two, I think the settings are listed under Spanish).

In any event, as joeyo34 has stated that this is "strictly an internal database only used in the U.S. (by a very small group of individuals)", then there is no point whatsoever using CDbl(Now), firstly, because Jet is happy with both US date and CDbl(Now) so why bother?, secondly calling a function in the concatenation introduces an unnecessary extra step/overhead, thereby it's only functionality in this setting, is to waste recourses.

As a third element, there are other issues with passing a long or double to a database, too, have you thought about the fact that different databases may have different base date?

SQL-Server: SELECT CAST(39470 AS DATETIME) -> 01/25/2008 00:00;00
Access/Jet: SELECT FORMAT((39470, "mm\/dd\/yyyy") -> 01/23/2008

Funnily enough, I think you might be safe if you allow Jet to handle SQL server "magic number" dates, but you'll be two days off with an ADO/OLEDB approach

So, lets be clear. If you want to work with international dates, CDbl is not safe. In addition, the usage of "semi magic" numbers for dates, might under some circumstances introduce very hard to find/debug anomalities - for instance with SQL server, dates being two days off.

Much safer to use established standards such as what I referred to earlier, the link to long time MS Access MVP Allen Brownes suggestion (which, I think is the format Jet uses internally) and ISO 8601. These will work on all settings.

 

[ByteMyzer]

joeyo34,

I made an oversight in the VBA/SQL statement I provided you. The revised statement should read (note the added paranthesis):

str_SQL1 = "UPDATE Sample_TM_Table_1" _
    & " SET Time_Out = [b]([/b]" & CDbl(Now) _
    & "[b])[/b] WHERE IsNull(Time_Out)" _
    & " AND (Time_In) > 0" _
    & " AND User = '" & Replace(Me.User, "'", "''") _
    & "';"

As RoyVidar generously pointed out, the statement as I originally posted it will cause syntax issues where the regional settings transpose the period and the comma in their respective numeric placeholder roles. Here, as I am posting it now, is how I have implemented it in my applications in the past. I did not proofread my original reply to your post carefully enough, or I would have noticed the missing paranthesis. Fortunately, in the case of your application, it would not have adversely affected its behavior.

 

[joeyo34]

I was finally able to get some time with our IT guy and had him copy the MSCOMCT2.OCX file into the system32 folder of the problem computer. This fixed the problem. I am not sure why this was a particular issue but apparently this file is not installed with or after Office 2003 so if anyone has similar issues you might look into this. Also, if anyone has any ideas of how this .ocx file is related to Update Statements I would love to be informed. Thanks again to everyone for all their help.