Solved

Basic syntax in recordset

Posted on 2011-09-16
13
351 Views
Last Modified: 2012-05-12
This is the code I am using for the recordset.  

 Dim rst As ADODB.recordset
    Dim strSql As String
    Set cnn = CurrentProject.Connection
    Dim vtxtBox
       
        strSql = "SELECT PrimaryKey, NEWRSKTB.DIV, SEQ, PROCF, PROCT, HCPCF, HCPCT, P, PV, T, SP " _
        & "FROM NEWRSKTB LEFT JOIN Column_Set_Division ON NEWRSKTB.COL = Column_Set_Division.Column " _
        & "WHERE PrimaryKey = " & lstRiskView & ""
       
    Set rst = New ADODB.recordset
    rst.Open strSql, cnn, adOpenStatic, adLockOptimistic, adCmdText
 
    If rst.EOF Then
        MsgBox "No Record(s) found"
        Exit Sub
    End If
     
    If IsNull(rst("PROCF")) = False Then
        MsgBox "PROCF is not null"
        txtProcCodeF = rst("PROCF")
    Else
         txtProcCodeF > 0 And txtProcCodeF < 99999
    End If

rst("PROCF") is in a text format in the table
The problem is that in the else statement access does not like > 0.  I get the error:

Compile error
Exprected Expression

It works if I a put an = sign but not when I use the > sign.  I am at a loss since I know I have been able to do this before

Thanks,
Scott


0
Comment
Question by:Scott Palmer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +2
13 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 125 total points
ID: 36550231
txtProcCodeF > 0 And txtProcCodeF < 99999

You can't set somthing to be greater than (or less than) some value... you have to set it to be equal to something.

What are you trying to accomplish?
0
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 125 total points
ID: 36550239
>> The problem is that in the else statement access does not like > 0.  I get the error: <<

That is because your expression is returning a Boolean value and you have not given VBA a place to store that value ... you basically have writen the "right" side of an assignment ...

From what I see it seems like you are wanting to check to see if the value of the PROCF field is between two values, and if it is, then do something.  If that is the case then you need an ELSE IF ...

    If IsNull(rst("PROCF")) = False Then
        MsgBox "PROCF is not null"
        txtProcCodeF = rst("PROCF")
    ElseIf txtProcCodeF > 0 And txtProcCodeF < 99999 Then
        MsgBox "PROCF is between 0 and 99999
    Else
        MsgBox "PROCF is out of range"
    Else
       
    End If

----

If that is not what you are after, then please explain in further detail as to what your goal is.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36550241
Should that be an IF statement?

If txtProcCodeF > 0 And txtProcCodeF < 99999  Then
   'Do something
End if
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 34

Assisted Solution

by:Norie
Norie earned 125 total points
ID: 36550258
Scott

What's this supposed to do?

 txtProcCodeF > 0 And txtProcCodeF < 99999

It's just an expression that will evaluate to true or false.

It isn't assigned to a variable, or used in an If etc...
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36550263
PS ... I know the code does not make sense because if its NOT Null, then the first brach will be taken ... I was merely trying to show the syntax of ElseIf and I wasn't worried with the logic.
0
 
LVL 82

Assisted Solution

by:hielo
hielo earned 125 total points
ID: 36550319
try:
If rst.EOF Then
        MsgBox "No Record(s) found"
        Exit Sub
    End If
     
    If IsNull(rst("PROCF")) = False Then
        MsgBox "PROCF is not null"
        txtProcCodeF = CInt(rst("PROCF"))
        IF txtProcCodeF > 0 And txtProcCodeF < 99999 Then
           MsgBox "PROCF is within range"
        Else
           MsgBox "PROCF is negative OR > 99999"
        End If
    Else
        MsgBox "PROCF = null"
    End If

Open in new window

0
 

Author Comment

by:Scott Palmer
ID: 36550358
Yea, that makes sense.  What am I trying to do?  The impossible I guess.  

The problem is I am using a query that uses text boxes on a form for its parameters.  But sometime the text boxes are blank.  When I run the query it seems the query is getting something form the blank text boxes and using it as a parameter making the results incorrect.  

When the text box is empty it should be as if the field in the query (PROCF) has no filter.  I would do it in VBA but I have 7 boxes I have to use as filters and so I don't have time to do all of hte permutations.
0
 

Author Comment

by:Scott Palmer
ID: 36550438
Is there a way in the code I can tell the query which parameters to use or to use none at all?

As in:

If IsNull(rst("PROCT")) = False Then
  Use these parameters
else
  do not use any parameters
end if
0
 
LVL 34

Expert Comment

by:Norie
ID: 36550589
If you were dealing with a null value for a parameter in a query you would use the same parameters but substitute something, usually a wildcard, for the null values.
0
 

Author Comment

by:Scott Palmer
ID: 36550600
Thanks imnorie, I was just thinking of how to do that.  Doing it now.
0
 
LVL 34

Expert Comment

by:Norie
ID: 36550668
In the query or the code?

It'd probably be easier to do in the query, and might be useful if you use it anywhere else.
0
 

Author Closing Comment

by:Scott Palmer
ID: 36551170
Had to totally redo how I am doing this with some manual stuff.  Can't spend more time on it.  Thanks for all of you help.  All of the answers helped me realized my management has not a clue.

Thanks.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36551233
In a Query object the depends on a Text Box control and a blank Text Box mean "don't filter this field" then you Query object's SQL would look something like this:

SELECT someTable.*
FROM someTable
WHERE (someField = [someTextBoxReferene] Or [someTextBoxReference] Is Null)

There are other ways to do this, but the above is fairly common.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

729 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question