Criteria for select query.

I have Access2003.

I need a criteria expression to select certain data from a 
query QryLedgerTxns.

When the ledger is reconciled with the bank statement, that 
statement date is manually entered into a QryLedgerTxns 
field called "DateStatement".

The "DateStatement" field matches a ledger transaction field 
called "Withdrawal"

My aim is to add up all the sums in the "Withdrawal" field 
*not*  reconciled with the bank statement.

The "Withdrawal" field is currency.
The "DateStatement" field is Date/time.

Therefore the  criteria for the query should be like:

Exprn = if "DateStatement" = null.

This does not work.  Can someone help me with the correct 
expression?

Frank 


0
Frank
5/8/2010 12:41:41 AM
access.queries 6343 articles. 1 followers. Follow

2 Replies
1260 Views

Similar Articles

[PageSpeed] 19

On Sat, 8 May 2010 10:41:41 +1000, "Frank Martin" <fm@general.com.au> wrote:

>I have Access2003.
>
>I need a criteria expression to select certain data from a 
>query QryLedgerTxns.
>
>When the ledger is reconciled with the bank statement, that 
>statement date is manually entered into a QryLedgerTxns 
>field called "DateStatement".
>
>The "DateStatement" field matches a ledger transaction field 
>called "Withdrawal"
>
>My aim is to add up all the sums in the "Withdrawal" field 
>*not*  reconciled with the bank statement.
>
>The "Withdrawal" field is currency.
>The "DateStatement" field is Date/time.
>
>Therefore the  criteria for the query should be like:
>
>Exprn = if "DateStatement" = null.
>
>This does not work.  Can someone help me with the correct 
>expression?
>
>Frank 
>

Nothing (not even Null) is = to NULL. NULL means "this value is unknown,
unspecified, undefined" - so you cannot assert that NULL is either equal or
unequal to something else.

The proper criterion is 

[DateStatement] IS NULL

In the query grid you'ld just put 

IS NULL

on the Criteria line.
-- 

             John W. Vinson [MVP]
0
John
5/8/2010 1:11:27 AM
"John W. Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote 
in message 
news:8fe9u5p82ocjuq76rk45apv6sfqqq6rpfd@4ax.com...
> On Sat, 8 May 2010 10:41:41 +1000, "Frank Martin" 
> <fm@general.com.au> wrote:
>
>>I have Access2003.
>>
>>I need a criteria expression to select certain data from a
>>query QryLedgerTxns.
>>
>>When the ledger is reconciled with the bank statement, 
>>that
>>statement date is manually entered into a QryLedgerTxns
>>field called "DateStatement".
>>
>>The "DateStatement" field matches a ledger transaction 
>>field
>>called "Withdrawal"
>>
>>My aim is to add up all the sums in the "Withdrawal" field
>>*not*  reconciled with the bank statement.
>>
>>The "Withdrawal" field is currency.
>>The "DateStatement" field is Date/time.
>>
>>Therefore the  criteria for the query should be like:
>>
>>Exprn = if "DateStatement" = null.
>>
>>This does not work.  Can someone help me with the correct
>>expression?
>>
>>Frank
>>
>
> Nothing (not even Null) is = to NULL. NULL means "this 
> value is unknown,
> unspecified, undefined" - so you cannot assert that NULL 
> is either equal or
> unequal to something else.
>
> The proper criterion is
>
> [DateStatement] IS NULL
>
> In the query grid you'ld just put
>
> IS NULL
>
> on the Criteria line.
> -- 
>
>             John W. Vinson [MVP]


Thanks, this works great.
Frank 


0
Frank
5/8/2010 2:53:56 AM
Reply:

Similar Artilces:

VBA to select formula cells OK -but want certain cells with ColorI
Using XL 2003 & 97 The following subroutine works. That said, how can I fine formula cells with a "!" in the cell. (In short, probably a cell formula referring to another sheet) With the following code I would like all formula cells set to colorindex 6 (yellow) but all formula cells with "!" set to a color index of 3 (red) I tried to use an If statement but to no avail. Sub SelectFormulaColor() Selection.SpecialCells(xlCellTypeFormulas, 23).Select With Selection.Interior .ColorIndex = 6 .Pattern = xlSolid End With ActiveWindow.Dis...

