question about using strFilter with AND

Access 2007 database and using a form to filter records and import the 
results into a report.

There are 4 list boxes:
lstpayment
lststore
lstkeyword
lstbuyer

I can get what I want to work one at a time i.e.
strFilter = "[store_name] = '" & Me!lststore & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter

Works fine for the storename filter.  But if I try to use an AND statement I 
get a type mismatch error

strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '" 
& Me!lstpayment & "'"
DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
Anyone have any ideas?

0
Utf
12/3/2009 8:24:02 PM
access.modulesdaovba 1670 articles. 0 followers. Follow

3 Replies
673 Views

Similar Articles

[PageSpeed] 36

Your bracketing appear off, try:

strFilter = "[store_name] = '" & Me!lststore & "' AND [payment_type] = '" & 
Me!lstpayment & "'"
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joe S." wrote:

> Access 2007 database and using a form to filter records and import the 
> results into a report.
> 
> There are 4 list boxes:
> lstpayment
> lststore
> lstkeyword
> lstbuyer
> 
> I can get what I want to work one at a time i.e.
> strFilter = "[store_name] = '" & Me!lststore & "'"
> DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
> 
> Works fine for the storename filter.  But if I try to use an AND statement I 
> get a type mismatch error
> 
> strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '" 
> & Me!lstpayment & "'"
> DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
> Anyone have any ideas?
> 
0
Utf
12/3/2009 9:06:02 PM
Daniel - Thanks for your reply :D

I was just informed that i was getting too excited about quotation marks, 
the correct syntax is

 strFilter = "[store_name] = '" & Me!lststore & "' And [payment_type] = '" & 
Me!lstpayment & "'"


One thing i just realized is, if a person doesn't want to include one of the 
list boxes it will never return any results. (i.e. they only want to know 
payment type and store name, but not buyer or keyword).

What syntax would i use to specify if nothing is selected, to not filter 
based off that list box?


"Daniel Pineault" wrote:

> Your bracketing appear off, try:
> 
> strFilter = "[store_name] = '" & Me!lststore & "' AND [payment_type] = '" & 
> Me!lstpayment & "'"
> -- 
> Hope this helps,
> 
> Daniel Pineault
> http://www.cardaconsultants.com/
> For Access Tips and Examples: http://www.devhut.net
> Please rate this post using the vote buttons if it was helpful.
> 
> 
> 
> "Joe S." wrote:
> 
> > Access 2007 database and using a form to filter records and import the 
> > results into a report.
> > 
> > There are 4 list boxes:
> > lstpayment
> > lststore
> > lstkeyword
> > lstbuyer
> > 
> > I can get what I want to work one at a time i.e.
> > strFilter = "[store_name] = '" & Me!lststore & "'"
> > DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
> > 
> > Works fine for the storename filter.  But if I try to use an AND statement I 
> > get a type mismatch error
> > 
> > strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '" 
> > & Me!lstpayment & "'"
> > DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
> > Anyone have any ideas?
> > 
0
Utf
12/3/2009 9:29:01 PM
You would have to build you string item by item.  Something along the lines of:

if isnull(Me!lststore)=false then
   strFilter = strFilter  & "[store_name] = '" & Me!lststore & "' AND "
end if
if isnull(Me!lstpayment )=false then
   strFilter = strFilter  & "[payment_type] = '" & Me!lstpayment & "' AND"
end if

And so for for each lst

Then finally trim of the last AND

strFilter = left(strFilter, len(strFilter)-5)

and then open using your code
-- 
Hope this helps,

Daniel Pineault
http://www.cardaconsultants.com/
For Access Tips and Examples: http://www.devhut.net
Please rate this post using the vote buttons if it was helpful.



"Joe S." wrote:

