[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Basic syntax in recordset

Posted on 2011-09-16
13
Medium Priority
?
358 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 500 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 500 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 35

Assisted Solution

by:Norie
Norie earned 500 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 500 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 35

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 35

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

650 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