Data mismatch in this criteria (1 Viewer)

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
Hi there,

I have a problem with my program. Indeed, when I launch it, the error is "Data mismatch is this criteria". You can see the following code:
Code:
Sub MotherPacketOP(MNbr As String, LATDT As String, CLDT As String, PRVAL As String, ASTDT As String)

    Dim strSQL1 As String
    Dim rst As DAO.Recordset
    Dim rst1 As DAO.Recordset
    
    Set rst = CurrentDb.OpenRecordset("Operations")
    
    strSQL1 = "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "
    strSQL1 = strSQL1 & "UNION ALL SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "
    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"
    
    Debug.Print strSQL1
    
    Set rst1 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset) 'just collect the information of the Operations tables and enter same name in the normal and extra field

Apparently the problem is about "UNION ALL"...
But the code isn't false!
Someone advices me to create a Temp table instead of " UNION ALL".
Does it the right thing to do?
How can I do that?

Thanks a lot!
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
What does Debug.Print actually print?

It prints:

SELECT AMFLIBP_MOHRTG.* FROM AMFLIBP_MOHRTG WHERE (((AMFLIBP_MOHRTG.ORDNO)='M818120') AND ((AMFLIBP_MOHRTG.CLDT)=0)) UNION ALL SELECT AMFLIBP_MOROUT.* FROM AMFLIBP_MOROUT WHERE AMFLIBP_MOROUT.ORDNO='M818120' AND AMFLIBP_MOROUT.PRVAL='' ORDER BY OPSEQ;
 

nanscombe

Registered User.
Local time
Today, 07:22
Joined
Nov 12, 2011
Messages
1,082
Before you do anything, make a copy of this section of the code and comment it out so you have it safe then try breaking it into two parts.

Code:
strSQL1 =""
    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

    strSQL1 = strSQL1 & "UNION ALL "

    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

Then comment out the bit that does the work so you can play with it.

Code:
' rst1.MoveFirst
'    Do Until rst1.EOF
'        rst.AddNew
'        rst!ORDNO = MNbr
'        rst!CLDT = CLDT
'        rst!LATDT = LATDT
'        rst!CORD = MNbr
'        rst!CCLDT = CLDT
'        rst!CLATD = LATDT
'        rst!CASTDT = ASTDT
'        rst!OP = rst1!OPSEQ
'        rst!CASTDT = rst1!ASTDT
'        rst.Update
'        rst1.MoveNext
'    Loop

Then try commenting out the second part and see if it runs ok.



Code:
strSQL1 =""
    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

'    strSQL1 = strSQL1 & "UNION ALL "

'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
'    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

If the first part seems to work then swap it over.


Code:
strSQL1 =""
'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
'    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

'    strSQL1 = strSQL1 & "UNION ALL "

    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

That could narrow the problem down.

make a copy of the WHERE line of the second part, and comment it out then,
to narrow it down further, play with last part of the second half of the query.

Try ..

Code:
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'"

Then if that works try ...

Code:
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

It's a matter of playing with the code to see where it breaks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 02:22
Joined
Feb 19, 2002
Messages
43,346
AND AMFLIBP_MOROUT.PRVAL='' ORDER BY OPSEQ;
The final double quote is missing and you have a leading space in front of ORDER.
 

nanscombe

Registered User.
Local time
Today, 07:22
Joined
Nov 12, 2011
Messages
1,082
I think you'll find that is a pair of single quotes followed by a space.
 

nanscombe

Registered User.
Local time
Today, 07:22
Joined
Nov 12, 2011
Messages
1,082
PRVAL, that wouldn't happen to a previous value would it?

What happens if there isn't one?

If are no records where AMFLIBP_MOROUT.PRVAL has no value then you may be able to ignore it, by not doing a UNION ALL, for those cases.

Code:
strSQL1 = "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

  If Nz(PRVAL, "") <> "" Then
    strSQL1 = strSQL1 & "UNION ALL SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & PRVAL & "' "
  Endif

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
Before you do anything, make a copy of this section of the code and comment it out so you have it safe then try breaking it into two parts.

Code:
strSQL1 =""
    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

    strSQL1 = strSQL1 & "UNION ALL "

    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