Linking queries on a report
Hello all, I built a number of queries that all tie to the same table. Each query sorts the data differently, but comes from the same data source. I would like to show the results of these queries on the same report, but I get and error message that the wizard can't connect the queries. How can I display multiple queries on the same report. I've tried subreports, but they don't seem to export as well. Thanks b Brennan What does "export" have to do with this? If I have multiple sets of results I want to show in one place, I create an empty "main"...

Can I set up a calendar lookup for date selection
Is it possible to lookup a calendar for date selection on a cell. I cannot find any reference to it in the help. try this url http://www.fontstuff.com/vba/vbatut07.htm ================= "Tangoxray" <Tangoxray@discussions.microsoft.com> wrote in message news:72D4DB63-1D7F-477A-93C7-2F767A207B26@microsoft.com... > Is it possible to lookup a calendar for date selection on a cell. I cannot > find any reference to it in the help. ...

min query
Hi, How do I get the 2nd minimum value and the 3rd minimum from a table after I have done MIN query for the 1st minimum value? SELECT dbo_CON.SC, dbo_CON.SN, Min(dbo_CON.SQ) AS MinOfSQ FROM dbo_CON GROUP BY dbo_CON.SC, dbo_CON.SN HAVING (((dbo_CON.SC)<>49)); Thanks, Hi Jerry, This will give me three different SC SN records. I am looking for same SC SN records that has next min SQ. table SC SN SQ 1 100 1 1 100 2 1 100 3 1 100 ...

Error when no records meet criteria
Hi, I have the following code and receive error 1004 Application defined or object defined error on this line Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _ "=AND(RIGHT(D2)<>""r"",B2=4)" Selection.FormatConditions(3).Interior.ColorIndex = 7 because there are no records in this instance of the report for "4" in ColB. I've searched and applied code all to no avail. Can someone please tell me how to write code for when this may occur in any of the situations below? Range("b2:b800").Select ...

>> Calendar Control drag to select range
Hi, using MS Access 2003, is it possible to allow a user to drag to select a range of dates and, if 'yes', how do you store the selected date range? Many thanks, Jonathan Not that I know of. Storing a range of dates is quite simple though. You store the start date and the end date and use a calculation to show all the dates between. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "Jonathan" <Jonathan@discussions.microsoft.com> wrote in message news:4F22299F-3765-40D2-AA03-67FB42FFC07A@microsoft.com... > ...

