Solved Action Query Parameter Numerical Comparissons In VBA (2 Viewers)

[dalski]

I want to assign parameters to an action query. I'm trying to avoid doing it by altering the querydef.SQL property in VBA. It would be much nicer to do it in the below fashion but I'm struggling with the syntax of the last two:

• SortOrderAdjustmentToMake should take the values in the query field [SortOrder]+1
• SortOrderCriteria is a simple > comparisson with a var. Struggling with how to assign > & the variable

 

[Minty]

Your query def should be > [SortOrderCriteria]

You can't bodge the ">" into the parameter - it's declared as a long, and wouldn't work as a string.
So in the query simply place the > in front of the parameter holder and save it.

 

[dalski]

Thanks Minty, getting there. I think I need to encase in brackets & I need to actually set the SortOrder criteria I think. but another problem occurs. Because I have a same named property in the class this code is running I think I need to explicitly force the value returned from the query; rather than the class property. I would've thought as I'm encasing in square brackets it would be ok, but it refers to the class property rather than the query's field. How do i force in the query's field? It jumps to the class' property even if I wrap it in an Eval; stating External name not defined.

Eval([Tender-PgheadersAdjustSortOrderAQ].[SortOrder]) > mBranchLastItemInSortOrder)

 

[cheekybuddha]

Minty is right - put the operators in the actual SQL.

Just pass values in the parameters

 

[dalski]

Thanks, sorry I do not understand. How do i actually put the operators in the actual SQL. What is the syntax? As in the OP I do not want to hardcode a queryDef. I know how to do this, I am trying to minimize code on screen as I have amass of code in this particular class and a parametized query is the desired solution if it is possible; setting the values of the parameters in VBA. I need to be able to change the operators.

Seems one cannot just put a > operator in front of the parameter as shown in the pic with red SortOrderCriteria. I think this might not be possible & I'm going to have to define the queryDef.SQL in code, using variables for the operators & parameters.

 

[Minty]

Thanks, sorry I do not understand. How do i actually put the operators in the actual SQL. What is the syntax? As in the OP I do not want to hardcode a queryDef. I know how to do this, I am trying to minimize code on screen as I have amass of code in this particular class and a parametized query is the desired solution if it is possible. I need to be able to change the operators.

Seems one cannot just put a > operator in front of the parameter as shown in the pic with red SortOrderCriteria.

No you can't.

Consider this

SELECT Blobby From TABLE1
Where CNum > [@YourParameterValue]

Against

SELECT Blobby From TABLE1
Where CNum "> [@YourParameterValue]"

Can you see why it won't work? It doesn't understand the second one, as it's not a valid SQL statement.

 

[dalski]

Thanks Minty, cheekybuddha, yes I understand the previous issue concatenating data-types. I thought maybe I had to force the ">" operator as it was not accepting it & maybe it was one of the quirks of VBA like using a string literal as a table name...

This is looking like it is not possible to use VBA to assign different operators to the parameters of a parametized query & assign variables to the parameters at the same time. I know you can set the parameter of a parametized query in vba. But it seems VBA cannot handle the Operator & the Parameter in the same line/ there isn't a workaround.

Looking like I'm going to have to define the QueryDef.SQL in vba?

 

[cheekybuddha]

What is the SQL of query Tender-PgHeadersAdjustSortOrderAQ ?

 

[dalski]

She'd be as the output pictured from the query designer, but I'm fine with altering all of that jazz. I've never used a parameter query before with actual defined parameters in the parameter window & that's got me really confused & I thought Parameter Queries were more powerful than they are.

I'm pretty sure it's not possible, going to have to define QueryDef.SQL in code I think.

 

[cheekybuddha]

What is the SQL of query Tender-PgHeadersAdjustSortOrderAQ ?

Click on the SQL view button and copy and paste the SQL here

 

[dalski]

Thanks, sorry there were some essentials I left out in the OP. I thought I'd be able to pass the operators as parameters; but you cannot do this. The SQL editor needs the operators or it'll error out when defining the query, so you cannot move forward to the next step.

Your query def should be > [SortOrderCriteria]

So in the query simply place the > in front of the parameter holder and save it.

I misread this thinking definintion in the VBA but Minty meant 'in the query SQL editor'. I thought in front of the parameter meant it was possible to put the operator in the query parameter; but he meant 'in front' of the queryParameter in the SQL Editor. Not 'in front' of the queryParameter in VBA as part of the query parameter definition.

I had the query already working for the above as a hardcoded queryDef passing var's for the operators & values & it is completely dynamic; but I was hoping to use a parameter query. But the drawback would be I would need to define each query separately; hardcoding the operators.
But I'm better off defining the SQL in VBA allowing the operators & parameters to be dynamic. It's a shame the query parameters dialogue does not have a datatype for operators. I thought the SQL editor would apply an implicit typecast with the operator ">" but it does not & I didn't for a moment think the SQL Editor parameter dialogue would not allow me to pass operators so didn't think I needed to raise the issue in the OP - again my apologies.

Minty is right - put the operators in the actual SQL.

This had me perplexed as I thought you meant somewhere in the VBA SQL; not the SQL Editor I realize you were now referring to. But I did not make it clear I needed the operators to be dynamic.

Thanks again chaps.

 

[Minty]

You can use a "empty" parameter query and simply define the SQL as you go. No need to hard code it.
You could in fact store the SQL strings in a table and use string manipulation to replace holders for the criteria operators?

That would make the queries themselves data driven.
Whether it is worth the effort depends on the bigger picture I guess.

 

[dalski]

Thanks Minty, that's interesting. "String manipulation to replace holders for the criteria operators"? That's what i was after in the OP, what do you have to do to convert ">" to get it to behave as an operator when being passed through the QueryDef.Parameter as an argument. I'm not after storing them in tables, I could just have them as variables/ in a collection whatever; but I'm probably overlooking something here.
But the issue I've encountered is I cannot put an operator in a query.Parameter. I can put an operator as a string in vba & an implicit typecast seems to happen with the string variable when using an operator. But not when going through the QueryDef.Parameter.

Oh I'm misreading again I think, "an empty parameter query", so nothing in the query, but concatenate the strings dependant on runtime logic. Thanks, I didn't think of that, in this particular case I think that would be around 25 times the amount of work on this occasion. But an interesting idea that I never ever would have thought of, thanks.

 

[cheekybuddha]

Really you only need 2 parameters since the amount of increment is always [SortOrder] + 1; one for the ID of the record, and one to mark where to start the increment.
But you can have on for if you are incrementing or decrementing

You could have your query SQL like this:

UPDATE Tender-HeadersQ
SET SortOrder = Sortorder + [SortOrderAdjustmentToMake]
WHERE PageID_FK = [PgIDCriteria]
  AND SortOrder > [SortOrderCriteria]
;

Saved in query Tender-PgHeadersAdjustSortOrderAQ

Then your code:

With CurrentDb.QueryDefs("Tender-PgHeadersAdjustSortOrderAQ")
  .Paramters("SortOrderAdjustmentToMake") = 1       ' Or -1 to decrement
  .Paramters("PgIDCriteria") = cPage.PgIdToView
  .Paramters("SortOrderCriteria") = mBranchLastItemInSortOrder
  .Execute dbFailOnError
End With

 

[cheekybuddha]

@dalski - I edited an error in the SQL after you 'liked'/saw my post - make sure you refresh

 

[cheekybuddha]

Normally, when changing sort orders you need min and max between which to increment/decrement

Eg, if you have 10 items, and you move item 8 to position 4, then you need to only increment items 4 - 7 (each +1)
If you move item 5 to position 7, you then need to decrement items 6 and 7 by -1

Make sure your logic is correct.

 

[dalski]

Thanks Dave, I've really struggled with the logic. This is the most basic one I have planned & I am failing massively. This looks exactly what I'm after. The only downside I see is that I could get confused with presumably having to pass a var for >. Sometimes >=,<,<=, who knows what on the harder ones. In my simple little mind it's clear to me with the hardcoded queryDef what I need to do, but lacking confidence with query parameters atm. I don't know whether to persevere down this route - with query parameters or to just code the query def in VBA. Which I'm more comfortable & seems I would only have to have one query definition & pass different variables for the operators & numerators.
As I'm inexperienced I wonder what I'll be less confused with later. In your experience which route you think better & clearer code to understand?

UPDATE Tender-HeadersQ
SET SortOrder = Sortorder + [SortOrderAdjustmentToMake]
WHERE PageID_FK = [PgIDCriteria]
  AND (SortOrder > [SortOrderCriteria]
;

Oooooh, I thought +1 turning into 1 was not working, but this is the syntax

But you can have on for if you are incrementing or decrementing

What do you mean here Dave? Please translate so an Alsatian could understand.

 

[cheekybuddha]

What do you mean here Dave? Please translate so an Alsatian could understand.

Sorry, typo, it should read:

But you can have one for if you are incrementing or decrementing

(where one is a parameter)

IE, id you are just incrementing then you do not need the parameter as you can hardcode Sortorder + 1

But, using the parameter you can pass 1 or -1 to increment/decrement with the same statement.

 

[cheekybuddha]

Changing sort order usually requires 2 passes:

1. Adjust one or more items to SortOrder +/- 1 between item being moved and item where it is moving to.
2. Adjust the sort order of the moved item to its new position

Something along the lines of:

Function MoveItem(ItemID As Long, CurrentPos As Integer, NewPos As Integer) As Boolean

  Dim strSQL As String
 
  If CurrentPos <> NewPos Then
    With CurrentDb
      ' Move everything between CurrentPos and NewPos up or down
      strSQL = "UPDATE YourTable SET SortOrder = SortOrder " & _
               IIf(NewPos > CurrentPos, "-", "+") & " 1 " & _
               "WHERE SortOrder BETWEEN " & CurrentPos & " AND " & NewPos & ";"
      .Execute strSQL, dbFailOnError
      ' Set the correct pos for the moved item
      strSQL = "UPDATE YourTable SET SortOrder = " & NewPos & " " & _
               "WHERE ID = " & ItemID & ";"
      .Execute strSQL, dbFailOnError
    End With
  End If
  MoveItem = Err = 0
 
End With

The above is simplistic code - you might have an issue if you have a unique index on the SortOrder field since you will have a moment where 2 items have the same sort order; in which case an extra step might be required to first set the moved item's sort order to something out of range like -1 before the other operations.

With 3 operations you might want to use a Transaction to ensure all or nothing.

And similarly if you are in a multi-user environment

 

[dalski]

Thanks so much Dave, massively appreciated.