Then comment out the bit that does the work so you can play with it.

Code:
' rst1.MoveFirst
'    Do Until rst1.EOF
'        rst.AddNew
'        rst!ORDNO = MNbr
'        rst!CLDT = CLDT
'        rst!LATDT = LATDT
'        rst!CORD = MNbr
'        rst!CCLDT = CLDT
'        rst!CLATD = LATDT
'        rst!CASTDT = ASTDT
'        rst!OP = rst1!OPSEQ
'        rst!CASTDT = rst1!ASTDT
'        rst.Update
'        rst1.MoveNext
'    Loop

Then try commenting out the second part and see if it runs ok.



Code:
strSQL1 =""
    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

'    strSQL1 = strSQL1 & "UNION ALL "

'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
'    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

If the first part seems to work then swap it over.


Code:
strSQL1 =""
'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
'    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

'    strSQL1 = strSQL1 & "UNION ALL "

    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

That could narrow the problem down.

make a copy of the WHERE line of the second part, and comment it out then,
to narrow it down further, play with last part of the second half of the query.

Try ..

Code:
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'"

Then if that works try ...

Code:
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

It's a matter of playing with the code to see where it breaks.


It's break on this row:

Set rst1 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset) 'just collect the information of the Operations tables and enter same name in the normal and extra field

So now what could I do??

I don't really understand what the problem is here...
 

nanscombe

Registered User.
Local time
Today, 07:22
Joined
Nov 12, 2011
Messages
1,082
Yes. We know that is where the program breaks.

As I said. First comment out the bit that does the update, so it won't make any changes.

Code:
' rst1.MoveFirst
'    Do Until rst1.EOF
'        rst.AddNew
'        rst!ORDNO = MNbr
'        rst!CLDT = CLDT
'        rst!LATDT = LATDT
'        rst!CORD = MNbr
'        rst!CCLDT = CLDT
'        rst!CLATD = LATDT
'        rst!CASTDT = ASTDT
'        rst!OP = rst1!OPSEQ
'        rst!CASTDT = rst1!ASTDT
'        rst.Update
'        rst1.MoveNext
'    Loop


Then comment out the second section to see if it gets past that line.

Code:
strSQL1 =""
    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

[COLOR="red"]'    strSQL1 = strSQL1 & "UNION ALL "

'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
'    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "[/COLOR]

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

Then if it does, try commenting out the first section and see if that fails.

Code:
strSQL1 =""
[COLOR="RoyalBlue"]'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
'    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

'    strSQL1 = strSQL1 & "UNION ALL "[/COLOR]

    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

If the first part doesn't fail but the second part does then make a copy of the line below and comment it out so you can play with the Where part.

Code:
strSQL1 =""
'    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOHRTG.* "
'    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
'    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

'    strSQL1 = strSQL1 & "UNION ALL "

    strSQL1 = strSQL1 & "SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
'    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

First try and see if the line below fails.

Code:
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'"

If it doesn't fail then try
Code:
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.PRVAL='" & Nz(PRVAL, "") & "' "

You're just trying to find out exactly what does work and what doesn't.
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
Ok I did that but I don't really know now what do you want to say when you told me: "First try and see if the line below fails"..

How? With F8 or?
 

nanscombe

Registered User.
Local time
Today, 07:22
Joined
Nov 12, 2011
Messages
1,082
Because you have comment out the code, that does the work, it should either run successfully or stop with an error like it did before. Just run the code as you would normally.

I'm just trying to get you to eliminate the bits that work correctly.

I also mentioned earlier.


PRVAL, that wouldn't happen to a previous value would it?

What happens if there isn't one?

If are no records where AMFLIBP_MOROUT.PRVAL has no value then you may be able to ignore it, by not doing a UNION ALL, for those cases.

Code:
strSQL1 = "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

  If Nz(PRVAL, "") <> "" Then
    strSQL1 = strSQL1 & "UNION ALL SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & PRVAL & "' "
  Endif

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
You're strong. The code has passed the problematic row.
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
Shit another bug appears...

The error is: No current record.

Lokk at the following code (it appears on the red row)

