SQL from VBA to Criteria 1

[sterlecki]

I need to write an sql statement setting a text box on a form me![txt_Targden] = Like 10.*

so this can be used in a query as a criteria
[Forms]![frm_DrillOutSchedule]![txt_Targden])

I can make this work with a single integer but I need to use a Like statement or a >9 type of criteria

The full sql of my query is below:

SELECT data_Wells_Drillout.WaterDatum, data_Wells_Drillout.PadAssignment, data_Wells_Drillout.[Target Density], data_Wells_Drillout.QEP_Status, data_Wells_Drillout.PadID
FROM data_Wells_Drillout
WHERE (((data_Wells_Drillout.[Target Density])=[Forms]![frm_DrillOutSchedule]![txt_Targden]) AND ((data_Wells_Drillout.PadID)=[Forms]![frm_DrillOutSchedule]![cboSelectPad]))
ORDER BY data_Wells_Drillout.[Target Density] DESC , data_Wells_Drillout.WaterDatum;

 

[genomon]

me![txt_Targden] = Like 10.*

This doesn't throw an error? What exactly are you looking for?

When everything is coming your way, you're in the wrong lane.

 

[dhookom]

I would use two text boxes

WHERE [Target Density] Between [Forms]![frm_DrillOutSchedule]![txt_MinTargden] AND [Forms]![frm_DrillOutSchedule]![txt_MaxTargden]

You don't have to show either of these text boxes and can use code to evaluate a user entry and populate the min and max text boxes.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[GingerR]

In order to ensure the users are entering something that makes sense, I'd put a combo box to the left of the current text box; the values to choose would be

=
<>
Like
>
>=
<
<=

and the existing text box would just be an integer.

Then in your query, in a new column put

[Forms]![frm_DrillOutSchedule]![cboComparitor]
and put in the various critiera under it, one in each row:

"="
"Like"
"<>"

etc

and then for each row's criteria under the [Target Density] field, put the correct criteria. In order of above, they'd be:

= Forms![frm_DrillOutSchedule]![txt_Targden]
Like "*" & Forms![frm_DrillOutSchedule]![txt_Targden] & "*"
<> Forms![frm_DrillOutSchedule]![txt_Targden]

etc

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[sterlecki]

dhookem,

Thanks for this simple answer.
I used this VBA code from my option Group


Private Sub opgrp_TargetDensity_AfterUpdate()

Dim varTargden As Variant
Dim strOperator As String
Dim intTargdenmin As Integer
Dim intTargdenmax As Integer




If Me!opgrp_TargetDensity = 2 Then
intTargdenmin = 10
intTargdenmax = 11
Me!txt_Targdenmin = intTargdenmin
Me!txt_Targdenmax = intTargdenmax
ElseIf Me!opgrp_TargetDensity = 3 Then
intTargdenmin = 5
intTargdenmax = 6
Me!txt_Targdenmin = intTargdenmin
Me!txt_Targdenmax = intTargdenmax
Else
intTargdenmin = 1
intTargdenmax = 11
Me!txt_Targdenmin = intTargdenmin
Me!txt_Targdenmax = intTargdenmax

End If

And combined it with the SQL from my query
SELECT data_Wells_Drillout.WaterDatum, data_Wells_Drillout.PadAssignment, data_Wells_Drillout.[Target Density], data_Wells_Drillout.QEP_Status, data_Wells_Drillout.PadID
FROM data_Wells_Drillout
WHERE (((data_Wells_Drillout.[Target Density]) Between [Forms]![frm_DrillOutSchedule]![txt_Targdenmin] And [Forms]![frm_DrillOutSchedule]![txt_Targdenmax]) AND ((data_Wells_Drillout.PadID)=[Forms]![frm_DrillOutSchedule]![cboSelectPad]))
ORDER BY data_Wells_Drillout.[Target Density] DESC , data_Wells_Drillout.WaterDatum;


All is working fine and was very simple