what is the query to list records with a certain field empty?
what is the query to list records with a certain field empty?
|
0 |
|
aa
3/12/2008 5:27:46 PM
access
16762 articles. 
3 followers.
2 Replies
660 Views
Put Is Null in the criteria row of the query -- Wayne Manchester, England. "aa" wrote: > what is the query to list records with a certain field empty? > > >
|
|
0 |
|
Utf
3/12/2008 5:36:03 PM
Thanks a lot
|
|
0 |
|
aa
3/12/2008 7:08:33 PM
Similar Artilces:
Excel template that lists each month's meetings w/ some detail
I'm looking for an Excel template that lists every month of the year with room to list weekly, monthly and quarterly meetings, including some detail for each meeting. Probably would need to be landscape oriented. The main intent is to give the person using it an idea of upcoming meetings, the intent of each and what prep is required. ...
Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...
Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...
data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...
hyperlinking to an item in Global Address List Options
I have had success linking via html from web page to opening a specific contact record from Microsoft Outlook. I have done it like this: outlook://Big%20Contacts/Contacts/~Peter%20Crawford However, my very large contact database also has people with basically the same name, like John Smith. In the current "Address Card" view inside Microsoft Outlook, one of the John Smith's is always sorted first. If I enter outlook://Big%20Contacts/Contacts/~John%20Smith the first John Smith in the sort order is always opened. However, I want to specify one of the other John Smith's....
how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...
Constructing Hyperlink from the Database Record fields
I am working on a Windows XP environment using MS Office 2007 including Access 2007. I want to open a document from Access 2007 which I can easily do with Hyperlink type field. However since all the necessary information is already in the Database Record I try to avoid creating additional field which would be a Hyperlink type on the Form unless it is absolutely necessary. Below is the code that I have to construct the FullFileName which consisted of ServerName, Division, Unit, RequirementDirectory, FolderName and the FileName itself. As you can see the Database records has al...
Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...
User Defined Required Fields
I have set several field on sub window Sales User-Defined Fields Entry of Sales Transaction Entry as "Required". If the user remembers to click User-Defined, then they become required. But if the user never clicks on User-Defined from Sales Transaction Entry, then they can still save the new document without the required fields entered. Does anyone know what I can do to fix this asside from continual user training? Your answer is VBA. You own Modifer, so you also have VBA enabled. You'll need to write VBA code to open the window (literally, push the button) then set th...
Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...
form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...
Field mapping for Opportunity Products
Why I can't add some field mapping for OpportunityProduct system relation with Opportunity? I need to know the default pricelist that was assigned for an Opportunity when I am at OpportunityProduct form. ...
OL2003 Master Category List
Anyone know where the master category list is hiding these days. In 2002 it was hkcu\software\microsoft\office\10 \outlook\categories Any help would be appreciated. change 10 to 11. It won't exist unless the user has added something to it and like Outlook 2002 it's a Unicode list so the value is a binary and not a string. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options http://www.slovaktech.com/products.htm "Dan" <anonymous@discussions.m...
Smart List
This is a multi-part message in MIME format. ------=_NextPart_000_002C_01C4A796.1EC1D910 Content-Type: multipart/alternative; boundary="----=_NextPart_001_002D_01C4A796.1EC1D910" ------=_NextPart_001_002D_01C4A796.1EC1D910 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable BlankIs there any way to get a report in smart list that includes the = serial numbers using inventory transactions or sales transcations? I = have version 7.0 Lisa ------=_NextPart_001_002D_01C4A796.1EC1D910 Content-Type: text/html; charset="Wind...
Report for single record
I have a report that shows much data about a single record that I am trying to reuse from a previous database. In my new DB, the project manager is set in an intermediate table between project data and contact data. How do I reference a table outside the forms data source? Can I run a query in a form without having to build a query? Several possibilities. If you just need to look up a single value from another table so you can limit the report to the correct value, DLookup() should do that for you. See: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07...
Message box when DOB meets certain criteria
Hi all, I am a relative novice to the ways of Access, I am currently setting up a database in Access 2003. I have a form field (DOB) that if the date of birth entered makes the person between 2 and 16 years old I would like a message box to appear notifying the user (and be able to turn it off). Similarly if an existing record is opened and a child has it's second birthday I would also like the message to appear.. .I have posted a similar request and have got thus far: Private Sub DOB_BeforeUpdate(Cancel As Integer) If DateAdd("yyyy", 2, [DOB]) > Date Or Date...
Pivot table Data field question
I create a pivot table from three columns of data, using the Wizard.In the Layout dialogue, I drag one of my fields to the Rows; I drag anther field to the Columns. I need to drag a third field to the Data area. There's no conflict there, each Data cell of the resulting matrix would be unique. Excel doesn't let me do this. It insists on using a calculation, e.g. Sum of [field3], Count of [field3], etc. How do I convince Excel to insert just the value of Field3 in the Data? -- Regards Gershon Shamay The field you drag to the data area will always be summarized. However, if the v...
Data in Fields Changing Automatically
We use a simple Access table to keep track of resolutions adopted by our City Council. It's always worked just fine until today. When I add information in the fields of a row and click in the next row, the information I just added changes. There doesn't seem to be a consistent pattern to how this happens. There is a column for the number of the resolution. Sometimes this will change to the number of the row above, or two rows above. I tried rebooting. Thanks in advance for any clues! ?Version of Access? ?Working directly in the table instead of, as recommended, in a fo...
User defined fields #2
Entered user defined fields the way I wanted, however, the software has now put it all in ascending order and there are no sort options available. Any way to correct without having to do it all over? Put in ascending order where?=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "Darcy W." <Darcy W.@discussions.microsoft.com> wrote in me...
Change the default format of the query design view
When I use the query design view, I have to increase the size of the table window (from which I'm selecting fields) sideways and vertically to see the field names more clearly and that also means moving the criteria grid further down the page to make room. Is there a way to change the default table window size and default grid position so that I dont' have to do this every time? Thanks, Pat Pat When you find it, let the newsgroup know! You are (unfortunately) not the first person to wish there was a setting...<g> Regards Jeff Boyce Microsoft Office/Access MVP "...
Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...
Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...
Automatically Log of at a Certain Time of Day
Is there a way to set up XP SP3 to log of automatically. I need to be able to have my kids' PC's log off at night at a certain time. Use Scheduled Task that invokes Shutdown.exe. kjfrey55 wrote: > Is there a way to set up XP SP3 to log of automatically. I need to be able > to have my kids' PC's log off at night at a certain time. If you only want them to logoff and keep the pc running run a Scheduled Task that runs the Logoff command, if you want to shut down the PC have the task run the Shutdown command. John kjfrey55 wrote: > Is there a...
Template Wizard, why aren't records being created in database
When creating a template using the Template Wizard, the database is being created but only the field headings are created. Why aren't the records being created in the database? ...
Auto jump to the next field
I have 10 fields of a designated length. Each needs only one number. When I fill up the present alpha field I'm typing into, I want the cursor to automatically jump to the next field without manually hitting the tab key. Thanks! Hi אלירם, AFAIK, that's not possible with a formfield. -- Cheers macropod [Microsoft MVP - Word] "אלירם" <@discussions.microsoft.com> wrote in message news:A0D98BC8-90B8-4FF6-B3B8-3B65AA1F493B@microsoft.com... >I have 10 fields of a designated length. Each needs only one number. > > When I fill up the presen...
I'm looking for an Excel template that lists every month of the year with room to list weekly, monthly and quarterly meetings, including some detail for each meeting. Probably would need to be landscape oriented. The main intent is to give the person using it an idea of upcoming meetings, the intent of each and what prep is required. ...
Ho to make one field required based on critera of another field?
I'm creating a form and need to make the "comments" field required if the "code" field is =>20. I appreciate suggestions! Deadline Monster is lurking! User enters the job processing endcode value (numeric) into the "code" field. If the endcode is =>20, comments are required. (P.S. I don't know VB) Thanks! Star You would put your validation code in the Form's BeforeUpdate event. If Me.EndCode >19 Then If Len(Me.Comments & "") = 0 Then MsgBox "Comments are required" Cancel = True End If End If ...
Numbers in a text field-can I add them up?
Hi everyone! Using A02 on XP. I have a table of data with survey response fields that contain a 0,1,2,3,4 or 5. However, the fields are formatted as text, not numbers. I need to add up certain blocks (Items 1-6, Items 7-23, etc.) and then do some averaging. I cannot change the field types from text. Must I append to a new table or can I do something right in my query? I've got one field in my query like this: ES: [Item1]+[Item2]+[Item3]+[Item4]+[Item5]+[Item6] My result is: 553453 or 554444, etc. I want: 25 or 22, etc. I would really appreciate any help or advice. Thanks...
data value in Form field if no table entry
I have a form with a field which pulls through and concentenates 2 fields called [ContactFirstname] and [ContactLastName]from my table There are however some customers for whom I do not have names and therefore instead I would like Sir/Madam to appear in the field in the form I think I have seen this done somewhere using ELSE? but can't find it Any help/ideas gratefully received Perhaps something like this: Nz(Trim([ContactFirstname] & " " + [ContactLastName]), "Sir/Madam") -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access use...
hyperlinking to an item in Global Address List Options
I have had success linking via html from web page to opening a specific contact record from Microsoft Outlook. I have done it like this: outlook://Big%20Contacts/Contacts/~Peter%20Crawford However, my very large contact database also has people with basically the same name, like John Smith. In the current "Address Card" view inside Microsoft Outlook, one of the John Smith's is always sorted first. If I enter outlook://Big%20Contacts/Contacts/~John%20Smith the first John Smith in the sort order is always opened. However, I want to specify one of the other John Smith's....
how to query my web site from VBA and return a value to VBA
Hello All, From VBA I would like send a value to my web site, and have it return a value. I've learned how to use FollowHyperlink to send a value to an ASP script, but how can the ASP script send a value back to VBA?? Thanks, Brian Austin, TX You can use xmlhttp to make a request to your web page: '********************************************************* Sub Tester() MsgBox WebResponse("http://www.mydomain.com/myactualpage.asp? info=3Dblah") End Sub Private Function WebResponse(sURL As String) As String Dim XmlHttpRequest As Object Se...
Constructing Hyperlink from the Database Record fields
I am working on a Windows XP environment using MS Office 2007 including Access 2007. I want to open a document from Access 2007 which I can easily do with Hyperlink type field. However since all the necessary information is already in the Database Record I try to avoid creating additional field which would be a Hyperlink type on the Form unless it is absolutely necessary. Below is the code that I have to construct the FullFileName which consisted of ServerName, Division, Unit, RequirementDirectory, FolderName and the FileName itself. As you can see the Database records has al...
Conditional Formatting w/ a List/Icons
I am trying to allow someone to select "Green", "Yellow" or "Red" from a list and the cell to display a green/yellow/red icon appropriately. Or, if possible, the user could just select the icon (instead of selecting text). Is this possible? Use Data Validation for the list. Type in Red, Yellow, Green as the list. This give the user the list to select from. Use Conditional Formatting for the fill part. Set three conditions, If Cell Value-"Green" (select a green fill), etc.. -- If this helps, please remember to click yes. "...
User Defined Required Fields
I have set several field on sub window Sales User-Defined Fields Entry of Sales Transaction Entry as "Required". If the user remembers to click User-Defined, then they become required. But if the user never clicks on User-Defined from Sales Transaction Entry, then they can still save the new document without the required fields entered. Does anyone know what I can do to fix this asside from continual user training? Your answer is VBA. You own Modifer, so you also have VBA enabled. You'll need to write VBA code to open the window (literally, push the button) then set th...
Pulling counts out of query results
I have a query that has one field Type which is set to Count The query results are used in a report. The report has the fields in the detail section as TYPE and CountOfType (1 line only in the detail section) The report looks like this when displayed AS 28 AV 17 OR 5 I need to be able to get the individual AS No (28) and add it to another number elswhere in the report. How can I do this? I have tried using a textbox with an if function (if [type]="AS",CountOfType,0 but that did not work. Any help appreciated. Ray I think you will need to do it in the query. But try addi...
form and query problem. please help.
All tables are linked with weak entities. However, when i enter data on the form I can't get it to let me enter more than one partipicant without access generating a new invoice id. however i need one invoice to many participants. It wont work and i have no idea what to do at this point. in addition the workshop will not let me add workshop to invoice. this is a small mdb and i'd like to email it to anyone who can assist me with the relationships as I think this is the problem but I don't know what to do. please help me. INVOICE invoiceNO - autonumber invoice prices WORKSHOP wo...
Field mapping for Opportunity Products
Why I can't add some field mapping for OpportunityProduct system relation with Opportunity? I need to know the default pricelist that was assigned for an Opportunity when I am at OpportunityProduct form. ...
OL2003 Master Category List
Anyone know where the master category list is hiding these days. In 2002 it was hkcu\software\microsoft\office\10 \outlook\categories Any help would be appreciated. change 10 to 11. It won't exist unless the user has added something to it and like Outlook 2002 it's a Unicode list so the value is a binary and not a string. -- Ken Slovak [MVP - Outlook] http://www.slovaktech.com Author: Absolute Beginners Guide to Microsoft Office Outlook 2003 Reminder Manager, Extended Reminders, Attachment Options http://www.slovaktech.com/products.htm "Dan" <anonymous@discussions.m...
Smart List
This is a multi-part message in MIME format. ------=_NextPart_000_002C_01C4A796.1EC1D910 Content-Type: multipart/alternative; boundary="----=_NextPart_001_002D_01C4A796.1EC1D910" ------=_NextPart_001_002D_01C4A796.1EC1D910 Content-Type: text/plain; charset="Windows-1252" Content-Transfer-Encoding: quoted-printable BlankIs there any way to get a report in smart list that includes the = serial numbers using inventory transactions or sales transcations? I = have version 7.0 Lisa ------=_NextPart_001_002D_01C4A796.1EC1D910 Content-Type: text/html; charset="Wind...
Report for single record
I have a report that shows much data about a single record that I am trying to reuse from a previous database. In my new DB, the project manager is set in an intermediate table between project data and contact data. How do I reference a table outside the forms data source? Can I run a query in a form without having to build a query? Several possibilities. If you just need to look up a single value from another table so you can limit the report to the correct value, DLookup() should do that for you. See: Getting a value from a table: DLookup() at: http://allenbrowne.com/casu-07...
Message box when DOB meets certain criteria
Hi all, I am a relative novice to the ways of Access, I am currently setting up a database in Access 2003. I have a form field (DOB) that if the date of birth entered makes the person between 2 and 16 years old I would like a message box to appear notifying the user (and be able to turn it off). Similarly if an existing record is opened and a child has it's second birthday I would also like the message to appear.. .I have posted a similar request and have got thus far: Private Sub DOB_BeforeUpdate(Cancel As Integer) If DateAdd("yyyy", 2, [DOB]) > Date Or Date...
Pivot table Data field question
I create a pivot table from three columns of data, using the Wizard.In the Layout dialogue, I drag one of my fields to the Rows; I drag anther field to the Columns. I need to drag a third field to the Data area. There's no conflict there, each Data cell of the resulting matrix would be unique. Excel doesn't let me do this. It insists on using a calculation, e.g. Sum of [field3], Count of [field3], etc. How do I convince Excel to insert just the value of Field3 in the Data? -- Regards Gershon Shamay The field you drag to the data area will always be summarized. However, if the v...
Data in Fields Changing Automatically
We use a simple Access table to keep track of resolutions adopted by our City Council. It's always worked just fine until today. When I add information in the fields of a row and click in the next row, the information I just added changes. There doesn't seem to be a consistent pattern to how this happens. There is a column for the number of the resolution. Sometimes this will change to the number of the row above, or two rows above. I tried rebooting. Thanks in advance for any clues! ?Version of Access? ?Working directly in the table instead of, as recommended, in a fo...
User defined fields #2
Entered user defined fields the way I wanted, however, the software has now put it all in ascending order and there are no sort options available. Any way to correct without having to do it all over? Put in ascending order where?=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for=20 Administrators, Power Users, and Developers http://www.outlookcode.com/jumpstart.aspx =20 "Darcy W." <Darcy W.@discussions.microsoft.com> wrote in me...
Change the default format of the query design view
When I use the query design view, I have to increase the size of the table window (from which I'm selecting fields) sideways and vertically to see the field names more clearly and that also means moving the criteria grid further down the page to make room. Is there a way to change the default table window size and default grid position so that I dont' have to do this every time? Thanks, Pat Pat When you find it, let the newsgroup know! You are (unfortunately) not the first person to wish there was a setting...<g> Regards Jeff Boyce Microsoft Office/Access MVP "...
Strange query results/Wild characters???
Hello. My query is showing me strange values from whem loading a value from a form. It's very strange because if you have a form named FormA and inside a field named Field1 and if the field has a default value of 2 and if you run it. Then if you'll make a query with any table or query and put this in the column: Test:([forms]![FormA]![Field1]) your result will be 2, tha same as the the Form Field. My problem is that my query instead of showing me the value of 2 is showing another thing very strange, such as wild characters or value that has nothing to do with it. If I use t...
Importing Excel named ranges using MS Query
I want to use multiple ranges (named) as the data source for a pivot table using MS Query. When I import the workbook my options are only to select the "tables" (which are my sheets referenced as sheetname$). I don't want to use the entire sheet, just my named ranges in multiple sheets. Thanks, Kathy H Names ranges should appear in the list of tables, unless they're dynamic ranges. But if there's nothing else on the sheet, you can use the sheetname$ tables. KHanna wrote: > I want to use multiple ranges (named) as the data source for a pivot table > usi...
Automatically Log of at a Certain Time of Day
Is there a way to set up XP SP3 to log of automatically. I need to be able to have my kids' PC's log off at night at a certain time. Use Scheduled Task that invokes Shutdown.exe. kjfrey55 wrote: > Is there a way to set up XP SP3 to log of automatically. I need to be able > to have my kids' PC's log off at night at a certain time. If you only want them to logoff and keep the pc running run a Scheduled Task that runs the Logoff command, if you want to shut down the PC have the task run the Shutdown command. John kjfrey55 wrote: > Is there a...
Template Wizard, why aren't records being created in database
When creating a template using the Template Wizard, the database is being created but only the field headings are created. Why aren't the records being created in the database? ...
Auto jump to the next field
I have 10 fields of a designated length. Each needs only one number. When I fill up the present alpha field I'm typing into, I want the cursor to automatically jump to the next field without manually hitting the tab key. Thanks! Hi אלירם, AFAIK, that's not possible with a formfield. -- Cheers macropod [Microsoft MVP - Word] "אלירם" <@discussions.microsoft.com> wrote in message news:A0D98BC8-90B8-4FF6-B3B8-3B65AA1F493B@microsoft.com... >I have 10 fields of a designated length. Each needs only one number. > > When I fill up the presen...