SQL problem ?

[Tronsliver]

I have a query named qryVacantSlots with two fields: "orgstrPrNbr" and "strestrPosc" I also have a listbox named EfficiencyListBox. sqleff is defined as a global variable.

The SQL statement I'm using to populate the listbox is embedded in a Public Sub Below. The call I'm using to pass the two arguments to the procedure is:

Call Efficiency(643, Var2)
EfficiencyListBox.RowSource = sqleff

Var2 gets its assignment from clicking on the list box and assigning column 2 to the variable. When I run in debug and track the arguments I find that they are reaching the Sub. However, for some reason the SQL search on the two fields is not working. Var2 is defined as a "text" field in the original table. One last note, when I look at var2's variable in "watch" the variable is in quotes like this "92A00". I believe the problem is how I have the last two lines in the SQL string. Basically the way its suppose to work is:

The Sub takes the two varibales and conducts a search based on their value. In other words if varx = 648 and vary = 92A00 then show the information in the list box.

Sorry for the long explantion...Thanks for assisting.


Public Sub Efficiency(varx As Integer, vary As Variant)

sqleff = "SELECT qryVacantSlots.Available As Open,"
sqleff = sqleff + " qryVacantSlots.strestrPosc As DutyPos, "
sqleff = sqleff + " qryVacantSlots.strestrGrade As Grade,"
sqleff = sqleff + " qryVacantSlots.strestrauthparadsg As Para,"
sqleff = sqleff + " qryVacantSlots.strestrauthlinedsg As Line,"
sqleff = sqleff + " qryVacantSlots.orgstruname As Unit,"
sqleff = sqleff + " qryVacantSlots.orgstraddresscity As City"
sqleff = sqleff + " FROM qryVacantSlots WHERE qryVacantSlots.orgstrPrNbr"
sqleff = sqleff + " = '" & Format(varx) & "' "
sqleff = sqleff + " AND qryVacantSlots.strestrPosc = ' " & Format(vary) & "' "

End Sub

 

[lespaul]

Since varx is defined as a number in the function, I'm assuming the field orgstrPrNbr is a number in the table and not text? If it's a number, remove the single quotes :

sqleff = sqleff + " = " & Format(varx) & "


Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Tronsliver]

lesPaul,

OK it was a text field and I made the change but it still doesn't work. Am I using the "AND" operator correctly in the SQL statement (last line)

 

[lespaul]

Ok, if it's a text field, then you need the single quotes!

Where do you open the sqleff statement? Perhaps you need to define a recordset as the return value of the Sub?

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!

 

[Tronsliver]

I open it in a standard module. Reason being is I have 5 other forms that use the Sub. What is strange about this problem is the code below works. It is also accessed from another form module vai a sub call. It works even though the table field is defined as text and I have the argument defined as a Integer. Maybe the compiler somehow casts the variable to text. Anyway this does work but as soon as I add the addition search var (vary) it does not work. This is why I believe it is somehow related to the format ?

Public Sub FindVacantSlots(varx As Integer)

sqlslots = "SELECT qryVacantSlots.Available As Open,"
sqlslots = sqlslots + " qryVacantSlots.strestrPosc As DutyPos, "
sqlslots = sqlslots + " qryVacantSlots.strestrGrade As Grade,"
sqlslots = sqlslots + " qryVacantSlots.strestrauthparadsg As Para,"
sqlslots = sqlslots + " qryVacantSlots.strestrauthlinedsg As Line,"
sqlslots = sqlslots + " qryVacantSlots.orgstruname As Unit,"
sqlslots = sqlslots + " qryVacantSlots.orgstraddresscity As City"
sqlslots = sqlslots + " FROM qryVacantSlots WHERE (((qryVacantSlots.orgstrPrNbr)"
sqlslots = sqlslots + " = '" & Format(varx) & "'));"


End Sub

 

[lespaul]

what if you declare it as a string rather than a variant?

Leslie
landrews@metrocourt.state.nm.us

There are 10 types of people in the world -
those who understand binary
and
those who don't!