Debug.Print

N

nate0057

Registered User.
Local time
Today, 13:23
Joined
Oct 4, 2012
Messages
74
Hi everybody,

How and why use "debug.print"?

Thanks a lot
 
pbaldy said:
This should help:

http://www.baldyweb.com/ImmediateWindow.htm
Click to expand...

Thank you about that.
After using Debug.Print, I have in the immediate sheet this code:

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

The initial after launching the program is :
"Syntax error (missing operator) in query expression 'AMFLIBP_MOROUT.ORDNO="M794840" AND AMFLIBP_MOROUT.PRVAL='

What can I do now??????

Could you help me??

Thanks!!!
 
I imagine it's failing at this point as there is no value for PRVAL. An empty control on a form perhaps?

Code: 
AMFLIBP_MOROUT.PRVAL= ORDER BY OPSEQ
 
nanscombe said:
I imagine it's failing at this point as there is no value for PRVAL. An empty control on a form perhaps?

Code: 
AMFLIBP_MOROUT.PRVAL= ORDER BY OPSEQ
Click to expand...

Yes it is! PRVAL is a column in a table and some of rows are equal to zero. But they probably have to be equal to zero. So how resolve these problem??
 
It's wherever you are getting your criteria from in the WHERE clause. It's like it was ...

Code: 
WHERE AMFLIBP_MOROUT.ORDNO='" & [B]someValue[/B] & "' AND AMFLIBP_MOROUT.PRVAL=" & [B]otherValue[/B] & " ORDER BY OSEQ

Where are you getting otherValue from?
 
It's probably Null, so perhaps wrapping that in the Nz() function to force a 0 in there?
 
nanscombe said:
It's wherever you are getting your criteria from in the WHERE clause. It's like it was ...

Code: 
WHERE AMFLIBP_MOROUT.ORDNO='" & [B]someValue[/B] & "' AND AMFLIBP_MOROUT.PRVAL=" & [B]otherValue[/B] & " ORDER BY OSEQ

Where are you getting otherValue from?
Click to expand...

"Some value" and "other value" are two column in the table named MOROUT.
 
Well, you haven't shown the code that results in that SQL, so it's hard to tell how to fix.
 
According to your earlier post you are getting the result ..

Code: 
"Syntax error (missing operator) in query expression 'AMFLIBP_MOROUT.ORDNO="M794840"
AND AMFLIBP_MOROUT.PRVAL='

You are getting the value "M794840" from somewhere and it is working whereas wherever you are trying to get the other value from is failing to provide an answer.

The immediate window has allowed you to track down the problem, now you need to go back to the query and find out why no value is being provided to check against AMFLIBP_MOROUT.PRVA
 
strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'
AND AMFLIBP_MOROUT.PRVAL=" & PRVAL & " "

So the problem is probably PRVAL, which may need to become nz(PRVAL,0) ...

strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'
AND AMFLIBP_MOROUT.PRVAL=" & NZ(PRVAL,0) & " "
 
nanscombe said:
According to your earlier post you are getting the result ..

Code: 
"Syntax error (missing operator) in query expression 'AMFLIBP_MOROUT.ORDNO="M794840"
AND AMFLIBP_MOROUT.PRVAL='

You are getting the value "M794840" from somewhere and it is working whereas wherever you are trying to get the other value from is failing to provide an answer.
Click to expand...

Yes, I'm getting the value M794840 in the MOROUT's table because it is in the column "from value" (ORDNO). But the value associated to M794840 is equal to 0 in the other value (PRVAL) column
 
nanscombe said:
strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'
AND AMFLIBP_MOROUT.PRVAL=" & PRVAL & " "

So the problem is probably PRVAL, which may need to become nz(PRVAL,0) ...

strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'
AND AMFLIBP_MOROUT.PRVAL=" & NZ(PRVAL,0) & " "
Click to expand...

I don't know you're probably right.
Iwill try it now
 
nanscombe said:
strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'
AND AMFLIBP_MOROUT.PRVAL=" & PRVAL & " "

So the problem is probably PRVAL, which may need to become nz(PRVAL,0) ...

strSQL1 = strSQL1 & "WHERE AMFLIBP_MOROUT.ORDNO='" & MNbr & "'
AND AMFLIBP_MOROUT.PRVAL=" & NZ(PRVAL,0) & " "
Click to expand...

And....... No. The error still the same...

I despaired.
 
You are getting MNbr and CLDT from somewhere and they appear to be all right, it's just PRVAL.

Is it a variable? A control on a form? A column from a table / query?
 
nanscombe said:
You are getting MNbr and CLDT from somewhere and they appear to be all right, it's just PRVAL.

Is it a variable? A control on a form? A column from a table / query?
Click to expand...

It's a column from a table like CLDT ord MNBR..
 
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=" & PRVAL & " "
strSQL1 = strSQL1 & "ORDER BY OPSEQ;"


The bits in bold are what I would call columns in the query.

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=" & PRVAL & " "
strSQL1 = strSQL1 & "ORDER BY OPSEQ;"

These are the bits that I'm interested in. They don't appear to be defined anywhere in the code you supplied. Is the code snippet from a function and are these values parameters perhaps?

Example Public Function myFunction (byval MNbr as string, byval CLDT as string, byval PRVAL as Long)

Code: 
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=" & PRVAL & " "
    strSQL1 = strSQL1 & "ORDER BY OPSEQ;"
    
    Debug.Print strSQL1
    
    Set rst1 = CurrentDb.OpenRecordset(strSQL1)  'just collect the information of the Operations tables and enter same name in the normal and extra field
    
    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
                
    Set rst = Nothing
 
Last edited:
So PRVAL is a string!


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)  'just collect the information of the Operations tables and enter same name in the normal and extra field
    
    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
                
    Set rst = Nothing

End Sub


If PRVAL is a string then the code AMFLIBP_MOROUT.PRVAL= " & Nz(PRVAL, 0) & " " would never work in a month of sundays.

What's needed is probably AMFLIBP_MOROUT.PRVAL= '" & Nz(PRVAL, "") & "' " with quotes around the PRVAL bit.

This should get closer.


The line Sub MotherPacketOP(MNbr As String, LATDT As String, CLDT As String, PRVAL As String, ASTDT As String) is probably crucial to trying to solve this. Without it we would probably get nowhere. This is why I kept asking where the values came from. :)
 
You must log in or register to reply here.

Similar threads

P
Is there a way of deleting all code lines beginning with debug.print across all modules using VBA?
2
Replies
26
Views
1,878
DickyP
DickyP
S
New to MS Access
Replies
3
Views
774
theDBguy
theDBguy
Gasman
Solved Why an overflow?
Replies
12
Views
520
Isaac
Isaac
R
VBA not working for saved query parameters
2
Replies
24
Views
776
RonPaii
RonPaii
C
Solved Treeview address
Replies
8
Views
825
ClaraBarton
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom