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
> Filter Multiple Criteria, Access 2007    
 
   
ielviro@gomacamp...
post Aug 23 2019, 04:01 AM
Post#1



Posts: 11
Joined: 20-August 19



Good morning

I am trying to filter one form by multiple criteria:

Rp_Data is a date.

Rp_Line is a text

Rp_Shift is a number

My expression doesn´t work:

.Filter = "[RP_Data] = #" & Me.[Rp_Data] & "#" And "[RP_Line] = '" & Me.[Rp_Line] & "'" And [Rp_Turno] = " & Me.[Rp_Turno] "

The mistake is "Run-time error '13' Type mismatch"

Could somebody help me?

Thanks in advance
Go to the top of the page
 
dale.fye
post Aug 23 2019, 04:51 AM
Post#2



Posts: 161
Joined: 28-March 18
From: Virginia


I always like to create these criteria on a line of their own, creating a variable, so that I can print it out to see what it is:
CODE
Dim strFilter as string

strFilter = "[RP_Data] = #" & Me.[Rp_Data] & "#" And "[RP_Line] = '" & Me.[Rp_Line] & "'" And [Rp_Turno] = " & Me.[Rp_Turno]"
.Filter = strFilter
In this case, you have two extra quotes, one before the last AND and the other at the end of the string.
It should look like:
CODE
strFilter = "[RP_Data] = #" & Me.[Rp_Data] & "#" And "[RP_Line] = '" & Me.[Rp_Line] & "' And [Rp_Turno] = " & Me.[Rp_Turno]
.Filter = strFilter



--------------------
Dale Fye
Microsoft Access MVP 2013-2016
Developing Solutions, LLC
Go to the top of the page
 
ielviro@gomacamp...
post Aug 23 2019, 05:43 AM
Post#3



Posts: 11
Joined: 20-August 19



Hi again;

Sorry:

Rp_Data is a date.

Rp_Line is a text

Rp_Turno is a text too.

Could you send me again with this variant?

Thanks in advance.



Go to the top of the page
 
RJD
post Aug 23 2019, 08:14 AM
Post#4


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


Hi: Pardon me for jumping in, but I would have removed a couple double-quotes around the first And in Dale's solution, as well as modifying the coding for [Rp_Turno] per your data type correction...

Old:
strFilter = "[RP_Data] = #" & Me.[Rp_Data] & "#" And "[RP_Line] = '" & Me.[Rp_Line] & "' And [Rp_Turno] = " & Me.[Rp_Turno]

New:
strFilter = "[RP_Data] = #" & Me.[Rp_Data] & "# And [RP_Line] = '" & Me.[Rp_Line] & "' And [Rp_Turno] = '" & Me.[Rp_Turno] & "'"

Try this and see if it works better ... or if we need to look at this more ...

HTH
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
 
ielviro@gomacamp...
post Aug 24 2019, 01:24 AM
Post#5



Posts: 11
Joined: 20-August 19



Hi again;

The example gives the same problem.

"Run-time error '13' Type mismatch"

I think the problem is the RP_Date field, because alone the rest of fields works correctly.

If it is a problem of format, I can not change in the original tables, becuase are of other database, and I haven´t got control of them.

I attach the example in order to simplify the answers.

Could you work in it?

Thanks in advance
Attached File(s)
Attached File  Archivos.zip ( 67.46K )Number of downloads: 5
 
Go to the top of the page
 
isladogs
post Aug 24 2019, 02:31 AM
Post#6


UtterAccess VIP
Posts: 1,687
Joined: 4-June 18
From: Somerset, UK


Answering on my tablet so not a full answer.
Your code does seem to be a convoluted method of opening another form filtered to a particular record.
Anyway, as your dates are in dd/mm/yyyy format, you need to reformat as mm/dd/yyyy for your SQL string

The code then throws up error 3709 - search key not found...
As I'm just going out I've no time to investigate further and have done a temporary work round.
You need to fix it properly or use a different approach

CODE
Private Sub Purchases_Click()
Dim frmPurchases As Form
Static intCtr As Integer, strFilter As String

next line added to temporarily bypass error 3709 - search key not found in any record.
'However, you need to identify and fix the cause
On Error Resume Next

intCtr = intCtr + 1
    
Set frmPurchases = New Form_frmClientpurchases

With frmPurchases
  .Visible = True
  .Caption = frmPurchases.Hwnd & ", opened " & Now()
  strFilter = "[RP_Data] = #" & Format(Me.[RP_DATA], "mm/dd/yyyy") & "# And [RP_Line] = '" & Me.[Rp_Line] & "' And [Rp_Turno] = '" & Me.[RP_TURNO] & "'"
  Debug.Print strFilter
  
  .Filter = strFilter
  .FilterOn = True
  .Move 0, intCtr * 500
End With
    
'Append it to our collection.
clnClient.Add Item:=frmPurchases, Key:=CStr(frmPurchases.Hwnd)
Set frmPurchases = Nothing
End Sub

--------------------
Go to the top of the page
 
RJD
post Aug 24 2019, 05:24 AM
Post#7


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


Hi: Seems like the field Rp_Turno is actually numeric, and thus the filter should look like this ...

.Filter = "[RP_Data] = #" & Me.[RP_DATA] & "# And [RP_Line] = '" & Me.[RP_LINE] & "' And [Rp_Turno] = " & Me.[RP_TURNO]

Take a look at the revised db attached. It seems to work okay now, with this revision. Or am I missing something ...

HTH
Joe
Attached File(s)
Attached File  ClientMultiSample_Rev2.zip ( 62.55K )Number of downloads: 4
 

--------------------
"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
 
isladogs
post Aug 24 2019, 08:53 AM
Post#8


UtterAccess VIP
Posts: 1,687
Joined: 4-June 18
From: Somerset, UK


QUOTE
Or am I missing something....


No...but I did.
RP_Turno is in both tables. Text field in tblClients and number field in tblClientPurchasing.
I modified my code accordingly to remove text delimiters and the On Error Resume Next line.,
As you say, it now runs without error.
Still think its convoluted though....!

--------------------
Go to the top of the page
 
RJD
post Aug 24 2019, 10:16 AM
Post#9


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


Hi Colin:

QUOTE
RP_Turno is in both tables. Text field in tblClients and number field in tblClientPurchasing.

Right. That threw me off at first as well, until I looked more closely.

QUOTE
Still think its convoluted though....!

Uh, me, too. Though I didn't bother to explore this any further ...

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    16th October 2019 - 07:23 PM