Query Bug

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Bonjour,

Il m'arrive un truc très bizarre.
J'ai une requête que j'utilise depuis bien longtemps maintenant, mais pas
régulièrement.
J'ai Access 97 SR 2.
Ma requête « Sélection » ne fonctionne plus comme avant et quand je la
transforme en requête "Création de Table", elle fonctionne correctement.
J'ai réparé et compacté plusieurs fois la base mais sans succès.

Une idée pour la refaire fonctionner ?

Cordialement

Isa
_________________________________________________________________
Hello,
It arrives to me a very odd trick.
I have a request which I a long time now use since good, but not regularly.
I have Access 97 SR 2.
My request "Selection" does not function any more as before and when I
transform it into request "Creation of Table", it functions correctly. I
repaired and compacted several times the base but without success.

An idea to remake it to function?

Cordially

Isa
 
Please post the SQL of the Query you tried to use. A description of the
Tables that the Query uses would help, too, but we can probably infer
what the Tables look like from your SQL. Also, please describe what you
would like the output from the Query to look like.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
SQL :
SELECT ListemaxdocNR1.ref AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],[ListemaxdocNR1]![Indice])) AS Amdt,
Max(ListemaxdocNR1.indice) AS MaxDeindice, Last(ListemaxdocNR1.validation) AS
Validation, Last(SelaProduitsDocumentations.Valide) AS DernierDeValide
FROM (ListemaxdocNR1 LEFT JOIN ListeEvolutions ON ListemaxdocNR1.ref =
ListeEvolutions.RefEquipement) LEFT JOIN SelaProduitsDocumentations ON
(ListemaxdocNR1.ref = SelaProduitsDocumentations.CodeProduitComplet) AND
(ListemaxdocNR1.doc = SelaProduitsDocumentations.CodeDoc) AND
(ListemaxdocNR1.indice = SelaProduitsDocumentations.IndiceDoc)
GROUP BY ListemaxdocNR1.ref, ListemaxdocNR1.doc
HAVING (((ListemaxdocNR1.doc)='PL'));


Je m'explique.
Il n'y a aucun message d'erreur.
C'est une requête qui est ensuite utilisée dans une autre.
Lorsque je fais la visualisation de ma requête en mode Sélection, toutes les
données on l'air d'y être.
Une fois que je lie cette requête à mon autre requête, elle ne retrouve pas
les données similaires.
Les champs joints sont de type identique et contiennent bien des valeurs
identiques.
Si je change les propriété de la jointure (qui est au départ : inclure tous
les enregistrements de la Requete2 et seulement ceux de RequeteBug égaux) et
que j'inverse, il me donne quelque chose.
Si au lieu de faire une jointure avec ma RequeteBug mais avec la Table créée
en mode " Création de Table " depuis ma RequeteBug, cela fonctionne ...
Bizarre non ?!
____________________________________________________________________

I am explained.
There is no error message.
It is a request which is then used in another.
When I make the visualization of my request in Sélection mode, all data one
it air to be there.
Once that I bind this request to my other request, it does not find the
similar data. The joined fields are of identical type and contain many
identical values. If I change the property of the joint (which is at the
beginning: to include all the recordings of Requete2 and only those of
RequeteBug equal) and that I reverse, it gives me something.
If instead of making a joint with my RequeteBug but with the Table created
in mode "Creation of Table" since my RequeteBug, that functions...
Odd not?!
 
Most of your SQL makes sense to me, but I do not understand what
AmendLDD2(,) is. If it is a function for which you have the definition,
please either describe what it does, or post the VBA code that defines it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Public Function AmendLDD2(Reference As String, Indice As String) As String
'Recherche du dernier amendement appliqué
Module1.NameFonc = "AmendLDD2"
On Error GoTo un_err

Dim passage As Integer
passage = 0
Set sela = CurrentDb
Set req = sela.CreateQueryDef("", "SELECT ListeEvolutions.RefEquipement,
Max(ListeEvolutions.Amendement) AS MaxDeAmendement, ListeEvolutions.Indice
From ListeEvolutions GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice HAVING (((ListeEvolutions.RefEquipement) Like '" &
Reference & "'));")
Set table = req.OpenRecordset
table.FindFirst ("[refequipement] = '" & Reference & "'")
If table.RecordCount = 0 Then
AmendLDD2 = "-"
Else
Do While table.RecordCount > passage
If Indice = table![Indice] Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
If table.RecordCount - 1 = passage And Indice > table![Indice]
Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
table.FindNext ("[refequipement] = '" & Reference & "'")
passage = passage + 1
End If
End If
Loop
End If
Exit_un_err:
'table.Close

Exit Function

un_err:
Module1.Enr_Err
Resume Exit_un_err

End Function


Vincent Johns said:
Most of your SQL makes sense to me, but I do not understand what
AmendLDD2(,) is. If it is a function for which you have the definition,
please either describe what it does, or post the VBA code that defines it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
SQL :
SELECT ListemaxdocNR1.ref AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],[ListemaxdocNR1]![Indice])) AS Amdt,
Max(ListemaxdocNR1.indice) AS MaxDeindice, Last(ListemaxdocNR1.validation) AS
Validation, Last(SelaProduitsDocumentations.Valide) AS DernierDeValide
FROM (ListemaxdocNR1 LEFT JOIN ListeEvolutions ON ListemaxdocNR1.ref =
ListeEvolutions.RefEquipement) LEFT JOIN SelaProduitsDocumentations ON
(ListemaxdocNR1.ref = SelaProduitsDocumentations.CodeProduitComplet) AND
(ListemaxdocNR1.doc = SelaProduitsDocumentations.CodeDoc) AND
(ListemaxdocNR1.indice = SelaProduitsDocumentations.IndiceDoc)
GROUP BY ListemaxdocNR1.ref, ListemaxdocNR1.doc
HAVING (((ListemaxdocNR1.doc)='PL'));