> Daniel - Thanks for your reply :D
> 
> I was just informed that i was getting too excited about quotation marks, 
> the correct syntax is
> 
>  strFilter = "[store_name] = '" & Me!lststore & "' And [payment_type] = '" & 
> Me!lstpayment & "'"
> 
> 
> One thing i just realized is, if a person doesn't want to include one of the 
> list boxes it will never return any results. (i.e. they only want to know 
> payment type and store name, but not buyer or keyword).
> 
> What syntax would i use to specify if nothing is selected, to not filter 
> based off that list box?
> 
> 
> "Daniel Pineault" wrote:
> 
> > Your bracketing appear off, try:
> > 
> > strFilter = "[store_name] = '" & Me!lststore & "' AND [payment_type] = '" & 
> > Me!lstpayment & "'"
> > -- 
> > Hope this helps,
> > 
> > Daniel Pineault
> > http://www.cardaconsultants.com/
> > For Access Tips and Examples: http://www.devhut.net
> > Please rate this post using the vote buttons if it was helpful.
> > 
> > 
> > 
> > "Joe S." wrote:
> > 
> > > Access 2007 database and using a form to filter records and import the 
> > > results into a report.
> > > 
> > > There are 4 list boxes:
> > > lstpayment
> > > lststore
> > > lstkeyword
> > > lstbuyer
> > > 
> > > I can get what I want to work one at a time i.e.
> > > strFilter = "[store_name] = '" & Me!lststore & "'"
> > > DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
> > > 
> > > Works fine for the storename filter.  But if I try to use an AND statement I 
> > > get a type mismatch error
> > > 
> > > strFilter = "[store_name] = '" & Me!lststore & "'" And "[payment_type] = '" 
> > > & Me!lstpayment & "'"
> > > DoCmd.OpenReport "rptnopic", acViewPreview, , strFilter
> > > Anyone have any ideas?
> > > 
0
Utf
12/3/2009 10:24:05 PM
Reply:

Similar Artilces:

Sorting a column by using formula #3
I am trying to use sort function just to delete blank cells in between Sort order doesn't matter actually. Data is coming by the use of simple cell reference of "another sheet -- Prais ----------------------------------------------------------------------- Praise's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1558 View this thread: http://www.excelforum.com/showthread.php?threadid=27144 Hi you may use the following addin to filter out blank cells: http://www.rondebruin.nl/easyfilter.htm -- Regards Frank Kabel Frankfurt, Germany Praise wrote: &g...

