Only want to include a field in the record under certain circumsta

I am designing a response log database for the fire departmetn I am working 
for.  I am trying to produce a report and querry in which an "address" field 
is displayed based on the "Calltype" field.  With HIPPA regulations, an 
address cannot be displayed on locations for medical calls to the public but 
I must display it for others.  Any suggestions?
-- 
E
0
Utf
1/4/2008 10:34:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
600 Views

Similar Articles

[PageSpeed] 10

On Fri, 4 Jan 2008 14:34:02 -0800, xX Longshank Xx
<xXLongshankXx@discussions.microsoft.com> wrote:

>I am designing a response log database for the fire departmetn I am working 
>for.  I am trying to produce a report and querry in which an "address" field 
>is displayed based on the "Calltype" field.  With HIPPA regulations, an 
>address cannot be displayed on locations for medical calls to the public but 
>I must display it for others.  Any suggestions?

Try using a calculated field such as

IIf([calltype] = "Medical", Null, [address])

in your query.

             John W. Vinson [MVP]
0
John
1/4/2008 11:46:00 PM
xX Longshank Xx wrote:

>I am designing a response log database for the fire departmetn I am working 
>for.  I am trying to produce a report and querry in which an "address" field 
>is displayed based on the "Calltype" field.  With HIPPA regulations, an 
>address cannot be displayed on locations for medical calls to the public but 
>I must display it for others.


Use a calculated field something like:

Addr:  IIf(CallType=xxx, Null, Address)

-- 
Marsh
MVP [MS Access]
0
Marshall
1/5/2008 12:26:39 AM
Reply:

Similar Artilces:

Problems with GP10 Field Service module
I keep having new problems come up after upgrading from GP9 to GP10. At first it was we could not post sop invoices that created a new contract and now we find that Contract Revenue Recognition isn't working. I figured out that applying SP3 resolved the invoice posting problem. When I open Transactions>ContractAdministration>RevenueRecognition I get the following error - "[Microsoft][ODBC SQL Server Driver][SQL Server]Violation of PRIMARY KEY constraint 'PKSVC00625'. Cannot insert duplicate key in object 'dbo.SVC00625'." before the window opens. If ...

adding field to form
Hello, I have made continiuse form with 10 field that works (text box in detail and lables in form header) but when i want to add one more field that text box is in detail part of form and lable in form header part ,I can not save my form.and access goes to repair mode after saving and there is no change when I reopen my form. thanks. ...

Lost Ability to Include Selected Text in Replies
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I no longer seem to be able to select some text in a message, and begin a reply to that message quoting only the selected text. Either in HTML or plain text, the selected text does not appear. I have tried all Preferences>Compose, Reply permutations. Either all of the in-coming message is included, or none is included. Does anyone have a similar issue? On 3/2/10 11:04 AM, AndyBobyarchick@officeformac.com wrote: > I no longer seem to be able to select some text in a message, and > begin a reply to ...

change the default opening of a form to enter a new record
How can you stop a form defaulting to record 1 when you want to type in a new record. The only way we are getting round this at the moment is to click on the icon New record. However, every time we open the form to enter another record it is always showing record 1 -- Thank you for reading my post. Hopefully you can answer my querie Shazza - Set the form property Data Entry to Yes. That will default to opening the form for new records. -- Daryl S "Shazza" wrote: > How can you stop a form defaulting to record 1 when you want to type in a new > r...

item list should display more fields
when looking through item list, it would be nice if more fields were available to see. If I am trying to find a specific item I don't always know what the lookup code is for a size or color. I would like the option to choose which fields are available for me to look at (like on a report) so I can more quickly find items in the list. ---------------- 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, follow this lin...

Document ID Field Locked
Hi, I'm trying to use VBA to unlock the DocumentID field (TypeID on the Sales Transaction Entry window). Initially when entering a new order it is unlocked but when you navigate off this field it locks and cannot be changed. The people who installed GP are no longer here so I'm not sure if this is a customization or if all GP installations work this way. In any case, I'd like to unlock this field completely so that users can change the Type ID at any time in case they put in the wrong one. No matter what VB code I use and what event, the field stays locked after tabbing off...

required fields--the sequel
Hello: I already got the answer to my question, thanks to the kindness of Habib Salim! I just have a follow-up question to the topic I created earlier on "requried fields". If you cannot make a required field "unrequired" even through the use of the Dexterity code, is that because of a setting (flag) in SQL? I'm just wondering what mechanism is being used in the system to prevent "unrequiring" a field. Thanks! childofthe1980s No that is in the application, in Dex code. From a SQL point of view, Dexterity creates all table fields as not null so eve...