Je m'explique.
Il n'y a aucun message d'erreur.
C'est une requête qui est ensuite utilisée dans une autre.
Lorsque je fais la visualisation de ma requête en mode Sélection, toutes les
données on l'air d'y être.
Une fois que je lie cette requête à mon autre requête, elle ne retrouve pas
les données similaires.
Les champs joints sont de type identique et contiennent bien des valeurs
identiques.
Si je change les propriété de la jointure (qui est au départ : inclure tous
les enregistrements de la Requete2 et seulement ceux de RequeteBug égaux) et
que j'inverse, il me donne quelque chose.
Si au lieu de faire une jointure avec ma RequeteBug mais avec la Table créée
en mode " Création de Table " depuis ma RequeteBug, cela fonctionne ...
Bizarre non ?!
____________________________________________________________________

I am explained.
There is no error message.
It is a request which is then used in another.
When I make the visualization of my request in Sélection mode, all data one
it air to be there.
Once that I bind this request to my other request, it does not find the
similar data. The joined fields are of identical type and contain many
identical values. If I change the property of the joint (which is at the
beginning: to include all the recordings of Requete2 and only those of
RequeteBug equal) and that I reverse, it gives me something.
If instead of making a joint with my RequeteBug but with the Table created
in mode "Creation of Table" since my RequeteBug, that functions...
Odd not?!
Click to expand...
Click to expand...
 
I re-wrote AmendLDD2(); please check to see that I did not make mistakes
in doing that.

Option Compare Database
Option Explicit

Public NameFonc As String

Public Function AmendLDD2( _
Reference As String, _
Indice As String) _
As String
'Recherche du dernier amendement appliqué

Dim sela As DAO.Database
Dim req As DAO.QueryDef
Dim table As DAO.Recordset
Dim passage As Integer

On Error GoTo un_err

Module1.NameFonc = "AmendLDD2"
passage = 0

Set sela = CurrentDb

Set req = sela.CreateQueryDef(Name:="", _
SQLText:="SELECT ListeEvolutions.RefEquipement, " & _
"Max(ListeEvolutions.Amendement) " & _
"AS MaxDeAmendement, ListeEvolutions.Indice " & _
"From ListeEvolutions " & _
"GROUP BY ListeEvolutions.RefEquipement, " & _
"ListeEvolutions.Indice " & _
"HAVING (((ListeEvolutions.RefEquipement) " & _
"Like '" & Reference & "'));")

Set table = req.OpenRecordset

table.FindFirst ("[refequipement] = '" _
& Reference & "'")

If table.RecordCount = 0 Then

AmendLDD2 = "-"

Else

Do While table.RecordCount > passage

If Indice = table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

If table.RecordCount - 1 = passage _
And Indice > table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

table.FindNext ("[refequipement] = '" _
& Reference & "'")

End If 'table.RecordCount - 1 = ...

End If

passage = passage + 1

Loop 'While table.RecordCount > ...

End If 'table.RecordCount = ...

Exit_un_err:
'table.Close

Exit Function

un_err:

MsgBox "Erreur: " & Err.Description, , "Erreur"
'Module1.Enr_Err

Resume Exit_un_err

End Function 'AmendLDD2

You did not define "Requete2" and "RequeteBug"; are these the Queries
represented by your SQL?

In your Query beginning "SELECT ListemaxdocNR1.ref ...", you used the
Last() function more than once, but your Query does not contain an
"ORDER BY" clause. Without that, Last() is likely to select a record at
random, if your Query returns more than one record.

Since I do not know what you stored in your Tables -- actually, I do not
know what data types some of the fields are -- I had to guess at the
contents. If you can post some sample data that demonstrate your
difficulty, that might help.

I set up Tables with the following sample data:

[ListeEvolutions] Table Datasheet View:

Indice RefEquipement Amendement
----------- ------------- ----------
-1647894833 DEF 10

[ListemaxdocNR1] Table Datasheet View:

Indice ref validation doc
------ --- ---------- ---
ABC DEF GHI PL

[SelaProduitsDocumentations] Table Datasheet View:

IndiceDoc Valide CodeProduitComplet CodeDoc
--------- ------ ------------------ -------
ABC PQR DEF PL

Then I put your SQL into the following Query:

[Query1] SQL:

SELECT [ListemaxdocNR1].[ref] AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],
[ListemaxdocNR1]![Indice])) AS Amdt,
Max([ListemaxdocNR1].[Indice]) AS MaxDeindice,
Last([ListemaxdocNR1].[validation]) AS Validation,
Last([SelaProduitsDocumentations].[Valide])
AS DernierDeValide
FROM (ListemaxdocNR1
LEFT JOIN ListeEvolutions
ON [ListemaxdocNR1].[ref]
=[ListeEvolutions].[RefEquipement])
LEFT JOIN SelaProduitsDocumentations
ON ([ListemaxdocNR1].[Indice]
=[SelaProduitsDocumentations].[IndiceDoc])
AND ([ListemaxdocNR1].[doc]
=[SelaProduitsDocumentations].[CodeDoc])
AND ([ListemaxdocNR1].[ref]
=[SelaProduitsDocumentations].[CodeProduitComplet])
GROUP BY [ListemaxdocNR1].[ref],
[ListemaxdocNR1].[doc]
HAVING (((ListemaxdocNR1.doc)='PL'));

