Parameter queries

[j_Southern]

I use MSQuery and an ODBC link to pick up data from Sage Line 100 (an accounting package). A straight forward query using a "Between ... And..." criteria on a date field works well.
My difficulties start when I try to convert this to a parameter query. Whichever format I use to I enter the dates I get a message "SQL Data Type out of range" This happens in whatever form I enter the date parameters. In my investigations I looked at what happens if I select on the basis of a string field. I get the same message. It is as if the whole parameter system is not working.

It was my intention to use two cells on a worksheet to enter the two dates, buy I can't get to the stage where I can specify the cells to use. i never had problems like this with Excel2000 ! Where am I going wrong?
Johns S

[dv8]

Are you building the SQL command yourself or letting MSQuery handle it through the interface? Could you post the SQL code / any relevant VBA code? To get the SQL go to immediate pane in the vb editor and enter ?activesheet.querytables(1).commandtext

I also use queries on Sage Line100 through ODBC so should be able to give pointers once I understand which way you're doing it.

[j_Southern]

I have used the Ms Query grid, though my intention is to use VBA to create a SQL string, when this problem is resolved. The SQL string shown in SQL view of the query is :-
SELECT WMORDER.WM_STATUS, WMORDER.WM_ACCOUNT, WMORDER.WM_INVOICE_DATE, WMORDER.WM_ORDER_NO, WMLABEL.WL_NOMCC, WMLABEL.WL_QUANTITY, WMLABEL.WL_MANUF_OR_MERCH, WMLABEL.WL_HEIGHT, WMLABEL.WL_MADE_QTY, WMLABEL.WL_WIDTH, WMLABEL.WL_LEADING, WM_LABEL_MATERIAL.MATERIAL_DESCRIPTION, WMLABEL.WL_MATERIAL, WMLABEL.WL_ADHESIVE, WM_LABEL_ADHESIVE.ADHESIVE_DESCRIPTION
FROM WINDMILL.WM_LABEL_ADHESIVE WM_LABEL_ADHESIVE, WINDMILL.WM_LABEL_MATERIAL WM_LABEL_MATERIAL, WINDMILL.WMLABEL WMLABEL, WINDMILL.WMORDER WMORDER
WHERE WMORDER.THIS_RECORD = WMLABEL.PARENT_RECORD AND WMLABEL.WL_MATERIAL = WM_LABEL_MATERIAL.MATERIAL_KEY AND WMLABEL.WL_ADHESIVE = WM_LABEL_ADHESIVE.ADHESIVE_KEY AND ((WMLABEL.WL_MATERIAL="N") AND (WMORDER.WM_STATUS=18) AND (WMORDER.WM_INVOICE_DATE Between {d '2010-02-01'} And {d '2010-02-28'}) AND (WMLABEL.WL_NOMCC="LAB") AND (WMLABEL.WL_MANUF_OR_MERCH=1) AND (WMLABEL.WL_ADHESIVE="P") OR (WMLABEL.WL_MATERIAL="P") AND (WMORDER.WM_STATUS=18) AND (WMORDER.WM_INVOICE_DATE Between {d '2010-02-01'} And {d '2010-02-28'}) AND (WMLABEL.WL_NOMCC="LAB") AND (WMLABEL.WL_MANUF_OR_MERCH=1) AND (WMLABEL.WL_ADHESIVE="F"))

The dates I changed to parameters are in the WHERE clause WM_ORDER.WM_INVOICE_DATE Between....And ....
The query created by this SQL works, the problem only comes when it is parametised.
THe simpler string parameter I created by changing WMLABEL.WLNOMCC = "LAB" to
a parameter. I got the same message "SQL Data Type Out of Range"
I have also run a simplified version of the query without so many OR and AND clauses, but still the error persists.
John

[dv8]

Hi John

try this, this is the way that I successfully query dates in Line 100

Please Login or Register  to view this content.

Incidentally I avoid mixing AND and OR where I possibly can.

Hope this does the trick
Dave

[j_Southern]

Dave,
I am most grateful for your time on this. Unfortunately I think we are slightly at cross purposes.
The SQL string I sent does work (in spite of mixed ORs and ANDs !) . It ceases to work when I convert to a parameter query. This is the problem I need to solve.

