Simple wildcard parameter on report control

I have searched the database but cannot find a solution to this problem.  I 
have written a text box control on an access report as follows:

=IIf([CaseNo] Like "A-*",
DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR Prelim'"),"None")

Some of our cases begin with *A-* and others with *C-*.  This expression 
results in all the 'AR Prelim' records are counted regardless of what they 
begin with.  
0
Utf
3/19/2008 8:41:00 PM
access 16762 articles. 3 followers. Follow

5 Replies
700 Views

Similar Articles

[PageSpeed] 49

I've also tried:

=IIf("[CaseNo] 
='A-*'",DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR 
Prelim'"),"None")


"PeteyP" wrote:

> I have searched the database but cannot find a solution to this problem.  I 
> have written a text box control on an access report as follows:
> 
> =IIf([CaseNo] Like "A-*",
> DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR Prelim'"),"None")
> 
> Some of our cases begin with *A-* and others with *C-*.  This expression 
> results in all the 'AR Prelim' records are counted regardless of what they 
> begin with.  
0
Utf
3/19/2008 8:50:02 PM
"PeteyP" <PeteyP@discussions.microsoft.com> wrote in message 
news:F0B20DB3-84FC-4478-89C3-31716C70C7C0@microsoft.com...
>I have searched the database but cannot find a solution to this problem.  I
> have written a text box control on an access report as follows:
>
> =IIf([CaseNo] Like "A-*",
> DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR 
> Prelim'"),"None")
>
> Some of our cases begin with *A-* and others with *C-*.  This expression
> results in all the 'AR Prelim' records are counted regardless of what they
> begin with.


Are you trying to find all the 'AR Prelim' records for the current [CaseNo], 
but only if that [CaseNo] begins with "A-"?  If so, you need to include the 
[CaseNo] as a criterion in your DCount expression;  for example,

    =IIf([CaseNo] Like "A-*",
            DCount("*","QuarterlyCompilationStart",
                "[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
            "None")

I broke that onto multiple lines for clearer posting, but it really would 
all be on one line in the text box's ControlSource proeprty.

On the other hand, if you just want a count of all 'AR Prelim' records that 
begin with "A-", you could use a controlsource expression like this:

    =DCount("*","QuarterlyCompilationStart",
                "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")

or maybe this:

    =IIf(DCount("*","QuarterlyCompilationStart",
                "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
            > 0,
          DCount("*","QuarterlyCompilationStart",
                "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
          "None")

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

0
Dirk
3/19/2008 9:11:58 PM
Thanks, Dirk, but I'm only trying to count those where [Determ] are "AR 
Prelim" AND where [CaseNo] begins with "A".   So I modified your effort to 
read:

=IIf([CaseNo] Like "A*",
         DCount("*","QuarterlyCompilationStart","[Determ]='AR 
Prelim'"),"None")

This counts all the AR Prelim records regardless whether they start with A 
or C.

When I change the expression to select records starting with C, it returns 
None! 

Thanks again.




"Dirk Goldgar" wrote:

> "PeteyP" <PeteyP@discussions.microsoft.com> wrote in message 
> news:F0B20DB3-84FC-4478-89C3-31716C70C7C0@microsoft.com...
> >I have searched the database but cannot find a solution to this problem.  I
> > have written a text box control on an access report as follows:
> >
> > =IIf([CaseNo] Like "A-*",
> > DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR 
> > Prelim'"),"None")
> >
> > Some of our cases begin with *A-* and others with *C-*.  This expression
> > results in all the 'AR Prelim' records are counted regardless of what they
> > begin with.
> 
> 
> Are you trying to find all the 'AR Prelim' records for the current [CaseNo], 
> but only if that [CaseNo] begins with "A-"?  If so, you need to include the 
> [CaseNo] as a criterion in your DCount expression;  for example,
> 
>     =IIf([CaseNo] Like "A-*",
>             DCount("*","QuarterlyCompilationStart",
>                 "[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
>             "None")
> 
> I broke that onto multiple lines for clearer posting, but it really would 
> all be on one line in the text box's ControlSource proeprty.
> 
> On the other hand, if you just want a count of all 'AR Prelim' records that 
> begin with "A-", you could use a controlsource expression like this:
> 
>     =DCount("*","QuarterlyCompilationStart",
>                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
> 
> or maybe this:
> 
>     =IIf(DCount("*","QuarterlyCompilationStart",
>                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
>             > 0,
>           DCount("*","QuarterlyCompilationStart",
>                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
>           "None")
> 
> -- 
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
> 
> (please reply to the newsgroup)
> 
0
Utf
3/20/2008 1:22:01 PM
Some sample informaiton may help

Field [CaseNo] always starts with 'A' or 'C'
Sample data:  A-533-833
Sample data:  C-570-326

Field [Determ]
Sample data:  AR Prelim

I want to do a DCount on a table where [Determ]='AR Prelim' AND [CaseNo]='A-*'
 

"PeteyP" wrote:

> Thanks, Dirk, but I'm only trying to count those where [Determ] are "AR 
> Prelim" AND where [CaseNo] begins with "A".   So I modified your effort to 
> read:
> 
> =IIf([CaseNo] Like "A*",
>          DCount("*","QuarterlyCompilationStart","[Determ]='AR 
> Prelim'"),"None")
> 
> This counts all the AR Prelim records regardless whether they start with A 
> or C.
> 
> When I change the expression to select records starting with C, it returns 
> None! 
> 
> Thanks again.
> 
> 
> 
> 
> "Dirk Goldgar" wrote:
> 
> > "PeteyP" <PeteyP@discussions.microsoft.com> wrote in message 
> > news:F0B20DB3-84FC-4478-89C3-31716C70C7C0@microsoft.com...
> > >I have searched the database but cannot find a solution to this problem.  I
> > > have written a text box control on an access report as follows:
> > >
> > > =IIf([CaseNo] Like "A-*",
> > > DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR 
> > > Prelim'"),"None")
> > >
> > > Some of our cases begin with *A-* and others with *C-*.  This expression
> > > results in all the 'AR Prelim' records are counted regardless of what they
> > > begin with.
> > 
> > 
> > Are you trying to find all the 'AR Prelim' records for the current [CaseNo], 
> > but only if that [CaseNo] begins with "A-"?  If so, you need to include the 
> > [CaseNo] as a criterion in your DCount expression;  for example,
> > 
> >     =IIf([CaseNo] Like "A-*",
> >             DCount("*","QuarterlyCompilationStart",
> >                 "[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
> >             "None")
> > 
> > I broke that onto multiple lines for clearer posting, but it really would 
> > all be on one line in the text box's ControlSource proeprty.
> > 
> > On the other hand, if you just want a count of all 'AR Prelim' records that 
> > begin with "A-", you could use a controlsource expression like this:
> > 
> >     =DCount("*","QuarterlyCompilationStart",
> >                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
> > 
> > or maybe this:
> > 
> >     =IIf(DCount("*","QuarterlyCompilationStart",
> >                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
> >             > 0,
> >           DCount("*","QuarterlyCompilationStart",
> >                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
> >           "None")
> > 
> > -- 
> > Dirk Goldgar, MS Access MVP
> > www.datagnostics.com
> > 
> > (please reply to the newsgroup)
> > 
0
Utf
3/20/2008 4:04:01 PM
I got it from studying your reply, Dirk.  Thanks again.  Sorry for all the 
posts!

"PeteyP" wrote:

> Some sample informaiton may help
> 
> Field [CaseNo] always starts with 'A' or 'C'
> Sample data:  A-533-833
> Sample data:  C-570-326
> 
> Field [Determ]
> Sample data:  AR Prelim
> 
> I want to do a DCount on a table where [Determ]='AR Prelim' AND [CaseNo]='A-*'
>  
> 
> "PeteyP" wrote:
> 
> > Thanks, Dirk, but I'm only trying to count those where [Determ] are "AR 
> > Prelim" AND where [CaseNo] begins with "A".   So I modified your effort to 
> > read:
> > 
> > =IIf([CaseNo] Like "A*",
> >          DCount("*","QuarterlyCompilationStart","[Determ]='AR 
> > Prelim'"),"None")
> > 
> > This counts all the AR Prelim records regardless whether they start with A 
> > or C.
> > 
> > When I change the expression to select records starting with C, it returns 
> > None! 
> > 
> > Thanks again.
> > 
> > 
> > 
> > 
> > "Dirk Goldgar" wrote:
> > 
> > > "PeteyP" <PeteyP@discussions.microsoft.com> wrote in message 
> > > news:F0B20DB3-84FC-4478-89C3-31716C70C7C0@microsoft.com...
> > > >I have searched the database but cannot find a solution to this problem.  I
> > > > have written a text box control on an access report as follows:
> > > >
> > > > =IIf([CaseNo] Like "A-*",
> > > > DCount("[CaseNo]","QuarterlyCompilationStart","[Determ]='AR 
> > > > Prelim'"),"None")
> > > >
> > > > Some of our cases begin with *A-* and others with *C-*.  This expression
> > > > results in all the 'AR Prelim' records are counted regardless of what they
> > > > begin with.
> > > 
> > > 
> > > Are you trying to find all the 'AR Prelim' records for the current [CaseNo], 
> > > but only if that [CaseNo] begins with "A-"?  If so, you need to include the 
> > > [CaseNo] as a criterion in your DCount expression;  for example,
> > > 
> > >     =IIf([CaseNo] Like "A-*",
> > >             DCount("*","QuarterlyCompilationStart",
> > >                 "[CaseNo]='" & [CaseNo] & "' AND [Determ]='AR Prelim'"),
> > >             "None")
> > > 
> > > I broke that onto multiple lines for clearer posting, but it really would 
> > > all be on one line in the text box's ControlSource proeprty.
> > > 
> > > On the other hand, if you just want a count of all 'AR Prelim' records that 
> > > begin with "A-", you could use a controlsource expression like this:
> > > 
> > >     =DCount("*","QuarterlyCompilationStart",
> > >                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
> > > 
> > > or maybe this:
> > > 
> > >     =IIf(DCount("*","QuarterlyCompilationStart",
> > >                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'")
> > >             > 0,
> > >           DCount("*","QuarterlyCompilationStart",
> > >                 "[CaseNo] Like 'A-*' AND [Determ]='AR Prelim'"),
> > >           "None")
> > > 
> > > -- 
> > > Dirk Goldgar, MS Access MVP
> > > www.datagnostics.com
> > > 
> > > (please reply to the newsgroup)
> > > 
0
Utf
3/20/2008 4:11:09 PM
Reply:

Similar Artilces:

Tree View Control state image list
I'm trying to implement a tree view in an MFC wizard property sheet that has check boxes that can be dimmed. In order to do this, I've created an image list that has the check boxes in each of their 4 possible combinations of unchecked/checked and enabled/disabled (as well as a blank image at index 0). However, when I set this image list as the tree view control's state image list, all I see are blank images where the check boxes should be. (I use the correct state indices when I add the various items. I'm using commctl32 version 5.80 and Visual Studio 2005, so according...

Blank page at the end of report (no margins problem)
In an Access report I've created, the last page is always blank (so not every other page, only the last one). This is very strange considering the fact that I've set both page headers and footers and they don't appear on this page. Now I've checked all margins, both "can shrink" and "can expand" options and I really can't seem to find where this problem is coming from. Another strange fact is that in preview mode (and in report preview mode) I don't see this blank page, it's just there when I print the report. Can anyone tell me what I'm m...

Microsoft Money 2006
I am using Microsoft Money 2006 Small Business. For some reason when I do reports and transactions by category I don't see my expenses for a few of my credit cards. It just would not show up in transactions by category. Then I notice that when I left click and change account settings for my credit card, the "Include transaction in account to calculate cash flow and Use this account for budgeted savings goals" has been grayed out. I tried everything but I can't seem to select the other option and ungray it. Thanks for any help you may provide In microsoft.public.money, super...

Exporting reports in PDF format ?
I heard this is possible but I do not see the PDF option in the export menu. Do I need special software to do this or is it built-in to Access? I have Access 2003 and XP. Exporting to PDF is not part of MS Access. If you have Adobe Acrobat (the full version, not just the reader) , it should show as a "Printer" on the File - Print. There are other 3rd party packages that are available to do the same thing - we use PDFCreator, which is installed as a "Printer", and saves any document as a pdf file to any location. John mscertified wrote: >I heard this is possible...

Microsoft Monthview Control 6.0 (SP6)
Hello, I have an own programm, which use Monthview from MSCOMCT2.ocx. I make wrapper-classes for MonthView. My Programm is - compiled with VC2003, - use mfc71 - UNICODE On - Windows XP - Vista I have no problems to create the monthview objects. On - Windows NT the creating of the monthview-objects fails, GetLastError==0. I make a test with a simple test MFC-Programm. ----------------------------------------------------------------------- Windows NT: I have no problems - if I use Multibyte- Programm. But I use Unicode, the objects are not created. I put mfc7u.dll into the Windows-NT d...

Using the IN keyword in an simple CASE statement
Hi, Is it possible to convert this into a simple case statement and retain the use of the IN keyword Case When InvoiceAllocation.SourceType in ('S','C') Then Coalesce(TimeCat.Description+' ','')+Coalesce(TimeSCat.Description,'') When InvoiceAllocation.SourceType in ('F') Then 'Free Item' When InvoiceAllocation.SourceType in ('A') Then 'Adjustment' Else 'Sales Tax' End, As you can see, i had to use a Searched CASE because of the IN keyword in the first WHEN. Without doing a s...

Control Panel Mail Icon not visible Win7 x64
Hello, The mail icon is not visible in control panel for the Outlook 2007 mail icon on Win7 Ultimate x64. How can I restore the icon or run the Mail utility manually? Many thanks ... Andrew Are you looking in the 32 bit area? Andrew_UK wrote: > Hello, > The mail icon is not visible in control panel for the Outlook 2007 mail icon > on Win7 Ultimate x64. How can I restore the icon or run the Mail utility > manually? > > Many thanks ... Andrew Type mail in the start search field of the Start menu or, if you prefer to open control panel, switch t...

Deploying .NET crystal reports on crm 1.2 server
Hi, We developed some reports using Visual Studio.Net 2003 inbuilt Crystal Reports & viewer.(we had to do this because the CRM reports using SDK were not able to talk to our separate custom database) We are able to render these .net crystal reports on our developer machine (non-crm).However, we are not able to render the same when we deploy these .NEt crystal reports to our CRM webserver. (We have CRM 1.2 Installation with Crystal Enterprise 9 on this crm server) I guess that this may be happening because of version conflict between .Net crystal viewer and the CRM enhanced crystal v...

Trial Balance Report Error
I am trying to print a detailed trial balance out of Financial. I am getting the following error message: Error in equation ****ILLEGAL SYNTAX*** found]: Any idea on how to fix this? Thanks Angie Did this error just start? Have there been any recent customizations to the report? Try logging out of GP and back in and see if that resolves the issue. If there haven't been any customizations, it's likely a communications error. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com I have this same issue with one user, unfortunately my direct manager. He ...

Stock Status report with Lot Costs
Hi All I would like the system Stock Status report to print up the Lot Number and the qty in that Lot and the cost of that Lot. I can see that I am able to get my first 2 requests, but the cost is not available. In the past I have only achieved this report by using a Crystal Report but I would like to know if anyone has had success in defining their own join and actually having those costs appear in this report in GP. Any suggestions would be welcomed. I am on GP ver 9 with SP 3 Thankx Sheila ...

Activex Windows Media Player control issue
I have a form with an Activex WMP control, and I wish to have the control play a .wma file when a command button is clicked. I want the .wma to be determined based on the currently displayed record (i.e. the record refers to a song, and I want that song to play). If I hard-code the path to the .wma into the Player.URL field, it works fine, but if I try to use a variable (constructed from fields and constants), it does not. I get an error "The file you are attempting to play has an extension (.wma") that does not match the file format. Playing the file may result in unexp...

parameter list for hooks (RMS)
Can someone please post a list of what the various parameter numbers refer to (in relation to using Com object hooks). Thanks. Search the forum for "hook list". It's been answered a couple of times this week. There is a document available from Customer Source now, which I don't think used to be available as it is clearly labelled "Partner Only Article". CustomerSource, Support, Knowledge Base, Retail Management, Store Operations, Customization, you should fine it. ArticleID is 869720. There is a direct link in the post starting 1) Database Schema. I&...

"inter parameter value"
"inter parameter value" i get prompt "inter parameter value" when i open query . how do i can to prevent it? thanks siahman wrote: > "inter parameter value" > i get prompt "inter parameter value" when i open query . how do i can > to prevent it? thanks Your query is referring to an object that does not exist within its scope when the query runs. A misspelled field name, prehaps? Or a reference to a form control whose form is not open? To prevent it, you have to fix whatever is wrong. You can figure out for yourself what the proble...

How do i enable the Query Parameter menu option on the External D. #2
I am trying to add a query to my worksheet using a cell as a parameter to the query. But the Query Parameter on the External Data menu option is disabled. How can I enable this. ...

Dept Wage and Hour report
In v6.0, the Dept Wage and Hour report should MTD, QTD and YTD columns of information, this was removed in v7.5. I have had many requests from my clients to bring this back. I was told that v9.0 would bring these fields back but it didn't. Please bring the MTD, QTD and YTD fields back to the Department Wage and Hour report. Thanks, Emily ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, foll...

Extra blank line on report
Access 2003... I have a report with a group header and detail lines. The group header has a fixed size, and set to neither grow nor shrink. The bottom-most control in the group header is a horizontal line (to set it apart from the details that follow). The detail contains one row of controls, but one of these is set so it can grow (in case the text doesn't fit on the single line). The detail section itself is set to shrink, but NOT to grow. Some of the controls have "Hide Duplicates" set to yes ... but I don't see what this has to do with the problem. ...

AR Trial Balance Report w/Multicurrency Balances
We have Customer using Canadia currency. When we print an aged trial balance report we have transactions that show up but when you go into Cards-Customer-Summary and drill back the balance is zero on the same transaction. I have tried Routines-Sales-Paid Transaction Removal but this does not work. Can anybody tell me how to get these balances cleared from the report? Are you printing an Aged Trial Balance or an Aged Trial Balance W/Options? Try the Aged Trial Balance W/Options. Be sure the Exclude Fully Paid Documents box is checked. JEK "nancy.kemmer@webasto-us.com" ...

question about ms-flexgrid control
hi all i have a question.when i want to insert a flex-grid control on my dialog it gives me a message about "design-time licence".can any one help me what is it. best regards, amir ...

Crosstab report
Is there a way to create cross tab report? For example sales by category by day of month... category going down the page, days going across the page. If any one has a sample report to attach it would be great. Thank you. hi, Well at the level of active report I don't think so but Crystal report you can create as you like, let m know if you need help in this. "relentless" wrote: > Is there a way to create cross tab report? For example sales by category by > day of month... category going down the page, days going across the page. If > any one has a sample r...

Control format of headers in replies to an email
Lets say I have an email with several people on the TO list. When I receive the email and view it, Outlook shows me the senders name or Exchange identifier and hides the emails address, ie instead of me seeing: sammy.smith@somewhere.com I get 'Sammy Smith' This is generally fine, and if I need the real address, I can right click and get it. However if I reply to that email, or forward it (with forwards set to quote the original email instead of attaching it), in the header section of the quoted email, Outlook will insert the text 'Sammy Smith' instead of the real email ad...

Is It Possible to Control Access Warning Pop-Up Message Boxes?
When running an update query, Access displays a warning message box and prompts for a response ("You are about to run an update query that will modify data in your table"). I have a macro that runs a series of update queries. Right now, I get the message box/response for each query. In this case, there is no need for the message box at all. Is there a way to (ideally) turn off this message for the duration of the query) or to answer it once for all of the queries? Thanks TerryoMSN It is possible to turn the warning off ... WARNING!!! If you forget to turn it bac...

How to use the WebBrowser control to open an Office document
I have recently installed office 2007 on my development box. As a result, Word documents no longer seem to load in the webbrowser control in one of my applications. Instead, Word is launched when I navigate to the document. A stripped down sample of my goal is found here http://support.microsoft.com/kb/243058 Has anyone encountered this or know of a work around for my issue? My development machine is WinXP sp3, running VB6 sp6 Thanks in advance. On Thu, 4 Feb 2010 11:08:41 -0600, "amdrit" <amdrit@hotmail.com> wrote: � I have recently installed o...

Report Footer
Hi, I have a report based on a query. The report has a group header and footer. There is a control in the group footer that does a count for the number of records for each group. When there are no records returned, you get the error message. I could use the On No Data event to cancel the report but the person who uses the report wnats to include the report in his monthly report, showing there were no records. Is it possible to reference the control and make it invisible when there are no records? Also would like to make a label visible stating "There were no matching records....

Control List and exception error
Hello, I'm trying to make a list control accessible from other CPP files in my project. I defined a CListCtrl m_ListCtrl; in the class which is derived from CMyCfgApp derived from CWinApp and use is as ((CMtSysCfgApp*)AfxGetApp())->m_ListCtrl in other CPP file but an exceptiong error comes up. Any comment? I will appreciate it. regards, please ignore this question! it violates the encapoulation rules. thanks, "JSmith" <jsmithmitra@yahoo.com> wrote in message news:u2V8Y9JrEHA.1164@TK2MSFTNGP10.phx.gbl... > Hello, > > I'm trying to make a lis...

Adding controls to SDI and MDI programs
Hi, from the time I begun to learn VC++ and MFC, all the examples in the books I have seen, for the SDI and MDI applications just just draw text or some graphics e.g. circles, etc. Now I want to add a control, say a list box to the view class and dissplay information in it in an SDI application, but I dont know how to do it. I searched on google and codeproject but I did not find anything. Can anyone help me with that? On 2 Apr 2007 10:07:00 -0700, "Shahoo" <shahookamangar@gmail.com> wrote: >Hi, from the time I begun to learn VC++ and MFC, all the examples in >the book...