UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Using A Variable As Query Criteria, Access 2010    
 
   
Dwellable
post Jun 11 2019, 05:14 PM
Post#1



Posts: 5
Joined: 9-April 19



I have a form that allows users to generate custom reports. In particular, the user can select shifts (1, 2, and/or 3) using check boxes on the form. I use TempVars to pass the selection to the query criteria. I have the following code in the onclick event,

If Me.shift1.Value = -1 And Me.shift2.Value = -1 And Me.shift3.Value = -1 Then
TempVars!shifts = Null
ElseIf Me.shift1.Value = -1 And Me.shift2.Value = -1 And Me.shift3.Value = 0 Then
TempVars!shifts = "<>3"
ElseIf Me.shift1.Value = -1 And Me.shift2.Value = 0 And Me.shift3.Value = -1 Then
TempVars!shifts = "<>2"
ElseIf Me.shift1.Value = 0 And Me.shift2.Value = -1 And Me.shift3.Value = -1 Then
TempVars!shifts = "<>1"
ElseIf Me.shift1.Value = -1 And Me.shift2.Value = 0 And Me.shift3.Value = 0 Then
TempVars!shifts = "1"
ElseIf Me.shift1.Value = 0 And Me.shift2.Value = -1 And Me.shift3.Value = 0 Then
TempVars!shifts = "2"
ElseIf Me.shift1.Value = 0 And Me.shift2.Value = 0 And Me.shift3.Value = -1 Then
TempVars!shifts = "3"
End If

This is for selecting 1st, 2nd or 3rd shift.

I have the following function as my query criteria,

IIf([tempvars]![shifts] Is Null,[R1Table].[sshift],[tempvars]![shifts])

It works great if you select all shifts or if you select an individual shift. It does not work at all if you select any combination of 2 shifts. I've tried many variations of <>3, not 3, and chr(34) sprinkled here and there but I continue to get a run time error 3071.

What am I missing?
Go to the top of the page
 
June7
post Jun 11 2019, 05:56 PM
Post#2



Posts: 649
Joined: 25-January 16



It fails on the <> lines? Not surprising, AFAIK operator sign cannot be dynamic in query and therefore cannot be passed as part of criteria.

Also, the criteria you show looks incomplete. What is the full WHERE clause from query?

I have never used TempVars. Nor do I use dynamic parameterized queries. I prefer VBA to build criteria and apply filter to form or report when opening.

This post has been edited by June7: Jun 11 2019, 06:14 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
Dwellable
post Jun 11 2019, 06:13 PM
Post#3



Posts: 5
Joined: 9-April 19



WHERE (((R1Table.sshift)=IIf([tempvars]![shifts] Is Null,[R1Table].[sshift],[tempvars]![shifts]))

Is the where clause.

Go to the top of the page
 
June7
post Jun 11 2019, 06:17 PM
Post#4



Posts: 649
Joined: 25-January 16



Explains the failure. Compiled line could end up:

WHERE R1Table.sshift = <>3

which makes no sense.

Also, cannot compare anything to Null. Review http://allenbrowne.com/casu-12.html

Review http://allenbrowne.com/ser-62.html

This post has been edited by June7: Jun 11 2019, 06:19 PM

--------------------
Attachments Manager is below the edit post window. To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
DEBUG! DEBUG! DEBUG! http://www.cpearson.com/Excel/DebuggingVBA.aspx
Go to the top of the page
 
GroverParkGeorge
post Jun 11 2019, 06:22 PM
Post#5


UA Admin
Posts: 35,306
Joined: 20-June 02
From: Newcastle, WA


I use tempvars a lot as criteria in queries for a couple of reasons. For one thing, they work whether or not the the form where the values are set is open or not. That can be handy.

I also like the fact that you can set the SQL using the TempVar(s) specified for the criteria and not have to rewrite it dynamically. Resetting the TempVar(s) value(s) in VBA is all you need to do.

That said, I agree you shouldn't try to pass the operators as part a single TempVar. I might try to do it with two TempVars, one for the actual value and the other for the appropriate operator.

See if that can be worked out. I'm not sure, haven't tried it, but I have done something similar a while back in VBA, in this demo from my website.

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
Dwellable
post Jun 11 2019, 06:25 PM
Post#6



Posts: 5
Joined: 9-April 19



I get that <> 3 would not work for that reason... similarly, not 3 is no good cause R1Table = Not 3 isn't Not(R1Table)=3... back to the drawing board. Thanks for the lesson.
Go to the top of the page
 
RJD
post Jun 11 2019, 06:27 PM
Post#7


UtterAccess VIP
Posts: 9,731
Joined: 25-October 10
From: Gulf South USA


Hi: Or you could get three tempvars this way ...

CODE
Private Sub QueryWithCriteria_Click()
TempVars!Shift1 = IIf(Me!Shift1, 1, 0)
TempVars!Shift2 = IIf(Me!Shift2, 2, 0)
TempVars!Shift3 = IIf(Me!Shift3, 3, 0)
DoCmd.OpenQuery "qryResults"
End Sub

... and then write the query like this ...

SELECT R1Table.ID, R1Table.sshift
FROM R1Table
WHERE (((R1Table.sshift)=[Tempvars]![shift1] Or (R1Table.sshift)=[tempvars]![shift2] Or (R1Table.sshift)=[tempvars]![shift3]));

Simplifies the tempvar code and only slightly complicates the query.

See the demo attached.

Or you could probably bypass the tempvars entirely and simply refer to the form yes/no controls. I haven't done that yet, but will play around with it soon.

HTH
Joe
Attached File(s)
Attached File  VariableAsQueryCriteria.zip ( 22.58K )Number of downloads: 0
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
RJD
post Jun 11 2019, 06:41 PM
Post#8


UtterAccess VIP
Posts: 9,731
Joined: 25-October 10
From: Gulf South USA


Hi again: Here's the method NOT using tempvars, just referring to the form controls directly...

CODE
SELECT R1Table.ID, R1Table.sshift
FROM R1Table
WHERE (((R1Table.sshift)=IIf([forms]![frmSelections]![Shift1],1,0) Or (R1Table.sshift)=IIf([forms]![frmSelections]![Shift2],2,0) Or (R1Table.sshift)=IIf([forms]![frmSelections]![Shift3],3,0)));

See the revised demo attached. See if this fits with what you are trying to do. As George mentioned above, tempvars are handy if the form is not open when the query runs, but not needed if the form is indeed open. Now you have two choices ...

HTH
Joe
Attached File(s)
Attached File  VariableAsQueryCriteria_Rev1.zip ( 22.85K )Number of downloads: 1
 

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 
Dwellable
post Jun 11 2019, 07:37 PM
Post#9



Posts: 5
Joined: 9-April 19



That's pretty slick RJD. Thanks for the input.
Go to the top of the page
 
RJD
post Jun 11 2019, 08:15 PM
Post#10


UtterAccess VIP
Posts: 9,731
Joined: 25-October 10
From: Gulf South USA


You are very welcome. We are all always happy to assist.

I hope you are able to successfully incorporate one of these approaches into you database.

Let us know if we can further assist.

Regards,
Joe

--------------------
"Each problem that I solved became a rule, which served afterwards to solve other problems."
"You just keep pushing. You just keep pushing. I made every mistake that could be made. But I just kept pushing."

Rene Descartes 1596-1650 (Mathematician and Philosopher)
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    18th July 2019 - 02:26 AM