Code:
strSQL1 = "SELECT AMFLIBP_MOHRTG.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOHRTG "
    strSQL1 = strSQL1 & "WHERE (((AMFLIBP_MOHRTG.ORDNO)='" & MNbr & "') AND ((AMFLIBP_MOHRTG.CLDT)=" & CLDT & ")) "

  If Nz(PRVAL, "") <> "" Then
    strSQL1 = strSQL1 & "UNION ALL SELECT AMFLIBP_MOROUT.* "
    strSQL1 = strSQL1 & "FROM AMFLIBP_MOROUT "
    strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "' AND AMFLIBP_MOROUT.PRVAL='" & PRVAL & "' "
  End If

    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"
    Debug.Print strSQL1
    
    Set rst1 = CurrentDb.OpenRecordset(strSQL1, dbOpenDynaset) 'just collect the information of the Operations tables and enter same name in the normal and extra field
    
   [B][I] [COLOR="Red"][SIZE="5"]rst1.MoveFirst[/SIZE][/COLOR] [/I][/B]
    Do Until rst1.EOF
        rst.AddNew
        rst!ORDNO = MNbr
        rst!CLDT = CLDT
        rst!LATDT = LATDT
        rst!CORD = MNbr
        rst!CCLDT = CLDT
        rst!CLATD = LATDT
        rst!CASTDT = ASTDT
        rst!OP = rst1!OPSEQ
        rst!CASTDT = rst1!ASTDT
        rst.Update
        rst1.MoveNext
    Loop
                
    Set rst = Nothing

End Sub
 

nanscombe

Registered User.
Local time
Today, 07:22
Joined
Nov 12, 2011
Messages
1,082
If there is no current record then the recordset has probably opened successfully but there are no records WHERE AMFLIBP_MOHRTG.ORDNO= MNbr AND AMFLIBP_MOHRTG.CLDT = CLDT, so it may not necessarily be a bug.

You have tried to move to the first record but there isn't one. When the recordset opens it should be at the first record so this line may not be required anyway, so you could probably comment it out as well. Better still replace it with If Not rst1.EOF Then rst1.MoveFirst.

Without the rst1.MoveFirst line it would hit the next line of Do Until rst1.EOF an already be at the End Of File so would skip the next set of code entirely.

Code:
[COLOR="Red"]If Not rst1.EOF Then rst1.MoveFirst[/COLOR] 
    Do Until rst1.EOF
        rst.AddNew
        rst!ORDNO = MNbr
        rst!CLDT = CLDT
        rst!LATDT = LATDT
        rst!CORD = MNbr
        rst!CCLDT = CLDT
        rst!CLATD = LATDT
        rst!CASTDT = ASTDT
        rst!OP = rst1!OPSEQ
        rst!CASTDT = rst1!ASTDT
        rst.Update
        rst1.MoveNext
    Loop
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
Once again you're right! See you for my next bug!
Merci
 

nate0057

Registered User.
Local time
Yesterday, 23:22
Joined
Oct 4, 2012
Messages
74
If there is no current record then the recordset has probably opened successfully but there are no records WHERE AMFLIBP_MOHRTG.ORDNO= MNbr AND AMFLIBP_MOHRTG.CLDT = CLDT, so it may not necessarily be a bug.

You have tried to move to the first record but there isn't one. When the recordset opens it should be at the first record so this line may not be required anyway, so you could probably comment it out as well. Better still replace it with If Not rst1.EOF Then rst1.MoveFirst.

Without the rst1.MoveFirst line it would hit the next line of Do Until rst1.EOF an already be at the End Of File so would skip the next set of code entirely.

Code:
[COLOR="Red"]If Not rst1.EOF Then rst1.MoveFirst[/COLOR] 
    Do Until rst1.EOF
        rst.AddNew
        rst!ORDNO = MNbr
        rst!CLDT = CLDT
        rst!LATDT = LATDT
        rst!CORD = MNbr
        rst!CCLDT = CLDT
        rst!CLATD = LATDT
        rst!CASTDT = ASTDT
        rst!OP = rst1!OPSEQ
        rst!CASTDT = rst1!ASTDT
        rst.Update
        rst1.MoveNext
    Loop

Once again you're right! See you for my next bug!
Merci
 

Users who are viewing this thread

Top Bottom