.... and given the contents of the Tables, it produced this:

[Query1] Query Datasheet View:

RefLDD Amdt MaxDeindice Validation DernierDeValide
------ ---- ----------- ---------- ---------------
DEF 10 ABC GHI PQR

The Query that is created and used by the AmendLDD2() function is
similar to this one:

[Query2] SQL:

SELECT ListeEvolutions.RefEquipement,
Max (ListeEvolutions.Amendement)
AS MaxDeAmendement,
ListeEvolutions.Indice
FROM ListeEvolutions
GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice
HAVING (((ListeEvolutions.RefEquipement) Like '*'));

Its Query Datasheet View might look like this:


[Query2] Query Datasheet View:

RefEquipement MaxDeAmendement Indice
------------- --------------- -----------
DEF 10 -1647894833

If you post some example Tables with representative data, perhaps I (or
someone) can reproduce your error and show you how to correct it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Public Function AmendLDD2(Reference As String, Indice As String) As String
'Recherche du dernier amendement appliqué
Module1.NameFonc = "AmendLDD2"
On Error GoTo un_err

Dim passage As Integer
passage = 0
Set sela = CurrentDb
Set req = sela.CreateQueryDef("", "SELECT ListeEvolutions.RefEquipement,
Max(ListeEvolutions.Amendement) AS MaxDeAmendement, ListeEvolutions.Indice
From ListeEvolutions GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice HAVING (((ListeEvolutions.RefEquipement) Like '" &
Reference & "'));")
Set table = req.OpenRecordset
table.FindFirst ("[refequipement] = '" & Reference & "'")
If table.RecordCount = 0 Then
AmendLDD2 = "-"
Else
Do While table.RecordCount > passage
If Indice = table![Indice] Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
If table.RecordCount - 1 = passage And Indice > table![Indice]
Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
table.FindNext ("[refequipement] = '" & Reference & "'")
passage = passage + 1
End If
End If
Loop
End If
Exit_un_err:
'table.Close

Exit Function

un_err:
Module1.Enr_Err
Resume Exit_un_err

End Function


:

Most of your SQL makes sense to me, but I do not understand what
AmendLDD2(,) is. If it is a function for which you have the definition,
please either describe what it does, or post the VBA code that defines it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
SQL :
SELECT ListemaxdocNR1.ref AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],[ListemaxdocNR1]![Indice])) AS Amdt,
Max(ListemaxdocNR1.indice) AS MaxDeindice, Last(ListemaxdocNR1.validation) AS
Validation, Last(SelaProduitsDocumentations.Valide) AS DernierDeValide
FROM (ListemaxdocNR1 LEFT JOIN ListeEvolutions ON ListemaxdocNR1.ref =
ListeEvolutions.RefEquipement) LEFT JOIN SelaProduitsDocumentations ON
(ListemaxdocNR1.ref = SelaProduitsDocumentations.CodeProduitComplet) AND
(ListemaxdocNR1.doc = SelaProduitsDocumentations.CodeDoc) AND
(ListemaxdocNR1.indice = SelaProduitsDocumentations.IndiceDoc)
GROUP BY ListemaxdocNR1.ref, ListemaxdocNR1.doc
HAVING (((ListemaxdocNR1.doc)='PL'));


Je m'explique.
Il n'y a aucun message d'erreur.
C'est une requête qui est ensuite utilisée dans une autre.
Lorsque je fais la visualisation de ma requête en mode Sélection, toutes les
données on l'air d'y être.
Une fois que je lie cette requête à mon autre requête, elle ne retrouve pas
les données similaires.
Les champs joints sont de type identique et contiennent bien des valeurs
identiques.
Si je change les propriété de la jointure (qui est au départ : inclure tous
les enregistrements de la Requete2 et seulement ceux de RequeteBug égaux) et
que j'inverse, il me donne quelque chose.
Si au lieu de faire une jointure avec ma RequeteBug mais avec la Table créée
en mode " Création de Table " depuis ma RequeteBug, cela fonctionne ...
Bizarre non ?!
____________________________________________________________________

I am explained.
There is no error message.
It is a request which is then used in another.
When I make the visualization of my request in Sélection mode, all data one
it air to be there.
Once that I bind this request to my other request, it does not find the
similar data. The joined fields are of identical type and contain many
identical values. If I change the property of the joint (which is at the
beginning: to include all the recordings of Requete2 and only those of
RequeteBug equal) and that I reverse, it gives me something.
If instead of making a joint with my RequeteBug but with the Table created
in mode "Creation of Table" since my RequeteBug, that functions...
Odd not?!
Click to expand...
Click to expand...
Click to expand...
 
Requete2 et RequeteBug, sont en fait nommées : "ProduitsArticles" et
"ListeMaxLDDNR" dans ma base.
Le SQL de "ListeMaxLDDNR" est celui envoyé précédemment.
Pour mieux vous expliquer mon problème, je propose de vous envoyer ma base.
Est vous d'accord ?
______________________________________________________________
Requete2 and RequeteBug, in fact are named: "ProduitsArticles" and
"ListeMaxLDDNR" in my base.
The SQL of "ListeMaxLDDNR" is that sent previously.
For better explaining you my problem, I propose to send my base to you.
Agrees you?

