Basic syntax in recordset
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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.
ASKER
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
As in:
If IsNull(rst("PROCT")) = False Then
Use these parameters
else
do not use any parameters
end if
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.
ASKER
Thanks imnorie, I was just thinking of how to do that. Doing it now.
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.
It'd probably be easier to do in the query, and might be useful if you use it anywhere else.
ASKER
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.
Thanks.
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.
SELECT someTable.*
FROM someTable
WHERE (someField = [someTextBoxReferene] Or [someTextBoxReference] Is Null)
There are other ways to do this, but the above is fairly common.
If txtProcCodeF > 0 And txtProcCodeF < 99999 Then
'Do something
End if