Query producing funny results

[CMPS]

Hello experts.

I am having a problem with my queries producing inconsistent results. They are part of a button on an Access form.

When I change the first select line to:

SELECT ORHDR.Ordno FROM ORHDR WHERE

It works fine but when I select wildcard or multiple fields it gives me incorrect values of 1.
SELECT ORHDR.* FROM ORHDR WHERE
SELECT ORHDR.Ordno, ORHDR.Orddate FROM ORHDR WHERE

The full query:

OrdersSQL = "SELECT ORHDR.Ordno FROM ORHDR WHERE" & _
"(ORHDR.Custkey='" & CompanyID & "') and " & _
"((ORHDR.Orddate >= '" & SDate & "' ) and (ORHDR.Orddate <= '" & EDate & "')) and " & _
"(ORHDR.Custpono LIKE 'CONS%') "

OrderSet.Open OrdersSQL, colmarSource2, adOpenStatic
MsgBox "OrderSet: " & OrderSet.RecordCount
OrderSet.Close


The same problem occurred when I selected the order lines as well but now works correctly with multiple values. Which seems to be the opposite behviour of the problem I described above.

LineSetSQL = "SELECT ORLIN.Origlinsq, ORLIN.ItemKey , ORLIN.Qtyship , ORLIN.Ordno , ORLIN.Qtyship " & _
" FROM ORLIN WHERE (ORLIN.Ordno = '" & OrderSet!Ordno & "')"
LineSet.Open LineSetSQL, colmarSource2, adOpenStatic

The inconsistency makes me go crazy! I thank anyone who looks or replies for your time and attention.

Regards,
Callum

 

[PHV]

Have you tried to OrderSet.MoveLast before playing with RecordCount ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[CMPS]

PH,

Thanks for the response. Does that mean that you first have to go to the end of the ADO record set to get the correct count? It seems to work fine when I select just one field rather than multiple fields in the first example. In the second ne it works fine with multiple fields but not the wildcard. I will try your suggestion to see if it stabilizes the proper count. I need the record count to loop through the ADO record set to write values to a table. So do most people who use acces go to the last record, get the count, then go back to the start and begin the loop?

Thanks again greatly for you helping me to understand this.

Regards,
Callum

 

[lespaul]

You shouldn't need to get the count for that, you should be able to use a Do...While loop (not sure of the exact syntax in Access):

while not recordset.eof do
  what to do
  recordset.next
end

Leslie

 

[CMPS]

Thanks for the response Leslie.

I can do a loop like that:
Do While Not OrderSet.EOF and it works.

I also tried using the recordcount after going to the last record.

The problem is that the actual data being returned is not correct. The system tells me there are ten records when I use the following query:

testSQL2 = "SELECT ORHDR.Custkey from ORHDR WHERE " & _
" (ORHDR.Custkey = '" & CompanyID & "') and " & _
" ((ORHDR.Orddate >= '" & SDate & "' ) and (ORHDR.Orddate <= '" & EDate & "')) and " & _
" (ORHDR.Custpono LIKE 'CONS%')"

But it tells me there is only one record when I use this query:

OrdersSQL = "SELECT ORHDR.Ordno, ORHDR.Returnflag, ORHDR.Custkey, ORHDR.Custpono, ORHDR.Orddate FROM ORHDR WHERE" & _
" (ORHDR.Custkey='" & CompanyID & "') and " & _
"((ORHDR.Orddate >= '" & SDate & "' ) and (ORHDR.Orddate <= '" & EDate & "')) and " & _
"(ORHDR.Custpono LIKE 'CONS%') "

The only difference is the fields I am pulling back into the record set. Could there be something I am doing wrong with the query? Do the fields I am asking for affect the data being returned?

Thnaks again for your help and interest.

Regards,
Callum

 

[CMPS]

Hello folks,

I have figured out that the problem is associated with the date field. Whenever I include it in the list of fields it messes up the query. I dont know why or how to fix it. I am connecting to a pervasive b-trieve database which stores its date format in a unique manner.

Regards,
Callum