Vincent Johns said:
I re-wrote AmendLDD2(); please check to see that I did not make mistakes
in doing that.

Option Compare Database
Option Explicit

Public NameFonc As String

Public Function AmendLDD2( _
Reference As String, _
Indice As String) _
As String
'Recherche du dernier amendement appliqué

Dim sela As DAO.Database
Dim req As DAO.QueryDef
Dim table As DAO.Recordset
Dim passage As Integer

On Error GoTo un_err

Module1.NameFonc = "AmendLDD2"
passage = 0

Set sela = CurrentDb

Set req = sela.CreateQueryDef(Name:="", _
SQLText:="SELECT ListeEvolutions.RefEquipement, " & _
"Max(ListeEvolutions.Amendement) " & _
"AS MaxDeAmendement, ListeEvolutions.Indice " & _
"From ListeEvolutions " & _
"GROUP BY ListeEvolutions.RefEquipement, " & _
"ListeEvolutions.Indice " & _
"HAVING (((ListeEvolutions.RefEquipement) " & _
"Like '" & Reference & "'));")

Set table = req.OpenRecordset

table.FindFirst ("[refequipement] = '" _
& Reference & "'")

If table.RecordCount = 0 Then

AmendLDD2 = "-"

Else

Do While table.RecordCount > passage

If Indice = table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

If table.RecordCount - 1 = passage _
And Indice > table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

table.FindNext ("[refequipement] = '" _
& Reference & "'")

End If 'table.RecordCount - 1 = ...

End If

passage = passage + 1

Loop 'While table.RecordCount > ...

End If 'table.RecordCount = ...

Exit_un_err:
'table.Close

Exit Function

un_err:

MsgBox "Erreur: " & Err.Description, , "Erreur"
'Module1.Enr_Err

Resume Exit_un_err

End Function 'AmendLDD2

You did not define "Requete2" and "RequeteBug"; are these the Queries
represented by your SQL?

In your Query beginning "SELECT ListemaxdocNR1.ref ...", you used the
Last() function more than once, but your Query does not contain an
"ORDER BY" clause. Without that, Last() is likely to select a record at
random, if your Query returns more than one record.

Since I do not know what you stored in your Tables -- actually, I do not
know what data types some of the fields are -- I had to guess at the
contents. If you can post some sample data that demonstrate your
difficulty, that might help.

I set up Tables with the following sample data:

[ListeEvolutions] Table Datasheet View:

Indice RefEquipement Amendement
----------- ------------- ----------
-1647894833 DEF 10

[ListemaxdocNR1] Table Datasheet View:

Indice ref validation doc
------ --- ---------- ---
ABC DEF GHI PL

[SelaProduitsDocumentations] Table Datasheet View:

IndiceDoc Valide CodeProduitComplet CodeDoc
--------- ------ ------------------ -------
ABC PQR DEF PL

Then I put your SQL into the following Query:

[Query1] SQL:

SELECT [ListemaxdocNR1].[ref] AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],
[ListemaxdocNR1]![Indice])) AS Amdt,
Max([ListemaxdocNR1].[Indice]) AS MaxDeindice,
Last([ListemaxdocNR1].[validation]) AS Validation,
Last([SelaProduitsDocumentations].[Valide])
AS DernierDeValide
FROM (ListemaxdocNR1
LEFT JOIN ListeEvolutions
ON [ListemaxdocNR1].[ref]
=[ListeEvolutions].[RefEquipement])
LEFT JOIN SelaProduitsDocumentations
ON ([ListemaxdocNR1].[Indice]
=[SelaProduitsDocumentations].[IndiceDoc])
AND ([ListemaxdocNR1].[doc]
=[SelaProduitsDocumentations].[CodeDoc])
AND ([ListemaxdocNR1].[ref]
=[SelaProduitsDocumentations].[CodeProduitComplet])
GROUP BY [ListemaxdocNR1].[ref],
[ListemaxdocNR1].[doc]
HAVING (((ListemaxdocNR1.doc)='PL'));

.... and given the contents of the Tables, it produced this:

[Query1] Query Datasheet View:

RefLDD Amdt MaxDeindice Validation DernierDeValide
------ ---- ----------- ---------- ---------------
DEF 10 ABC GHI PQR

The Query that is created and used by the AmendLDD2() function is
similar to this one:

[Query2] SQL:

SELECT ListeEvolutions.RefEquipement,
Max (ListeEvolutions.Amendement)
AS MaxDeAmendement,
ListeEvolutions.Indice
FROM ListeEvolutions
GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice
HAVING (((ListeEvolutions.RefEquipement) Like '*'));

Its Query Datasheet View might look like this:


[Query2] Query Datasheet View:

RefEquipement MaxDeAmendement Indice
------------- --------------- -----------
DEF 10 -1647894833

If you post some example Tables with representative data, perhaps I (or
someone) can reproduce your error and show you how to correct it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

Public Function AmendLDD2(Reference As String, Indice As String) As String
'Recherche du dernier amendement appliqué
Module1.NameFonc = "AmendLDD2"
On Error GoTo un_err