Need to change date in field to whatever I want
I have a database for my research. I need to change the date, so that I do not have any restraints on my admissions. I have checked the data field for date of admission and it looks exactly the same in the data field, but for some reason, the date will not allow me to move forward with 2010. I am not sure what to do. Can you please help. This seems to be the only field that does not change to what I need it to change to. I currently am able to use the date for 2010. in other date fields except for one field. Please any suggestions. I am very new in setting or using access. ...

Deleting shortcut key In Recording new Macros dialog box
I have recorded several new Macros and I am running out of shortcut key letters to put into the shortcut key box within the Record a new Macro dialog box. How could I delete the shortcut keys that I have already used to reuse again to record new Macros? One way: Choose Tools/Macro/Macros... Select a macro for which you want to delete the shortcut. Click Options... Clear the shortcut key input box. Click OK, then Cancel. In article <9DC795B4-1EBE-4685-94DF-CD79ABEAF45D@microsoft.com>, John <John@discussions.microsoft.com> wrote: > I have recorded several new Macros ...

Including dynamic info in Template subject line?
I am looking to display dynamic information {!opportunity:name;} into the subject line of an email alert. We use the c360 email enhancement pack to post templates as URLs tha are called by the WFM. It appears that the CRM Templates do not populate info into the subject line (because it is not HTML I guess) but has anyone found a workaround to this? Is there ANY means of capturing this information into the subject line, so recepients could sort/search by this information? Thanks, Scott ...

Certain e-mails get bounced back to me with the following error.
Please help, Certain contacts I could send to recently now come back undelivered with the following error message. I have looked into the knowledge base and nothing matches. 553 Sorry, that domain isn't allowed (#5.7.1) Thank you in advance for any assistance. Lance You may need to authenticate to your outgoing server, which can be set up on the "Outgoing Server" tab for your account. -- Jeff Stephenson Outlook Development This posting is provided "AS IS" with no warranties, and confers no rights "Lance Stevens" <lance_of_denver@yahoo.com> wr...

Random Selection of Records
I have a table named Table1 that has 200,000 records. Is there a way to randomly select 40,000 records from this table ? Thank you in advance. carl wrote: >I have a table named Table1 that has 200,000 records. Is there a way to >randomly select 40,000 records from this table ? In your code, use the Randomize statement before using the query: SELECT TOP 40000 * FROM Table1 ORDER BY Rnd(1) -- Marsh MVP [MS Access] On Thu, 11 Mar 2010 06:02:10 -0800, carl <carl@discussions.microsoft.com> wrote: >I have a table named Table1 that has 200,000 records...

Add Record from form into two tables
I have a form (frm_Invoice) that records phone serial # from customers. I want to create a new record in both the tbl_Inventory and tbl_Inventory_Detail when someone scans in a DEC_SN into the frm_Invoice. The issue I am running across is that the tbl_Inventory_Details has a child relationship to the tbl_Inventory. I chose this set up because most of the inventory is manually entered from shipments received from a warehouse. I created a form and sub form for the inventory tying the SN_ID from each table on a one to many relationship. I do not know how to update both tables at ...

How do I average a column without including zeros
I want to average a column that is all percentages, but I want to excluding any cells with a 0%, what formula would I use? Thanks!!! Maybe.. =SUM(A1:A10)/COUNTIF(A1:A10,"<>"&0) KMHarpe wrote: > > I want to average a column that is all percentages, but I want to excluding > any cells with a 0%, what formula would I use? Thanks!!! -- Dave Peterson There may be a snappy built-in function in Excel to do this, but until someone responds with it, this formula will work: =SUM(D1:D5)/COUNTIF(D1:D5,"<>0") .... where D1:D5 is the range containin...