Using Function to call Woorbook Sheet
I would like to use WEEKDAY() to call a particular Sheet in a Wookbook. The Sheets are called Sunday - Saturday and I have an IF statement that needs to check for particular information on that sheet depending on what day it is. Have it currently functioning but it is taking up way to much space. I am unable to update my code with other functions that I need it to calculate until I can figure this part out. Code =IF(OR(AND((TEXT(TODAY(),"dddd")="Monday"),(HLOOKUP(MOD(NOW(),1),Monday!$B$1:$BA$40,2))>0),AND((TEXT(TODAY(),"dddd")="Tuesday"),(HLOOKU...

use a time value in a bar graph
i have values in a series like c5 9:01 d5 21:01 e5 =(d5-c5) I would like to use e5 in a bar graph it gives values which would make sense only to excel If the axis is formatted as time, it shouldn't be a problem. If it doesn't choose the scales to give clean divisions, you can choose an appropriate unit on the axis, such as 03:00. -- David Biddulph "pdfrone" <pdfrone@discussions.microsoft.com> wrote in message news:A8C14E9F-104A-464A-81B6-39DA2C679E71@microsoft.com... >i have values in a series > > like > > c5 9:01 > d5 21:01 > e5 =(d5-c5...

Problem using Microsoft Web Browser control
I am trying to access the Custom properties of a Web Browser ActiveX control on a form and I keep getting a message telling me that "The Operation on the Microsoft Web Browser object failed. The OLE server may not be registered. To register the OLE server, reinstall it." I searched TechNet and MSDN and could not resolve this. I reinstalled Access 2003 (I have 2003 and 2007 installed on my workstation, XP Pro) and still no resolution. Has anyone else seen this and if so, can you tell me how to resolve this? Jim Does anyone know the name of the file the message...

Exchange question
I currently have Exchange 2000 on Windows 2000. I am getting ready to setup a new box with Exchange 2003 Ent. on Windows Server 2003. Here are my questions: 1) Does my Exchange server have to be in Native mode before I can upgrade? 2) If I change it to Native mode, will I need to reboot my server? 3) Do I need to reboot the server after I run the Forest and Domain Prep? Any help will be greatly appreciated. Thanks Kit It's been *a while* since I upgraded to Ex2003, but I believe the answers would be: No No No ------------------------ Chris Williams Sirana Software www.sir...

Question about Date validation
Hi All, I'm working on a form for weekly data entry. I'd like to create a date field where the user will enter the date the week ends (ie week ending Friday, June 25th). Is there a way to write code so that Access will check that the day the user inputs is a Friday (and if not they will recieve an error message)?. I know how to program the error message pop-up box, but I have no idea how to write code to check if the date inputted is a Friday. Any help would be greatly appreciated! Thanks Use the WeekDay function If WeekDay(Me.DateField) = 6 Then MsgBox "Error" En...

Changing my functions to use subtotals?
I have 2 functions that are calculating my data correctly, except now I need to consider the autofilter. I figure using subtotal is the way to go if I want to disregard filtered out rows. Also, I plan on moving my data to another sheet, so it will need to reference the current sheet, which is AAT_Raw_Data. Finally, the # of rows of data will vary, but I don't think it will ever be over 10,000. I'm using Excel 2003, so I think there's an issue with using something like "A:A" in my formulas. Can someone help me change these to add in a subtotal and take into...

How to Populate .msg File Used in Command Line
I have a .msg file I saved as a boilerplate for Calendar requests that will be programmatically started in an application I am developing. I want to be able to modify the Calendar request's date, time, duration, and message contents based on the user's inputs prior to opening the .msg file. After the user verifies the contents, he/she will click the <Send> button. Currently, from the command line, I can run "c:\program files\microsoft office\office12\outlook.exe" /f "c:\p_review\PR.msg" The Calendar request opens with the boilerplate. Ho...

a question about XmlTextReader.ReadBase64()
Hi, I am new to xml, so if I ask something stupid or funny, pls don't laugh. my problem is like this: I have a data structure like this class mydata { byte[] data; }; // I wrote it to xml like this XmlTextureWriter writer; writer.WriteStartElement("MyData"); writer.WriteAttributeString("Length",md.data.Length.ToString()); writer.WriteBase64(md.data,0,md.data.Length); writer.WriteEndElement(); // I want to read it out XmlTextReader reader; while(reader.Read()) { switch(reader.NodeType) { case XmlNodeType.Element: MyData md = new MyData(); while(reader.M...

cleare space using shrink
Hello there I have server which has not enough disk space. I have removed an unessesery data from my databases which is approx 50% of the datbase which is 20 GB As i know i need at least 20 GB on the HDisk to run the shrink (which i don't have, at least 10 GB). how can i free space in this case? ...

CRM3.0 Question on Activity
Hello, I am using CRM3.0. I have the following case. 1. Create Campaign 2. Add contact to campaign list 3. Create Campaign activity - Phone call 4. Distribute activity 5. CRM User will make phone call 6. When contact is interest, CRM user will make a "Free Trail" for contact. I am having troble in step 6. So far as I know, I have two choices for "Free Trail". A) Service Activity If I choose to create a service activity, then I can not link up the service activity with the campaign or phone call. B) Appointment If I choose appointment, I can set "regarding"...

Pvt Table Question
I am creating several pvt tables that will be updated often with new data. I want to know if there is any function within a pvt table that I can use to set up my pvt table so that only the top 10 data points are automatically displayed. So for example, if i have pvt table set up to show brand name and then accounts for each brand name and I sorted on accounts descending and only wanted the pvt table to show the top 10 brands that had the highest total accounts. Is this possible? Thanks. Hi, To show the top 10 values for a field - double-click the field heading, click 'Advanced' ...

Create Assembly Call
hi All, can the create assembly call under update entity be used to add a certain number of hours/days/months/years to a date. i am trying to do this workflow if variable 1 = yes then create a task update variable 1 sent = yes variable 1 date = execution time can i use the create assembly workflow to add x number of days to variable 1 date and show in variable 2 date. any advice would be helpful please. thanks heaps regards Ridhima If Variable 1 Date is a DateTime field in CRM, then yes. You can use the Add DateTime assembly to take the Value in the DateTime field (which would be t...

Using skins in a multiline edit box
Hi, I have a dialog based application, in which I have a multi-line edit control. In the edit control, I would like to have a background image, say some .bmp file. Some status messages are to be displayed in the edit control, based on certain user actions in the dialog. How do I go about this? Thanks, Sucharit you can do it, i think, if you were to create a class which inherited from the edit control, then in the override the OnDraw()/OnPaint() event, where you first get the rect, and draw your bitmap resource, then call the parent event method to do the rest... I don't know if t...

how to use a saved master page
I have created a new master page and saved it as a .pub file. The question is when opening a new document, how do I apply the saved master file? Open the master file, copy, paste to your new publication. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Colin" <Colin @discussions.microsoft.com> wrote in message news:A3C59647-7D56-46B9-AA56-C707538ECDED@microsoft.com... >I have created a new master page and saved it as a .pub file. The question > is when opening a new document, how do I apply the saved mas...

How to check if mouse wheel has been used, system-wide?
Hi! I have the following task - I need to check time to time if mouse wheel has been used by the user, in ANY application. I have some kind of user activity detector, to do certain actions in application when user is idle for some time. It would be nice to have the ability to check if mouse wheel has been used. I already know how to check for keyboard (GetKeyboardState and compare with previous call's result) and mouse (GetCursorPos and compare with previous call's result). I know how to set up a hook, and can achieve what I need via hook. But, customer says sometimes system runs slo...

Setup Outlook 2003 to reply using Quotes
I have read some posts already but I am not clear on the quoting/replying method. I have set outlook 2003 as follows: Tools/options/mail - preferences tab - mail options, and then set to prefix with a > for replys and forwards. I get a whole bunch of > on some messages and mostly blue lines on the side for the rest, so I can't do the proper quoting. How is this supposed to be setup? Each time you reply should I cut and paste the text I don't want in the message or is there something that helps do that? If you can reply to this message and use the quoting method as the other g...

Using XSD stored in assembly resource with includes
I am trying to validate an XML file against an XSD that is stored in the assembly as an embedded resource. I can get it to work as long as the XSD does not include other XSDs. After a fair amount of searching, I have found 3 possible solutions but none have worked for me. They are: 1. Use the Includes property of XmlSchema to add included XSDs, then call Compile XmlSchema mainSchema = XmlSchema.Read( stream1, null ); XmlSchema includeSchema1 = XmlSchema.Read( stream2, null ); XmlSchema includeSchema2 = XmlSchema.Read( stream3, null ); mainSchema.Includes.Add( includeSchema1 ...

Copy and Paste question
I have a macro that copies and pastes a large amount of data. After it has run I get the question: " There is a large amount of information on the clipboard. Do you want to [keep it]?" Could someone please tell me how I can avoid this question being asked as I never wish to keep the data? Many thanks Insert this line after the pasting is done, it will clear the clipboard: Application.CutCopyMode = False hth knut egil "Richard" <rgarwell@jaguar.invalid> skrev i melding news:bpi1ee$cf71@eccws12.dearborn.ford.com... > I have a macro that copies and pastes a l...

Question about frontend backend config
We have two E2K3 boxes, both with E2K3/sp1 running on W2K3/sp1. At first we just had one, but were continually running out of disk space (Ent. vers.), so I re-imaged a system with a great deal more space and installed E2K3/sp1 on it, and moved some, but not all of the mailboxes on it. My plans are to use the old server (svr1) as a FE, then use the new one(svr2) as a BE. I have not checked the FE box on svr1 yet. Also, we have configured OWA to run on svr1, and it worked until I started moving mailboxes. Here are my questions: - I have several Mac clients using Entourage, configured to co...

Formula question 03-03-10
I am trying to complete the following. In s141 i have a drop list with 'yes' and 'no' as possiblities. In x141 i have a drop list with 3 possible choices, .02 , .04 , or .08 g141 is the originating cell p159 is the answer cell. if s141 remains empty or has 'no' chosen from the drop list i need p159 to remain blank, however if 'yes' is in s141 then i need the answer from g141 multiplied by .02 or .04 or .08 (which ever one is chosen from the drop list in x141) to be displayed in p159. Can anybody help? Thanks in advance. Scoob...

Using Code instead of Criteria
In my query builder, I'm getting the message: "The string returned by the builder is too long. The result will be truncated." I think I understand why, as I am using a form to specify a large number of options for the report. Can I use VB to specify these criteria instead of the query builder? Examples in query builder: If [Forms]![boxSeatsMailings]![PW] Criteria =1 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. If [Forms]![boxSeatsMailings]![PW] Criteria =2 and (PFDirect>0 or PFBulk>0 or PIDirect>0) then select record. As ind...

Can I use 'or' in conditional formatting?
I'd like to have 4 conditions, Red, Yellow, and Green. But I have two conditions for green, without using two conditions for green is there a way to use OR somehow to beat the limit? Thanks, Norm PS Win2000 + XL2002 Sure ... just click on "Formula Is", and you can use "OR" for more then two conditions. For example: =OR(A1=2,A1=4,A1="good",A1="bad") Will trigger the set format if "any" of the above equate to "True". -- HTH, RD --------------------------------------------------------------------------- Please keep all corr...

Newb question: How did my Form_.... Access Class Objects get crea
I have an access database that originally was created in office 2003 access. I am not in 2007 Access. When I go into the IDE, I can see, under Microsoft Office Access Class Objects, a set of what appears to be modules named: - Form_form abc - Form_form xyz etc Each of these corresponds to an actual form I have created, and includes subroutines like Private Sub RefreshAssetTable() ImportExcel End Sub Private Sub Command4_Click() ImportExcelAssets End Sub by default. Now, my question: How did this class "modules" (and I know that is the wrong term) get created? I &...

New to Visio-Hyperlink Question
I have created a flowchart in Visio 2000. I put in a hyperlink to another flowchart. When I view my flowchart in full-screen view I click on the hyperlink for the other flowchart and it opens in Actual Size mode w/ all the toolbars open. I would like for it to remain in full-screen mode for the flowchart I open when I click on the hyperlink. Is this possible? Thanks in Advance, Mike ...