Solved

Basic syntax in recordset

Posted on 2011-09-16
13
341 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:ScootterP
  • 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
 
LVL 33

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:ScootterP
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:ScootterP
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 33

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:ScootterP
ID: 36550600
Thanks imnorie, I was just thinking of how to do that.  Doing it now.
0
 
LVL 33

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:ScootterP
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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now