The change in your code was from a Between... And operator to >=....<= which also works in my code if substituted.
To simplify the picture I have created another query which also works when a value is used as a criterion but fails when a parameter is used, giving the same message as before.
The SQL statement is :-
SELECT WMORDER.PARENT_RECORD, WMORDER.THIS_RECORD, WMORDER.WM_ACCOUNT, WMORDER.WM_STATUS, WMTRANS.WT_NOMCC, WMTRANS.WT_PRODUCT, WMTRANS.WT_QUANTITY
FROM WINDMILL.WMORDER WMORDER, WINDMILL.WMTRANS WMTRANS
WHERE WMORDER.THIS_RECORD = WMTRANS.PARENT_RECORD AND ((WMTRANS.WT_NOMCC="MER") AND (WMORDER.WM_STATUS=18))

The criterion WMTRANS.WT_NOMCC="MER" works but if I use a parameter like eg [NOM Code?] in its place then I get the dreaded message.
John

[dv8]

I guess my method of attack is different to yours. If it were me I would build the SQL using whatever parameters are passed to the procedure, whether directly or through cell reference. Something like this

Please Login or Register  to view this content.

Any good to you?

[j_Southern]

I see what you are driving at now. I'll give it a go and let you know how I get on.
I will have to use something like the Access Currentdb.Querydef. SQL = Buildsql I guess
John

[dv8]

Hi John, tbh I'm not familiar with Access but you can use an Excel querytable and set commandtext to the SQL you build, or set a reference to a Microsoft ActiveX Data Objects library from the VBE and then use an ADO connection and recordset object. If you need help on either of these let me know.

[j_Southern]

Dave,
The only VBA I have used so far is with access. I have some excel books though that have references to QueryTable. I will try and sort it out myself first, though I suspect i could well be calling on you again. Many thanks foe your help so far.
John

[j_Southern]

Dave,
I have fallen at first hurdle. I am confused about the data type I am refering. My Code is :-
Sub Selectdata()
Dim FROM1 As Long
Dim TO1 As Long
FROM1 = Worksheets("Sheet1").Range("AQ4").Value
TO1 = Worksheets("Sheet1").Range("AQ6").Value
'FROM1 = Format(CDate(FROM1), "yyyy/mm/dd")
'TO1 = Format(CDate(TO1), "yyyy/mm/dd")
Call BuildSQL(FROM1, TO1)
With ActiveSheet.QueryTables("QrySelect")
.Sql = BuildSQL
.FillAdjacentFormulas = True
.BackgroundQuery = True
.FieldNames = True
.Destination = Worksheets("Sheet1").Range("C3")
End With

DoCmd.OpenQuery ("QrySelect")

End Sub

Where I have called your function exactly as written.
I get an error Compile Error Argument not optional
Can you help please
John

[dv8]

Hi John

try it like this:

Please Login or Register  to view this content.

[j_Southern]

Dave,
Your code is working but with some problems.
1. Whilst it returns the right column headings (showing that the ODBC link has been made) There is no data. (showing some error in the SQL string?)

2. When I repeat running the code the new column headings do not replace the first set but create a new set of columns "pushing aside" the old ones.This is a problem because I then loose all the calculated columns i am deriving from the data columns.

I feel that I am in danger of becoming a pain in the a***e with all these questions. The problem is that I have no other source of information. Is there a book you could recommend that would help with this kind of stuff ? I just know I am going to get lost when trying to work out the quote marks needed to create my own SQL strings. I have never seen anything which states the principles used.
John

[dv8]

Hi John

Before the qtthis.refresh line try inserting the following line, hopefully this will stop any old query data being shifted across

Please Login or Register  to view this content.

Regarding no data being returned, is your connection string correct? Can you test the SQL using a querying method that has previously worked for you, so that you can check that the SQL is valid? Maybe use a simple SQL string to test that the connection is sound... eg strSQL = "SELECT * FROM tablename" where tablename is the name of a small table, something like SYS_FOREIGN_CURRENCY

Documentation on querying Sage seems to be non-existant outside the accredited Sage developer community, I've learnt what I know the hard way.

regards
Dave

[j_Southern]

Dave,
Hold a while on last posting. I have found a way of checking the query generated by the code and my original query. I had forgotten that I simplified the query before posting the SQL string originally. It is quite likely that in this form it would not find data.
John

[j_Southern]

Dave,
All is sweetness and light now. The SQL string needed some tweaking and after that everything worked well. Thank you so much for your help. I have learned a lot
John