Select Query with IF THen statement

[jabenj]

Hi All,

What I'm trying to do seems simple enough yet I can't get it to work. I am querying a database and I want a text to display based on my IF Then statement. However, it seems as though my select query is only reading my most recent value in my table instead of the select values according to my where statement. Any help would be greatly appreciated. Thanks

Private Sub cmbTankNum_Change()

Dim stSql As String
Dim rs As Object
Dim con As Object



Set con = Application.CurrentProject.Connection
stSql = "Select [LotNum] FROM [Blendsheet Input]"
stSql = stSql & " WHERE [TankNum] = '" & cmbTankNum.Value & "'"

Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1

TxtStatus.SetFocus

If [LotNum] = True Then
TxtStatus.Text = ("Finalled")

Else
TxtStatus.Text = ("jermaine")

End If
rs.Close
Set rs = Nothing
Set con = Nothing


Exit_Close_Click:
Exit Sub

End Sub

 

[PHV]

Replace this:
If [LotNum] = True Then
with this:
If rs![LotNum] = True Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Where are you running this code? I'm not sure this is a query question and should probably be in forms coding.

You are creating a recordset and then seem to ignore it until you close it.

You can only use the Text property in Access when the text box has the focus. Normally you would use the Value property to set or reference a text box value. You don't have to set the focus when using the Value property.

Can you add the LotNum field to the Row Source of the combo box? If so, you don't need to create any recordset. Just reference the Me.cmbTankNum.Column(x) value in the After Update event (not the change) of the combo box.

Duane
Hook'D on Access
MS Access MVP

 

[lespaul]

additionally, you can get your FINALLED and JERMAINE right in the query:

SELECT LotNum, IIF(LotNum, "Finalled", "Jermaine") FROM [Blendsheet Input]

Leslie

Have you met Hardy Heron?

 

[jabenj]

Thanks for Help! The "If rs![LotNum] = True Then" statement kind of worked. However now it seems that even though the value is "True" in a couple scenarios, the correct If statement isn't executed.

I am running this code from a form in access (running query in form). I can not add the LotNum field to the Row Source of the Combo box

 

[dhookom]

What's your most recent code? Is there potentially more than one of the same TankNum in the table Blendsheet Input?

Duane
Hook'D on Access
MS Access MVP

 

[jabenj]

Yes the same TankNum can be displayed for multiple rows in the table and that's the problem. Ultimately, I want the correct text to be displayed if any "LotNum" field value associated with a specific TankNum does not contain a text (or is null). I originally tried:

If [LotNum] = Null Then
TxtStatus.Text = ("Not Finalled")

Else
TxtStatus.Text = ("jermaine")

But this does not work.

Thanks again.

 

[dhookom]

I would try these changes:

Private Sub cmbTankNum_Change()
  If Dcount("LotNum","[Blendsheet Input]","TankNum ='" & Me.cmbTankNum & "'") = 0 Then
    Me.TxtStatus = "Not Finalled"
   Else
    Me.TxtStatus = "jermaine"
  End If
Exit_Close_Click:
  Exit Sub
End Sub

Or just set the control source of txtStatus to

=IIf(Dcount("LotNum","[Blendsheet Input]","TankNum ='" & cmbTankNum & "'") = 0,"Not Finalled","Jermaine")

Duane
Hook'D on Access
MS Access MVP

 

[jabenj]

Get the same problem. Maybe I need to include a time stamp so the If statement can query the most recent entry for a specific tank

 

[dhookom]

The expression that I suggest should match your specification of

any "LotNum" field value associated with a specific TankNum does not contain a text (or is null)

Try create a query with a specific tanknum value:

Select [LotNum] , Count(*) as NumOf
FROM [Blendsheet Input]
WHERE [TankNum] = "A particular Tank Num"
GROUP BY LotNum;

If the NumOf is greater than 0 then are some non-null values. Do you understand the entire calculation will not trigger simply when you change records in your form? All values will display the same until you do something to update the combo box.

Duane
Hook'D on Access
MS Access MVP

 

[jabenj]

I gave up on this some time ago but revisited it recently and with some help got the solution. The problem was that the Dcount function does not read Null values; it skips over it. The following finally worked:


Dim i As Integer
i = DCount("*", "[Blendsheet Input]", "TankNum ='" & Me.cmbTankNum & "' AND LotNum IS NULL")

If i = 0 Then
Me.TxtStatus = "Finalled"
Else
Me.TxtStatus = "Not Finalled"
End If