Dim passage As Integer
passage = 0
Set sela = CurrentDb
Set req = sela.CreateQueryDef("", "SELECT ListeEvolutions.RefEquipement,
Max(ListeEvolutions.Amendement) AS MaxDeAmendement, ListeEvolutions.Indice
From ListeEvolutions GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice HAVING (((ListeEvolutions.RefEquipement) Like '" &
Reference & "'));")
Set table = req.OpenRecordset
table.FindFirst ("[refequipement] = '" & Reference & "'")
If table.RecordCount = 0 Then
AmendLDD2 = "-"
Else
Do While table.RecordCount > passage
If Indice = table![Indice] Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
If table.RecordCount - 1 = passage And Indice > table![Indice]
Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
table.FindNext ("[refequipement] = '" & Reference & "'")
passage = passage + 1
End If
End If
Loop
End If
Exit_un_err:
'table.Close

Exit Function

un_err:
Module1.Enr_Err
Resume Exit_un_err

End Function


:

Most of your SQL makes sense to me, but I do not understand what
AmendLDD2(,) is. If it is a function for which you have the definition,
please either describe what it does, or post the VBA code that defines it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

isa911 wrote:

SQL :
SELECT ListemaxdocNR1.ref AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],[ListemaxdocNR1]![Indice])) AS Amdt,
Max(ListemaxdocNR1.indice) AS MaxDeindice, Last(ListemaxdocNR1.validation) AS
Validation, Last(SelaProduitsDocumentations.Valide) AS DernierDeValide
FROM (ListemaxdocNR1 LEFT JOIN ListeEvolutions ON ListemaxdocNR1.ref =
ListeEvolutions.RefEquipement) LEFT JOIN SelaProduitsDocumentations ON
(ListemaxdocNR1.ref = SelaProduitsDocumentations.CodeProduitComplet) AND
(ListemaxdocNR1.doc = SelaProduitsDocumentations.CodeDoc) AND
(ListemaxdocNR1.indice = SelaProduitsDocumentations.IndiceDoc)
GROUP BY ListemaxdocNR1.ref, ListemaxdocNR1.doc
HAVING (((ListemaxdocNR1.doc)='PL'));


Je m'explique.
Il n'y a aucun message d'erreur.
C'est une requête qui est ensuite utilisée dans une autre.
Lorsque je fais la visualisation de ma requête en mode Sélection, toutes les
données on l'air d'y être.
Une fois que je lie cette requête à mon autre requête, elle ne retrouve pas
les données similaires.
Les champs joints sont de type identique et contiennent bien des valeurs
identiques.
Si je change les propriété de la jointure (qui est au départ : inclure tous
les enregistrements de la Requete2 et seulement ceux de RequeteBug égaux) et
que j'inverse, il me donne quelque chose.
Si au lieu de faire une jointure avec ma RequeteBug mais avec la Table créée
en mode " Création de Table " depuis ma RequeteBug, cela fonctionne ...
Bizarre non ?!
____________________________________________________________________

I am explained.
There is no error message.
It is a request which is then used in another.
When I make the visualization of my request in Sélection mode, all data one
it air to be there.
Once that I bind this request to my other request, it does not find the
similar data. The joined fields are of identical type and contain many
identical values. If I change the property of the joint (which is at the
beginning: to include all the recordings of Requete2 and only those of
RequeteBug equal) and that I reverse, it gives me something.
If instead of making a joint with my RequeteBug but with the Table created
in mode "Creation of Table" since my RequeteBug, that functions...
Odd not?!
Click to expand...
Click to expand...
Click to expand...
 
isa911 said:
Requete2 et RequeteBug, sont en fait nommées : "ProduitsArticles" et
"ListeMaxLDDNR" dans ma base.
Le SQL de "ListeMaxLDDNR" est celui envoyé précédemment.
Pour mieux vous expliquer mon problème, je propose de vous envoyer ma base.
Est vous d'accord ?
Click to expand...

Oui.

But I may need to ask you further questions about it... :-)

______________________________________________________________
Requete2 and RequeteBug, in fact are named: "ProduitsArticles" and
"ListeMaxLDDNR" in my base.
The SQL of "ListeMaxLDDNR" is that sent previously.
For better explaining you my problem, I propose to send my base to you.
Agrees you?

:

I re-wrote AmendLDD2(); please check to see that I did not make mistakes
in doing that.

Option Compare Database
Option Explicit

Public NameFonc As String

Public Function AmendLDD2( _
Reference As String, _
Indice As String) _
As String
'Recherche du dernier amendement appliqué

Dim sela As DAO.Database
Dim req As DAO.QueryDef
Dim table As DAO.Recordset
Dim passage As Integer

On Error GoTo un_err

Module1.NameFonc = "AmendLDD2"
passage = 0

Set sela = CurrentDb

Set req = sela.CreateQueryDef(Name:="", _
SQLText:="SELECT ListeEvolutions.RefEquipement, " & _
"Max(ListeEvolutions.Amendement) " & _
"AS MaxDeAmendement, ListeEvolutions.Indice " & _
"From ListeEvolutions " & _
"GROUP BY ListeEvolutions.RefEquipement, " & _
"ListeEvolutions.Indice " & _
"HAVING (((ListeEvolutions.RefEquipement) " & _
"Like '" & Reference & "'));")

Set table = req.OpenRecordset

table.FindFirst ("[refequipement] = '" _
& Reference & "'")

If table.RecordCount = 0 Then

AmendLDD2 = "-"

Else

Do While table.RecordCount > passage

If Indice = table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

If table.RecordCount - 1 = passage _
And Indice > table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