Criteria help
The following sql query returns a good result except some of the enteries do have a business address and I will be using the query to produce labels. What I would like to do is if the BusinessAddress1, BusinessCity, BusinessState and BusinessPostalCode are blank then use the Home Address, HomeCity, HomeState and HomePostalCode. So will I be able to do this using the criteria or do I have to write a sql query. Either way would someone give some help to acheive a good result? Thanks, Richard SELECT [Main Directory].[Sr Pastor], [Main Directory].NoPastorChurch, [Main Directory].[Colle...

make query from to 64 bit system
I have a application need to be compile for 64 bit system. how can i read data from vb.net 2005. hi Jennifer, On 09.12.2009 17:36, Jennifer wrote: > I have a application need to be compile for 64 bit system. how can i read > data from vb.net 2005. First of all, I don't see the point why you're asking this question in an Access newsgroups. Further more, you should really be more spefific... mfG --> stefan <-- ...

move selection after enter into cell
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Upon entering data into a cell, I do not want to move to another cell when I hit Return. In the older version of Excel I used to be able to go into Tools/Options/Edit and uncheck Move Selection after Enter. How can I accomplish this in the 2008 version? thanks Aha - you've revealed yourself as a Windows user :-) Mac OS has never had Options, it uses Preferences instead. Go to Excel> Preferences> Edit where you can specify Up, Down, Left, Right from the list or clear the check to not have the se...

<> data query
I have two tables and both have a field called tag_id. The first table "my2003" has about 5000 records. The second table "monitor2003" is a subset of table "my2003" and has about 3000 records. I need help building a query that will give me the records in table "my2003" where the "tag_id" field is <> to the "tag_id" field in the "monitor2003" table. Also which type of joining of the two tables is needed? Use the query wizard to create an unmatch record query that will list all the records in my2003 but are n...

Option to select printer B4 printing Payables Checks
Client recently upgraded from 7.5 to 9.0 and they lost the option to select a printer after clicking print. From print payables checks In 7.5 when they would click on print, a window would pop up and give them the option to select a printer to send their checks to. Now it automatically goes to their defaulted printer. How do they set up the option in 9.0? Go to the Named Printers settings, and look at the checkbox to suppress the printer dialog box. This should solve your problem Best regards Mike "TROOTBMI" <TROOTBMI@discussions.microsoft.com> wrote in message news:...

Different queries but the same?
Hi Everyone, Access 2003. I accidently double clicked on the wrong thing in a new query based on another query. In the query grid, I added the qryVentThpyWithinLastArea and double clicked on the *. It only filled one column of my grid but when I ran the query, it showed all the fields from the query. I then dragged down Area field and added my criteria. I had been taught to double click on the blue title bar and then drag all the fields to the grid then add my criteria. Both ways give me the same data. What is the differnce? I pasted the sql below so you can see what's going...

formatting selected characters or numbers in each cell within a range of cells
I am sending this to several Excel newsgroups, because I am not sure which one if the most appropriate. I apologize in advance for the multiple postings. I have MS Excel 2000 (version 9.0.3821 SR-1). How can I format only a selected character or number within each cell in a specific range of cells? For instance if I have a cell containing �1234234�, how can I format it so that only the 2�s are bold or are colored? Obviously, I have a range of cells, and I want to format all the 2�s occurring in any of the cells as either bold or colored. Conditional formatting does not seem to all...

trim in query
Hi All I am trying to trim a leading space at the start of each field in my "Customers" table which is an Exel spreadsheet imported using "TransferSpreadsheet" After researching the internet I have been able to get this to work using, for example: CustomerName: Trim([F3]) in the Field line of a select query ("CustomersTrimQry"). My promlem is that when I append the data from "CustomersTrimQry" to the "Customers" table the space returns. Prior to using trim I ran 3 queries from the original "CustomersImport" table, "C...

How write blank cells as the criteria "URGENT"
I have learned how to count cells with data according to criteria, bu now part of my criteria is cells that are empty and I don't know how t write. {=SUM((D1:D81="0_F")*(J1:J81="I"))} In the illustration above I need to count the cells that are blank i Column D AND I in Column J I thought this is how I would write it, but it doesn't work. {=SUM((D1:D81=" ")*(J1:J81="I"))} Thanks for any help -- Message posted from http://www.ExcelForum.com How about {=SUM((D1:D81="")*(J1:J81="I"))} ? jeff >-----Original Message-...

Database SQL query
Sorry for this post here. If someone knows a group I can go to for this question, please let me know. I have a program in MFC that accesses and updates an Access database. The problem is I can't figure out how to add a field which contains a word with a single apostrophe ie (St. Vincent's). I'm using the CDaoDatabase and CDaoRecordset classes. I understand that DAO is interpreting the apostrophe as some type of terminator but there must be a way around the issue. MS Access has information on how to do it in Access VB but that doesn't seem to work in MFC. Thanks Ughh ...

criteria default value?
I've created a query that has a from/to criteria as shown below: >=[From Pre-School Number :] And <=[To Pre-School Number :] The possible numeric range is 1-99, so entering 7 and 7 gives you just Pre-School 7's details, or entering 1 and 99 gives you every Pre-School's details. Is there some way to have the "Enter Parameter Value" boxes come up with a default value already in there (say 1 and 99 respectively), which the user can then typeover if they choose. Thanking you in anticipation. You can create an Access Form [frmPreQuery] with 2 Textboxes (defaulted ...