Dynamic range (column); include only rows before blank row above more data
Hello: One worksheet will contain more than one table holding lookup values (prices, CPU speed, etc.). They will be located one on top of another, with possibly some other data between tables, separated by at least one blank row. They will be expandable by row, as new prices, CPUs, etc. are added. I want the range name to expand and reflect all the rows in a table. Using a column as an example of tables, a (in A1) b c d e f represent 2 tables (ranges). Excel Hacks has the code for Define Name "Refers to" as =OFFSET(Sheet1!$A$1,0,0,MATCH("*",Sheet1!$A$1:$A$100,-1...

deleting a fields content
I have a situation in which a user will need to delete only the content of one field for a record. For each record in the table there is a column (field) called IsChildOf. On the webpage there will be a button that will delete only the content (a number) of the field IsChildOf for the record. So, if record ID 449945 has 25 in the IsChildOf and the person uses the delete button, the 25 is deleted. How is the DELETE statement constructed for something like that (I'm a noob to sql)? Thanks. John John > On the webpage there will be a button that will > delete onl...

How to find merged records?
How to find merged inactive records with advanced find? Or do I need to use e.g. workflow to put those to special marketing list? If I'm understand right, those merged inactive record I can delete and still save the data that is copied to active record. Merged information is anyway on opened form "The record was merged with the X recrd, and then deactivated." but it is too much work to open all inactivated records (~50000) Jukka Majander Hi, Have a look at this post: http://crmdude.wordpress.com/2009/09/28/modifying-views-outside-microsoft-crm/. Shafraz -- Visit my bl...

How to transfer a masterdocument including all subdocuments?
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel To whom it may concern: <br><br>I have written a book using Word's master document feature. I am now about to send the entire manuscript to the publisher. How do I do that? How can I save the masterdocument including all the subdocuments on a CD-ROM in a format that my editor can actually read, in the right format, all subdocuments in the right order etc.? I love the masterdocument feature as such but I cannot find information on how to &quot;undo&quot; it or how to share all the files wi...

Allow searching of date fields to allow wildcard characters
For fields such as birthdays and expiry dates, customers often want to search the database for a list of contacts with birthdays. It would be great if you would allow wildcards in date field searches so that we could search for all birthdays that occur in a specific month. The year is not always relevant when performing date searches. Birthdays is a common search performed by customers in the financial sector as well as other industries. Currently, we have to add an additional text field to facilitate this type of search. It adds confusion to the users, in some cases double data entry...

Can I have partial superscripts in a functions including cell ref.
For example, in the function ="3. As of "&TEXT($A$9,"mm/dd/yy") &"(1)" is there any way to make the (1) superscript? Hi no you can't -- Regards Frank Kabel Frankfurt, Germany "Mike" <Mike@discussions.microsoft.com> schrieb im Newsbeitrag news:DF9DD8C2-0020-44F1-97D1-24179D59C053@microsoft.com... > For example, in the function ="3. As of "&TEXT($A$9,"mm/dd/yy") &"(1)" is > there any way to make the (1) superscript? ...

I want to get rid of this message!
"The contents of this appointment have been updated." Office 2009, WinXP Please Help! Wendy Oops.....I meant Office 2007! "Wendymel" wrote: > "The contents of this appointment have been updated." > > Office 2009, WinXP > > Please Help! > > Wendy Under what circumstances are you seeing that message? -- -Ben- Ben M. Schorr, MVP Roland Schorr & Tower http://www.rolandschorr.com http://www.officeforlawyers.com/outlook.html Author: The Lawyer's Guide to Microsoft Outlook 2007: http://tinyurl.com/ol4la...

cut and paste limitations for TO Field
I use Outlook 2002 and an Exchange 2000 server. The clients in question have workstations ranging from Windows SE to XP. We have a program that puts email addresses into the clipboard. when I paste to Outlook 2002, I only get about 1400 of the 4800 email addresses. However, if I paste the email addresses to OWA all 4800 addresses are listed. If I paste to Excel I get about 1400 email addresses. If I export from the application and import into a folder in Outlook I get all addresses. Is there a limitation in Outlook from pasting so many email addresses in the TO or BCC fields? ...

MS Access 2007: Calculated field based on subform:
Hello: As part of a review assignment, I have been tasked with adding a calculated field to a form that will count the number of items listed in the associated subform. The subform is based on the table "tblProduct", and is a subform named "frmProductSubform". The Parent table is named "tblCompany". The tables are related by ProductID, and CustomerID fields. The fields included in the subform are: ProductID, ProductType, Price, and Unit. I am trying to create a calculated field based on the subform that will count the number of line items in th...

Check if field is empty (what code to use)
In a form's BeforeUpdate I check if certain fields are empty, and if that's the case I cancel the BeforeUpdate. I always use this kind of code for that: If IsNull([Surname] or Surname = "" then However, I noticed that others use other ways for checking this: If Len(Nz([Surname],"")) = 0 Then If Len(Trim([Surname] & "")) = 0 then If RTrim([Surname].Text) = Nothing Then If Nz([SurName],"") = "" Then I'm wondering what I can use best in this case. Also I'm wondering, why do you have to check for a ""...

help with summing fields in repot
I have a field called ponts assigned in a report. It uses an if statement to assign points based on which class my students are attending. I want to sum the total points accrued for each student. In the detail section I made sure I named the if statement field pointsassigned and then I entered a text box with =sum([pointsassigned]) when I run the report, It doesn't recognize pointsassigned. I checked spelling etc. I even tried =abs(sum([pointsassigned])) it doesn't recognize the name I assigned. Any thoughts on what I am doing wrong ? I appreciate any help Thank...