table.FindNext ("[refequipement] = '" _
& Reference & "'")

End If 'table.RecordCount - 1 = ...

End If

passage = passage + 1

Loop 'While table.RecordCount > ...

End If 'table.RecordCount = ...

Exit_un_err:
'table.Close

Exit Function

un_err:

MsgBox "Erreur: " & Err.Description, , "Erreur"
'Module1.Enr_Err

Resume Exit_un_err

End Function 'AmendLDD2

You did not define "Requete2" and "RequeteBug"; are these the Queries
represented by your SQL?

In your Query beginning "SELECT ListemaxdocNR1.ref ...", you used the
Last() function more than once, but your Query does not contain an
"ORDER BY" clause. Without that, Last() is likely to select a record at
random, if your Query returns more than one record.

Since I do not know what you stored in your Tables -- actually, I do not
know what data types some of the fields are -- I had to guess at the
contents. If you can post some sample data that demonstrate your
difficulty, that might help.

I set up Tables with the following sample data:

[ListeEvolutions] Table Datasheet View:

Indice RefEquipement Amendement
----------- ------------- ----------
-1647894833 DEF 10

[ListemaxdocNR1] Table Datasheet View:

Indice ref validation doc
------ --- ---------- ---
ABC DEF GHI PL

[SelaProduitsDocumentations] Table Datasheet View:

IndiceDoc Valide CodeProduitComplet CodeDoc
--------- ------ ------------------ -------
ABC PQR DEF PL

Then I put your SQL into the following Query:

[Query1] SQL:

SELECT [ListemaxdocNR1].[ref] AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],
[ListemaxdocNR1]![Indice])) AS Amdt,
Max([ListemaxdocNR1].[Indice]) AS MaxDeindice,
Last([ListemaxdocNR1].[validation]) AS Validation,
Last([SelaProduitsDocumentations].[Valide])
AS DernierDeValide
FROM (ListemaxdocNR1
LEFT JOIN ListeEvolutions
ON [ListemaxdocNR1].[ref]
=[ListeEvolutions].[RefEquipement])
LEFT JOIN SelaProduitsDocumentations
ON ([ListemaxdocNR1].[Indice]
=[SelaProduitsDocumentations].[IndiceDoc])
AND ([ListemaxdocNR1].[doc]
=[SelaProduitsDocumentations].[CodeDoc])
AND ([ListemaxdocNR1].[ref]
=[SelaProduitsDocumentations].[CodeProduitComplet])
GROUP BY [ListemaxdocNR1].[ref],
[ListemaxdocNR1].[doc]
HAVING (((ListemaxdocNR1.doc)='PL'));

.... and given the contents of the Tables, it produced this:

[Query1] Query Datasheet View:

RefLDD Amdt MaxDeindice Validation DernierDeValide
------ ---- ----------- ---------- ---------------
DEF 10 ABC GHI PQR

The Query that is created and used by the AmendLDD2() function is
similar to this one:

[Query2] SQL:

SELECT ListeEvolutions.RefEquipement,
Max (ListeEvolutions.Amendement)
AS MaxDeAmendement,
ListeEvolutions.Indice
FROM ListeEvolutions
GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice
HAVING (((ListeEvolutions.RefEquipement) Like '*'));

Its Query Datasheet View might look like this:


[Query2] Query Datasheet View:

RefEquipement MaxDeAmendement Indice
------------- --------------- -----------
DEF 10 -1647894833

If you post some example Tables with representative data, perhaps I (or
someone) can reproduce your error and show you how to correct it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


isa911 wrote:

Public Function AmendLDD2(Reference As String, Indice As String) As String
'Recherche du dernier amendement appliqué
Module1.NameFonc = "AmendLDD2"
On Error GoTo un_err

Dim passage As Integer
passage = 0
Set sela = CurrentDb
Set req = sela.CreateQueryDef("", "SELECT ListeEvolutions.RefEquipement,
Max(ListeEvolutions.Amendement) AS MaxDeAmendement, ListeEvolutions.Indice
From ListeEvolutions GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice HAVING (((ListeEvolutions.RefEquipement) Like '" &
Reference & "'));")
Set table = req.OpenRecordset
table.FindFirst ("[refequipement] = '" & Reference & "'")
If table.RecordCount = 0 Then
AmendLDD2 = "-"
Else
Do While table.RecordCount > passage
If Indice = table![Indice] Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
If table.RecordCount - 1 = passage And Indice > table![Indice]
Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
table.FindNext ("[refequipement] = '" & Reference & "'")
passage = passage + 1
End If
End If
Loop
End If
Exit_un_err:
'table.Close

Exit Function

un_err:
Module1.Enr_Err
Resume Exit_un_err

End Function


:



Most of your SQL makes sense to me, but I do not understand what
AmendLDD2(,) is. If it is a function for which you have the definition,
please either describe what it does, or post the VBA code that defines it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

isa911 wrote:


SQL :
SELECT ListemaxdocNR1.ref AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],[ListemaxdocNR1]![Indice])) AS Amdt,
Max(ListemaxdocNR1.indice) AS MaxDeindice, Last(ListemaxdocNR1.validation) AS
Validation, Last(SelaProduitsDocumentations.Valide) AS DernierDeValide