MS query and Firebird
I am accessing a Firebird data base with MS Query into Excel. I am trying to change the column heading (column name) but as I writte a "Newname" in Column Heading MS Query dont accept. I try to change directlly the SQL statement with "AS" clause and without "AS" clause but MS Query dont accept. I dont know if the format of select statement is different in Firebird data base or is a problem with MS Query. JC. ...

Highlight a selected record
I am trying to highlight a selected record of a continuous form. Conditional Formatting is not working because it highlights individual fields. If Conditional Formatting would highlight the entire row, it would be perfect. I created a txtBackground text box to size of all my fields, sent it the back—behind the fields. Use the txtBackground as the control, but when it has focus it covers the data. Below is the code I used: Sub HighlightControl(ctl As Control) On Error Resume Next ctl.BackColor = 65535 End Sub Sub UnhighlightControl(ctl As...

Select Records that fall in an external set of ranges (subquery)
HiI have not done subqueries before so I am a bit confused. I have twotables1 - Phone Numbers - (converted to be numeric) (with other fields forlater) and2 - Exchange codes - multiple entries for code with 3 fields -Exchange name (duplication) - Start Range and End Range.I want to return a list of phone numbers that fall into the range fora certain exchange.For example - MRSPOO has 4 entries in the Exchange Table with 4distinct ranges. 200-299, 307-788, 997-1102 and 2036-6698. I would like to be able to return all the phone numbers for a singleexchange code that I would select (using a query...

Selecting data for charts by criteria
Hi there, I am in control of a large database of information and I want to be able to automatically make my charts update to include new data that is added. I am using a regular excel spreadsheet. I would also like to be able to select which data is used for a chart depending on a criteria such as, 0 <= value <= 10. Any help is appreciated. Thanks Tim Archer ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Tim - If it's an external database, I guess yo...

Project Manager 2007 queries
I am considering purchasing Proect Manager for my my Planner, we are a small office who a familiar with the basics but have a few queries which are a little more detailed, I hope someone may able to advise on:- 1. Reporting - What are the options for importing and exporting documents and the number of format types, are they clear and easy to learn and use ? 2. Graphs - Is graphical data readily available, are there differing options of graphs and are the tools for producing them clear and easy to use ? 3. Sand pits - Are there areas where 'offline' working is availabl...

UDF XLA and SQL queries
Newbie question. Apologies in advance if it's too simplistic or in the wrong forum. I want to add an UDF to Excel. This UDF queries a SQL database. Say the function name is 'MyFunc'; I want to be able to have (in a cell) =MyFunc("ABC") return some data from the SQL database (for example here, say the Product Description for product "ABC"). The user needs to select the database the first time he uses the workbook. The UDF is called often in the worksheets of a given workbook so I would prefer to open the connection once at the beginning and...

Parse field in query
I have a table called MasterData with a field called Originator. The field data looks like this... Lou Santana Steven Sentry/Miami/PU/WC/US CN=Kathy T. Buon/OU=Miami/OU=BU/O=WC/C=US Terry J Leif I need it to look like this... Lou Santana Steven Sentry Kathy T. Buon Terry J Leif How can this be parsed?? -- deb What a mess that you're stuck with! Below will look for the first / and return the string before it in a query. If there isn't a / it returns the entire string. Of course this doesn't help with the CN= in CN=Kathy T. Buon. Parsed: IIf(InStr([Originator],"/")...

Using form to enter "query criteria" (between values)
Good morning everyone, I want to create a form with two boxes, where first box is lower limit of the value and the second upper limit. This values should be used in my query as filter criteria. Like this (query filter criteria): > "textbox1" AND < "textbox2" Does anyone know how I can build this in a form (i.e. connect my textboxes and query criteria)? Kindly, Mikael Sweden By thinking about for another minute I found the (simple) answer. In the query criteria I put the following expression: BETWEEN [forms].[nameofform].[NameofDatefield1] AND [forms].[name...