?
Solved

Basic syntax in recordset

Posted on 2011-09-16
13
Medium Priority
?
360 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
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

840 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