FROM (ListemaxdocNR1 LEFT JOIN ListeEvolutions ON ListemaxdocNR1.ref =

ListeEvolutions.RefEquipement) LEFT JOIN SelaProduitsDocumentations ON
(ListemaxdocNR1.ref = SelaProduitsDocumentations.CodeProduitComplet) AND
(ListemaxdocNR1.doc = SelaProduitsDocumentations.CodeDoc) AND
(ListemaxdocNR1.indice = SelaProduitsDocumentations.IndiceDoc)
GROUP BY ListemaxdocNR1.ref, ListemaxdocNR1.doc
HAVING (((ListemaxdocNR1.doc)='PL'));


Je m'explique.
Il n'y a aucun message d'erreur.
C'est une requête qui est ensuite utilisée dans une autre.
Lorsque je fais la visualisation de ma requête en mode Sélection, toutes les
données on l'air d'y être.
Une fois que je lie cette requête à mon autre requête, elle ne retrouve pas
les données similaires.
Les champs joints sont de type identique et contiennent bien des valeurs
identiques.
Si je change les propriété de la jointure (qui est au départ : inclure tous
les enregistrements de la Requete2 et seulement ceux de RequeteBug égaux) et
que j'inverse, il me donne quelque chose.
Si au lieu de faire une jointure avec ma RequeteBug mais avec la Table créée
en mode " Création de Table " depuis ma RequeteBug, cela fonctionne ...
Bizarre non ?!
____________________________________________________________________

I am explained.
There is no error message.
It is a request which is then used in another.
When I make the visualization of my request in Sélection mode, all data one
it air to be there.
Once that I bind this request to my other request, it does not find the
similar data. The joined fields are of identical type and contain many
identical values. If I change the property of the joint (which is at the
beginning: to include all the recordings of Requete2 and only those of
RequeteBug equal) and that I reverse, it gives me something.
If instead of making a joint with my RequeteBug but with the Table created
in mode "Creation of Table" since my RequeteBug, that functions...
Odd not?!
Click to expand...
Click to expand...
Click to expand...
 
oui, bien sur, mais j'espere que ce sera plus facile pour avancer...
Surtout que mon anglais est tres moyen...
_________________________________________________
yes, well on, but I espere that it will be easier to advance...
Especially that my English is very average...


Vincent Johns said:
isa911 said:
Requete2 et RequeteBug, sont en fait nommées : "ProduitsArticles" et
"ListeMaxLDDNR" dans ma base.
Le SQL de "ListeMaxLDDNR" est celui envoyé précédemment.
Pour mieux vous expliquer mon problème, je propose de vous envoyer ma base.
Est vous d'accord ?
Click to expand...

Oui.

But I may need to ask you further questions about it... :-)

______________________________________________________________
Requete2 and RequeteBug, in fact are named: "ProduitsArticles" and
"ListeMaxLDDNR" in my base.
The SQL of "ListeMaxLDDNR" is that sent previously.
For better explaining you my problem, I propose to send my base to you.
Agrees you?

:

I re-wrote AmendLDD2(); please check to see that I did not make mistakes
in doing that.

Option Compare Database
Option Explicit

Public NameFonc As String

Public Function AmendLDD2( _
Reference As String, _
Indice As String) _
As String
'Recherche du dernier amendement appliqué

Dim sela As DAO.Database
Dim req As DAO.QueryDef
Dim table As DAO.Recordset
Dim passage As Integer

On Error GoTo un_err

Module1.NameFonc = "AmendLDD2"
passage = 0

Set sela = CurrentDb

Set req = sela.CreateQueryDef(Name:="", _
SQLText:="SELECT ListeEvolutions.RefEquipement, " & _
"Max(ListeEvolutions.Amendement) " & _
"AS MaxDeAmendement, ListeEvolutions.Indice " & _
"From ListeEvolutions " & _
"GROUP BY ListeEvolutions.RefEquipement, " & _
"ListeEvolutions.Indice " & _
"HAVING (((ListeEvolutions.RefEquipement) " & _
"Like '" & Reference & "'));")

Set table = req.OpenRecordset

table.FindFirst ("[refequipement] = '" _
& Reference & "'")

If table.RecordCount = 0 Then

AmendLDD2 = "-"

Else

Do While table.RecordCount > passage

If Indice = table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

If table.RecordCount - 1 = passage _
And Indice > table![Indice] Then

AmendLDD2 = table![MaxDeAmendement]

Else

table.FindNext ("[refequipement] = '" _
& Reference & "'")

End If 'table.RecordCount - 1 = ...

End If

passage = passage + 1

Loop 'While table.RecordCount > ...

End If 'table.RecordCount = ...

Exit_un_err:
'table.Close

Exit Function

un_err:

MsgBox "Erreur: " & Err.Description, , "Erreur"
'Module1.Enr_Err

Resume Exit_un_err

End Function 'AmendLDD2

You did not define "Requete2" and "RequeteBug"; are these the Queries
represented by your SQL?

In your Query beginning "SELECT ListemaxdocNR1.ref ...", you used the
Last() function more than once, but your Query does not contain an
"ORDER BY" clause. Without that, Last() is likely to select a record at
random, if your Query returns more than one record.

Since I do not know what you stored in your Tables -- actually, I do not
know what data types some of the fields are -- I had to guess at the
contents. If you can post some sample data that demonstrate your
difficulty, that might help.

I set up Tables with the following sample data:

[ListeEvolutions] Table Datasheet View:

Indice RefEquipement Amendement
----------- ------------- ----------
-1647894833 DEF 10

[ListemaxdocNR1] Table Datasheet View:

Indice ref validation doc
------ --- ---------- ---
ABC DEF GHI PL

