?
Solved

Basic syntax in recordset

Posted on 2011-09-16
13
Medium Priority
?
353 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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
LVL 34

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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.
Suggested Courses

777 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