Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Chriss Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Background Refersh Error 1

Status
Not open for further replies.
woodyinoz

woodyinoz

IS-IT--Management
Jan 8, 2002
215
GB
Hi all,

I'm trying to run a query that uses two date parameters from user input. This is my code for the query:

Code: 
    With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SQLSvr;Description=Connection to SQL Server (SQLSVR1);APP=Microsoft Office 2003;WSID=SWOODWARD;DATABASE=FMDB;Network=DBMSSO" _
        ), Array("CN;Trusted_Connection=Yes")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT LABOR_TICKET.TRANSACTION_DATE, LABOR_TICKET.WORKORDER_BASE_ID, LABOR_TICKET.RESOURCE_ID, LABOR_TICKET.EMPLOYEE_ID, LABOR_TICKET.HOURS_WORKED, SHOP_RESOURCE.BUR_PER_HR_RUN, EMPLOYEE.FIRST_NAME, " _
        , _
        "EMPLOYEE.LAST_NAME" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.EMPLOYEE EMPLOYEE, FMDB.dbo.LABOR_TICKET LABOR_TICKET, FMDB.dbo.SHOP_RESOURCE SHOP_RESOURCE" & Chr(13) & "" & Chr(10) & "WHERE LABOR_TICKET.RESOURCE_ID = SHOP_RESOURCE.ID AND LABOR_TICKET.EMPLOY" _
        , _
        "EE_ID = EMPLOYEE.ID AND ((LABOR_TICKET.WORKORDER_LOT_ID='SO1') AND (LABOR_TICKET.CREATE_DATE Between {ts '" & Format(Date1, "dd-mm-yyyy hh:nn:ss") & "'} And {ts '" & Format(Date2, "dd-mm-yyyy hh:nn:ss") & "'}) OR (LABOR_TICKET.WORKORDER_LOT_ID='FO1')" _
        , _
        " AND (LABOR_TICKET.CREATE_DATE Between {ts '" & Format(Date1, "dd-mm-yyyy hh:nn:ss") & "'} And {ts '" & Format(Date2, "dd-mm-yyyy hh:nn:ss") & "'}))" _
        )
        .Name = "Query from SQLSvr"
        .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

Unfortunately I'm getting a General ODBC error when I run it and the error is highlighting the line

Code: 
.Refresh BackgroundQuery:=False
[\Code]

Does anyone have any ideas on this?

Cheers,

Woody
 
Sort by date Sort by votes
Means there is something wrong with your SQL. That line is the line that actually executes the query - until that point, you can set any properties to anything you like - it's just that they'll fall over when you try and execute the query

not 100% sure but is:

Format(Date2, "dd-mm-yyyy hh:nn:ss"

really what you want to format the date as ???

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
No! It wasn't! But my code still falls over when I change the nn to mm!

Any other ideas people?!
 
Upvote 0 Downvote
Anyway, nn is perfectly legal for minutes ...
 
Upvote 0 Downvote
fair enough - there is still something wrong with the SQL or the connection though - being as the connection looks recorded, I'm going for the SQL itself

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Yep the connection is recoreded..... Any other ideas?
 
Upvote 0 Downvote
yup - there is something wrong with your SQL - as a starter for 10:

"EMPLOYEE.LAST_NAME" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.EMPLOYEE EMPLOYEE,

has no space between Employee.Last_Name & FROM.

Also brings up the question of why you are introducing character returns to a string ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
The SQL was generated automatically from recording a query and setting it up in MS Query. Until I put the date paramters in the query was running fine.
The date parameters replaced two fixed dates which worked but I wanted the user to be able to enter two dates instead.
 
Upvote 0 Downvote
Apologies then - can you use a debug.print statement to show how the SQL looks when the dates have been entered and it has been processed ?

Destination:=Range("A1"))
.CommandText = Array( _
"SELECT LABOR_TICKET.TRANSACTION_DATE, LABOR_TICKET.WORKORDER_BASE_ID, LABOR_TICKET.RESOURCE_ID, LABOR_TICKET.EMPLOYEE_ID, LABOR_TICKET.HOURS_WORKED, SHOP_RESOURCE.BUR_PER_HR_RUN, EMPLOYEE.FIRST_NAME, " _
, _
"EMPLOYEE.LAST_NAME" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.EMPLOYEE EMPLOYEE, FMDB.dbo.LABOR_TICKET LABOR_TICKET, FMDB.dbo.SHOP_RESOURCE SHOP_RESOURCE" & Chr(13) & "" & Chr(10) & "WHERE LABOR_TICKET.RESOURCE_ID = SHOP_RESOURCE.ID AND LABOR_TICKET.EMPLOY" _
, _
"EE_ID = EMPLOYEE.ID AND ((LABOR_TICKET.WORKORDER_LOT_ID='SO1') AND (LABOR_TICKET.CREATE_DATE Between {ts '" & Format(Date1, "dd-mm-yyyy hh:nn:ss") & "'} And {ts '" & Format(Date2, "dd-mm-yyyy hh:nn:ss") & "'}) OR (LABOR_TICKET.WORKORDER_LOT_ID='FO1')" _
, _
" AND (LABOR_TICKET.CREATE_DATE Between {ts '" & Format(Date1, "dd-mm-yyyy hh:nn:ss") & "'} And {ts '" & Format(Date2, "dd-mm-yyyy hh:nn:ss") & "'}))" _
)
DEBUG.PRINT .COMMANDTEXT
.Name = "Query from SQLSvr"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False

etc etc........

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Nothing happens when I put that piece of code in! I'm struggling a bit here!
 
Upvote 0 Downvote
it will output the result of the SQL string to your "IMMEDIATE" window - if you do not have it open, press CTRL + G

the actual text of the SQL should be in there, along with the entered dates - simply copy it out and paste it here

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Hmmm.... It seems that the dates aren't being picked up in the query...

Code: 
SELECT LABOR_TICKET.TRANSACTION_DATE, LABOR_TICKET.WORKORDER_BASE_ID, LABOR_TICKET.RESOURCE_ID, LABOR_TICKET.EMPLOYEE_ID, LABOR_TICKET.HOURS_WORKED, SHOP_RESOURCE.BUR_PER_HR_RUN, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME
FROM FMDB.dbo.EMPLOYEE EMPLOYEE, FMDB.dbo.LABOR_TICKET LABOR_TICKET, FMDB.dbo.SHOP_RESOURCE SHOP_RESOURCE
WHERE LABOR_TICKET.RESOURCE_ID = SHOP_RESOURCE.ID AND LABOR_TICKET.EMPLOYEE_ID = EMPLOYEE.ID AND ((LABOR_TICKET.WORKORDER_LOT_ID='SO1') AND (LABOR_TICKET.CREATE_DATE Between {ts ''} And {ts ''}) OR (LABOR_TICKET.WORKORDER_LOT_ID='FO1') AND (LABOR_TICKET.CREATE_DATE Between {ts ''} And {ts ''}))

The dates should be getting picked up from the 2 text boxes on the userform.... the text boxes are called Date1 and Date2.
The user enters the vaules and then clicks a button that runs the sub containing the query.

Thanks for your persistance on this!

Woody
 
Upvote 0 Downvote
How is this code run ? is it from a button on the userform ?

I think you need to qualify your Date values properly - if the textbox is called Date1, try using Date1.Value instead

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Ok... What if the date values were entered into a range somewhere on the worksheet? Could I reference them that way?

Woody
 
Upvote 0 Downvote
yup:

Date1 = Sheets("SheetName").range("CellReference").value

etc

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Ok....

I've now tried to reference two fields for my range of dates with the following code:

Code: 
Date1 = Sheets("Date Entry").Range("B1").Value
Date2 = Sheets("Date Entry").Range("B2").Value

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
        "ODBC;DSN=SQLSvr;Description=Connection to SQL Server (SQLSVR1);APP=Microsoft Office 2003;WSID=SWOODWARD;DATABASE=FMDB;Network=DBMSSO" _
        ), Array("CN;Trusted_Connection=Yes")), Destination:=Range("A1"))
        .CommandText = Array( _
        "SELECT LABOR_TICKET.TRANSACTION_DATE, LABOR_TICKET.WORKORDER_BASE_ID, LABOR_TICKET.RESOURCE_ID, LABOR_TICKET.EMPLOYEE_ID, LABOR_TICKET.HOURS_WORKED, SHOP_RESOURCE.BUR_PER_HR_RUN, EMPLOYEE.FIRST_NAME, " _
        , _
        "EMPLOYEE.LAST_NAME" & Chr(13) & "" & Chr(10) & "FROM FMDB.dbo.EMPLOYEE EMPLOYEE, FMDB.dbo.LABOR_TICKET LABOR_TICKET, FMDB.dbo.SHOP_RESOURCE SHOP_RESOURCE" & Chr(13) & "" & Chr(10) & "WHERE LABOR_TICKET.RESOURCE_ID = SHOP_RESOURCE.ID AND LABOR_TICKET.EMPLOY" _
        , _
        "EE_ID = EMPLOYEE.ID AND ((LABOR_TICKET.WORKORDER_LOT_ID In ('FO1','SO1')) AND (LABOR_TICKET.CREATE_DATE Between {ts '" & Format(Date1, "dd/mm/yyyy hh:nn:ss") & "'} And {ts '" & Format(Date2, "dd/mm/yyyy hh:nn:ss") & "'}))" _
        )
         Debug.Print .CommandText
        .Name = "Query from SQLSvr"
        .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

This code still fails, even though the immediate window shows the following!

Code: 
SELECT LABOR_TICKET.TRANSACTION_DATE, LABOR_TICKET.WORKORDER_BASE_ID, LABOR_TICKET.RESOURCE_ID, LABOR_TICKET.EMPLOYEE_ID, LABOR_TICKET.HOURS_WORKED, SHOP_RESOURCE.BUR_PER_HR_RUN, EMPLOYEE.FIRST_NAME, EMPLOYEE.LAST_NAME
FROM FMDB.dbo.EMPLOYEE EMPLOYEE, FMDB.dbo.LABOR_TICKET LABOR_TICKET, FMDB.dbo.SHOP_RESOURCE SHOP_RESOURCE
WHERE LABOR_TICKET.RESOURCE_ID = SHOP_RESOURCE.ID AND LABOR_TICKET.EMPLOYEE_ID = EMPLOYEE.ID AND ((LABOR_TICKET.WORKORDER_LOT_ID In ('FO1','SO1')) AND (LABOR_TICKET.CREATE_DATE Between {ts '01/01/2005 00:00:00'} And {ts '01/02/2005 00:00:00'}))

Which suggests that the dates are being read!

Arrggghhh!!!
 
Upvote 0 Downvote
I have been an idiot!

the format that I was specifying was the wrong format for the database field I was referencing! Instead of dd-mm-yyyy, it should have been yyyy-mm-dd!

Stupid!

thanks for all of your help Geoff... It was all a massive help.

Cheers

woody
 
Upvote 0 Downvote
no probs - glad you got a resolution

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

remeng
  • Locked
  • Helpful tip
Microsoft Office 365 Word Macro Causing a Project is unviewable error
Replies
1
Views
1K
dhookom
dhookom
remeng
  • Locked
  • Solved
Run-time error '429': ActiveX component can't create object From Excel to Word
Replies
9
Views
2K
remeng
remeng
morechocolate
  • Locked
  • Question
run-tune error 3704 operation is not allowed when the object is codes: I don't think so.
Replies
7
Views
1K
morechocolate
morechocolate
huiettcm
  • Locked
  • Question
Catching the Mouse Click Error in Modal User Form
Replies
1
Views
582
strongm
strongm
sxschech
  • Locked
  • Question
For Each Loop works once then gives error 91 2
Replies
19
Views
1K
sxschech
sxschech

Part and Inventory Search

Sponsor

Back
Top