[SelaProduitsDocumentations] Table Datasheet View:

IndiceDoc Valide CodeProduitComplet CodeDoc
--------- ------ ------------------ -------
ABC PQR DEF PL

Then I put your SQL into the following Query:

[Query1] SQL:

SELECT [ListemaxdocNR1].[ref] AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],
[ListemaxdocNR1]![Indice])) AS Amdt,
Max([ListemaxdocNR1].[Indice]) AS MaxDeindice,
Last([ListemaxdocNR1].[validation]) AS Validation,
Last([SelaProduitsDocumentations].[Valide])
AS DernierDeValide
FROM (ListemaxdocNR1
LEFT JOIN ListeEvolutions
ON [ListemaxdocNR1].[ref]
=[ListeEvolutions].[RefEquipement])
LEFT JOIN SelaProduitsDocumentations
ON ([ListemaxdocNR1].[Indice]
=[SelaProduitsDocumentations].[IndiceDoc])
AND ([ListemaxdocNR1].[doc]
=[SelaProduitsDocumentations].[CodeDoc])
AND ([ListemaxdocNR1].[ref]
=[SelaProduitsDocumentations].[CodeProduitComplet])
GROUP BY [ListemaxdocNR1].[ref],
[ListemaxdocNR1].[doc]
HAVING (((ListemaxdocNR1.doc)='PL'));

.... and given the contents of the Tables, it produced this:

[Query1] Query Datasheet View:

RefLDD Amdt MaxDeindice Validation DernierDeValide
------ ---- ----------- ---------- ---------------
DEF 10 ABC GHI PQR

The Query that is created and used by the AmendLDD2() function is
similar to this one:

[Query2] SQL:

SELECT ListeEvolutions.RefEquipement,
Max (ListeEvolutions.Amendement)
AS MaxDeAmendement,
ListeEvolutions.Indice
FROM ListeEvolutions
GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice
HAVING (((ListeEvolutions.RefEquipement) Like '*'));

Its Query Datasheet View might look like this:


[Query2] Query Datasheet View:

RefEquipement MaxDeAmendement Indice
------------- --------------- -----------
DEF 10 -1647894833

If you post some example Tables with representative data, perhaps I (or
someone) can reproduce your error and show you how to correct it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.


isa911 wrote:


Public Function AmendLDD2(Reference As String, Indice As String) As String
'Recherche du dernier amendement appliqué
Module1.NameFonc = "AmendLDD2"
On Error GoTo un_err

Dim passage As Integer
passage = 0
Set sela = CurrentDb
Set req = sela.CreateQueryDef("", "SELECT ListeEvolutions.RefEquipement,
Max(ListeEvolutions.Amendement) AS MaxDeAmendement, ListeEvolutions.Indice
From ListeEvolutions GROUP BY ListeEvolutions.RefEquipement,
ListeEvolutions.Indice HAVING (((ListeEvolutions.RefEquipement) Like '" &
Reference & "'));")
Set table = req.OpenRecordset
table.FindFirst ("[refequipement] = '" & Reference & "'")
If table.RecordCount = 0 Then
AmendLDD2 = "-"
Else
Do While table.RecordCount > passage
If Indice = table![Indice] Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
If table.RecordCount - 1 = passage And Indice > table![Indice]
Then
AmendLDD2 = table![MaxDeAmendement]
passage = passage + 1
Else
table.FindNext ("[refequipement] = '" & Reference & "'")
passage = passage + 1
End If
End If
Loop
End If
Exit_un_err:
'table.Close

Exit Function

un_err:
Module1.Enr_Err
Resume Exit_un_err

End Function


:



Most of your SQL makes sense to me, but I do not understand what
AmendLDD2(,) is. If it is a function for which you have the definition,
please either describe what it does, or post the VBA code that defines it.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

isa911 wrote:


SQL :
SELECT ListemaxdocNR1.ref AS RefLDD,
Last(AmendLDD2([ListemaxdocNR1]![Ref],[ListemaxdocNR1]![Indice])) AS Amdt,
Max(ListemaxdocNR1.indice) AS MaxDeindice, Last(ListemaxdocNR1.validation) AS
Validation, Last(SelaProduitsDocumentations.Valide) AS DernierDeValide

FROM (ListemaxdocNR1 LEFT JOIN ListeEvolutions ON ListemaxdocNR1.ref =

ListeEvolutions.RefEquipement) LEFT JOIN SelaProduitsDocumentations ON
(ListemaxdocNR1.ref = SelaProduitsDocumentations.CodeProduitComplet) AND
(ListemaxdocNR1.doc = SelaProduitsDocumentations.CodeDoc) AND
(ListemaxdocNR1.indice = SelaProduitsDocumentations.IndiceDoc)
GROUP BY ListemaxdocNR1.ref, ListemaxdocNR1.doc
HAVING (((ListemaxdocNR1.doc)='PL'));


Je m'explique.
Il n'y a aucun message d'erreur.
C'est une requête qui est ensuite utilisée dans une autre.
Lorsque je fais la visualisation de ma requête en mode Sélection, toutes les
données on l'air d'y être.
Une fois que je lie cette requête à mon autre requête, elle ne retrouve pas
les données similaires.
Les champs joints sont de type identique et contiennent bien des valeurs
identiques.
Si je change les propriété de la jointure (qui est au départ : inclure tous
Click to expand...
Click to expand...
Click to expand...
 
Back
Top