Re: msde 2000 INNER JOIN problems

Daniel

Thanks worked a treat

Steve
"Daniel Crichton" <msnews@xxxxxxxxxxxxxxxxxx> wrote in message
news:%23Vgqw9SjFHA.576@xxxxxxxxxxxxxxxxxxxxxxx
> steve wrote on Wed, 20 Jul 2005 21:31:53 +1000:
>
>> Hi All
>>
>> I am using VB6 and MSDE 2000
>>
>> I previously used Access 2000 and used the following ado sql to create a
>> temp table in access
>> (Invoice table contains Invoice data such as Invoice number, date etc.
>> Idetail table contains details of the invoice individual items e.g
>> product
>> code, Invoice Number etc) The link is the Invoice Number field in each
>> table (Master/Detail)
>>
>> sql = "SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
>> idetail.price INTO idetailtemp IN '" & App.path &
>> "\data\tramcarstemp.mdb'
>> FROM idetail inner join [invoice] on idetail.inv_num = invoice.inv_num
>> where invoice.inv_date between #" & Format(DT1.Value, "m-d-yyyy") & "#
>> and
>> #" & Format(DT2.Value, "m-d-yyyy") & "#"
>> cn.execute sql
>>
>> This worked fine in Access 2000
>>
>> I am upgrading to MSDE2000 and using the following code in TSql
>>
>> Private Sub maketable()
>> Dim a, fso As New FileSystemObject
>> Dim x As Integer
>>
>> Set a = fso.CreateTextFile(App.path & "\sql\BackupTramcars.sql", True)
>>
>> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
>> 'true'") a.WriteLine ("GO")
>> a.WriteLine ("USE " & "Tramcars")
>> a.WriteLine ("SELECT idetail.qty, idetail.prod_code, invoice.inv_date,
>> idetail.price INTO idetailtemp")
>> a.WriteLine ("FROM idetail inner join [invoice] on idetail.inv_num =
>> invoice.inv_num")
>> a.WriteLine ("where invoice.inv_date between '" &
>> Format(frmorderhistory.DT1.Value, "m-d-yyyy") & "' and '" &
>> Format(frmorderhistory.DT2.Value, "m-d-yyyy") & "'")
>> a.WriteLine ("GO")
>> a.WriteLine ("EXEC sp_dboption 'Tramcars', 'select into/bulkcopy',
>> 'false'") a.WriteLine ("GO")
>> a.Close
>>
>> Set a = Nothing
>>
>> End Sub
>>
>> The table is created but the inserted invoice dates are only ever 1 of 2
>> dates
>
> Change you date format to a one that isn't region setting specific, eg.
>
> YYYYMMDD
>
> Depending on the region settings, something like 6-12-2005 could be
> interpreted as either 6th December 2005 or 12th June 2005.
>
>> Also when I read from the table to graph the data with
>> sql = "Select inv_date, sum(qty) as amount from [idetailtemp] where
>> [prod_code] = '" &
>> frmorderhistory.maingrid.TextMatrix(frmorderhistory.maingrid.Row, 0) & "'
>> group by [inv_date]"
>> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
>>
>> There are several records in the recordset but calling rs.recordcount =
>> -1, which didn't happen in Access 2K
>
> When a server side cursor is used you will get -1 for the recordcount (it
> indicates that there are records, but the count is unknown as not all of
> the records have been returned yet, records are returned as requested to
> save on immediate resource usage on the server). If you require the
> recordcount, use a client side cursor which will cause all records to be
> sent to the recordset when the Open method.
>
> rs.CursorLocation = adUseClient
> rs.Open sql, cn, adOpenDynamic, adLockReadOnly
>
>
> Dan
>


.

Relevant Pages

  • Re: msde 2000 INNER JOIN problems
    ... > (Invoice table contains Invoice data such as Invoice number, ... > cn.execute sql ... When a server side cursor is used you will get -1 for the recordcount (it ...
    (microsoft.public.data.ado)
  • Re: Insert, Delete, and Update - best practices advice please
    ... and handles only a small subset of the SQL keywords, functions, etc. ... The table GrowthInv is derrived from the above datasources by doing a SELECT ... those "fields" and represent the Invoice Number of an invoice and the ... as Customer Name and Salesperson but I can get those easily from Goldmine's ...
    (microsoft.public.sqlserver.programming)
  • Re: Insert, Delete, and Update - best practices advice please
    ... and handles only a small subset of the SQL keywords, functions, etc. ... The table GrowthInv is derrived from the above datasources by doing a SELECT ... those "fields" and represent the Invoice Number of an invoice and the ... as Customer Name and Salesperson but I can get those easily from Goldmine's ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with sql statment - ASAP please
    ... The form is not a sub form, ... the user enter the invoice number,InvoiceDate and company ... CTRL-G to Goto the debuG window -- look at the SQL statement ...
    (microsoft.public.access.formscoding)
  • Re: Please explain this update query?!?
    ... that update everytime a payment is made against an invoice. ... >> This update query works fine as it updates the values that I want it to, ... >> however, I seem to having a problem with multiple transactions, ie. ... > The way to get summations is thru SELECT queries that use the SQL ...
    (microsoft.public.access.queries)