Replies

You have to invest a bit of time to learn how to use it, but then it works very well indeed.

Thanks all. We’re temporarily using the “CheckYes” option. This works for now until we get either SQL Server Reporting Services figured out or SQL Database Mail scripted.

—aap2—

OK, I see that the problem is simple. I spelled the word “THURSDAY” incorrectly. The query works now!

I suppose this is one of those ID10T errors.

Best regards,

Yes. I do have a startup form. I’ll move the call statement to the OnOpen event and see what happens.

By the way, I will hide the table and hide the database window and disable special keys to prevent SQL credentials from being exposed. If anyone is aware of a more secure way to do this, I would l like to find out. But that is for another post.

Thanks.

As always, worked like a charm!
Thank you.

Thank you. I must have missed that. In order to restrict users to one selection when option 2 (Weekly) is chosen, I suppose I must make a different list box visible where multi-select is set to 0.
Thanks again!

Hans,
I did two things.
1. I defined a variable called strSQL in the beginning of the procedure like this:
Dim strSQL as string

strSQL = “SELECT DISTINCT CM_CLIENT.CHAIN, ”
strSQL = strSQL & “CM_Service_Line_Master.ServiceLineDescription, ”
strSQL = strSQL & “CM_CLIENT.LINE_OF_BUSINES FROM CM_DEBTOR ”
strSQL = strSQL & “INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client ”
strSQL = strSQL & “INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID ”
strSQL = strSQL & “Where CM_CLIENT.CHAIN = ‘” & strChain & “‘”

2. I took the ” =Array(“…”) ” part out of the .CommandText = Array(“…”)
and replaced it with .CommandText = strSQL and it works.

It seems that if you build your query by recording a macro, it inserts the .CommandText = Array(“…”) for some reason.

As always, thanks for your guidance.

I’m attempting to populate Sheet1 with a list of data from our SQL Server 2005 database. The following query works well if I don’t add the WHERE clause.
I keep getting a type mismatch error with the .CommandText = Array line when I add the WHERE clause.

Some other useful info regarding my question:
1. I pass in a code called strChain
2. Each chain has 1 or more service lines
3. I want to return only the service lines for the chain code that is passed in.
4. If you know of a better way to pass in an SQL string to the database, I’m happy to give it a try!

Sub ChainSvcLines(ByVal strChain As String)
On Error GoTo Error_Handler

With ActiveSheet.QueryTables.Add(Connection:= _
“ODBC;DRIVER=SQL Server;SERVER=999.99.99.9;UID=user123;PWD=*********;APP=Microsoft Office 2003;WSID=XXX-XXXXXX” _
, Destination:=Range(“I2”))
.CommandText = Array(“SELECT DISTINCT CM_CLIENT.CHAIN, ” & _
“CM_Service_Line_Master.ServiceLineDescription , CM_CLIENT.LINE_OF_BUSINES ” & _
“FROM CM_DEBTOR INNER JOIN CM_CLIENT ON CM_CLIENT.CLIENT_NUM = CM_DEBTOR.Client ” & _
“INNER JOIN CM_Service_Line_Master ON CM_CLIENT.LINE_OF_BUSINES = CM_Service_Line_Master.ServiceLineID ” & _
“WHERE CM_CLIENT.CHAIN = ‘ ” strChain & ” ‘ “)
.Name = “qryChainLOB”
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
Error_Handler:
MsgBox Err.Number & ” ” & Err.Description

End Sub

As always, any suggestions would be most helpful.
Thanks,

My mistake. It should say strChain which is the value passed in. Sorry about that.

I did what you suggested (Exported, removed, imported) and still had the same issue. I even started with a new workbook and imported all the modules and form. However, because I had to shutdown overnight, when I re-opened the application, the strange behavior was gone. I don’t know why it happened but I am investigating Hans V’s link as well as looking into the code cleaner you mentioned.
Thanks,

Thanks again Hans. Works just fine.

FYI, the GetRealLastCell procedure is a handy piece of code that we use very frequently in most of our Excel apps.
I don’t know who gets credit for writing it. I may have found it here in this forum.
‘=============================================================
Option Explicit
Public RealLastRow As Long
Public RealLastColumn As Long

Public Sub GetRealLastCell()

Range(“A1”).Select

On Error Resume Next

RealLastRow = Cells.Find(“*”, Range(“A1”), xlFormulas, , xlByRows, xlPrevious).Row
RealLastColumn = Cells.Find(“*”, Range(“A1”), xlFormulas, , xlByColumns, xlPrevious).Column
Cells(RealLastRow, RealLastColumn).Select
End Sub
‘=============================================================

Hans,
I did as you suggested on the user PC and compiled the MDB without any problem.
I was able to make what appears to be a working executable (still testing on other user desktops).
I didn’t see any MISSING references but I did notice that on my PC I have references to:
Microsoft Access 11 object library and Microsoft Office 11 object library
whereas our users show:
Microsoft Access 10 object library and Microsoft Office 10 object library

Once again, you have been very helpful!
Thanks,
aap2

Thanks Hans. Can you tell me why this change worked where the original line of code did not?

Thanks Hans. The TimeValue function will help with the calculation portion but I still need a way to determine “AM” or “PM”. It appears that the TimeValue function assumes “AM” if the argument is not stamped. I have decided that looping through a recordset of each persons Finished_Time and counting the changes in the Hours will help determine if the span of time crosses noon. If that is the case, I know that SessionStart is AM and Finished_Time is PM.

I put in this block of code….
=======================================
Sub TestTime()
‘some other dim statements here…

Dim intFirstFinish As Integer
Dim intNextFinish As Integer
Dim intLastFinish As Integer

‘ I open a recordset here called rsdata…
‘ I get the count of records in rsdata. intRecordCount

‘ val function captures the numbers in the hours portion of the string.
intFirstFinish = Val(rsData.Fields(“Finished_Time”))
intLastFinish = Val(rsData.Fields(“Finished_Time”))

For i = 1 To intRecCount

intNextFinish = Val(rsData.Fields(“Finished_Time”).Value)
If intNextFinish intFirstFinish Then
‘calculation of the number of times the hour changes.

intHrs = intHrs + 1

intFirstFinish = intNextFinish

End If
rsData.MoveNext
Next i

Select Case intHrs
‘ I think I need some logic here that says if the number of hour changes is > some number then
‘ SessionStart is AM and Finished_Time is PM
‘ Else Both are AM or Both are PM — I don’t have this logic figured out yet.

End Select

Thanks Hans. This is exactly what I had in mind.

Wendell,
Thanks for the tip. I did try the upsizing wizard and SQL put [square brackets] around the field names instead of _underscores_ . I don’t recall any options in the upsizing wizard that would allow you to customize how Access handles field names. I was told however, that using DTS and pulling data into SQL instead of pushing data out of access might be an option. If anyone has any knowledge on this, let me know.

Thanks,