Setting a conditional value in a query to perform an additional qu

Ok, I have a slight brain twister here and wondering if anyone could help 
shed some light on this. :)

Im in the midst of creating a database that will hopefully replace a 
paper-based system of storing 'shift runsheets' from a 24/7 office. There is 
3 shifts per 24 hours; Morning (6am-2:30pm), Afternoon (2pm-10:30pm) and 
Night (10pm to 6:30am). Each day it adds a new row of data to the table 
"shift" and users can peform searches on previous sheets by searching the 
table "previous" which links to "shift". When a user wishes to search, they 
select the shift and the date and it presents them with the results. However, 
the issue I have is that when they search for a nightshift, it only presents 
the last 2 hours of the date and the earlier 6 hours of the shift from the 
night before (not very helpful! :P).
Is there a way to set the query to only reveal the relevant information 
according to the shift?
0
Utf
7/27/2007 2:34:07 PM
access.queries 6343 articles. 1 followers. Follow

0 Replies
1197 Views

Similar Articles

[PageSpeed] 22

Reply:

Similar Artilces:

Build Dynamic Query from Form
I am trying to build a dynamic Query from a Form. I keep getting an error that reads ‘Object qryFilter already exists’ I suspect it has something to do with the string of dates being passed to the Query; strDateCondition = "([Trades].[TDATE] Between [Forms]![SearchForm]![cboFrom] And [Forms]![SearchForm]![cboTo])" I am trying to add a means for a user to Query by Customer and Trader AND all records between two dates. This was working fine for Customer and Trader; when I added in the code to filter by dates I started having problems. I know the SQL will be li...

Change <value> in outlook
We Have Exchange 2003 sp2 and outlook 2003 sp2. I have renamed 2 AD accounts. Changed all display names, accounts name etc with the 2 accounts. When users open outlook and send to renamed account they see account name <oldaccountname> in the autocomplete list. I have cleared nk2 files in profile , but it still shows newaccount <oldaccountname> in drop down box. Is there a way to edit the <oldaccountnam> value of the renamed account? I tried the same thing with a freshly loaded PC. with the same result. Thanks You can delete the nickname files. -- Ed Crowley MVP - E...

excel, worksheet, set print area, position selection on printed pa
Office 07, Excel. Print area is set, print preview displays selection, always on far left of page. I want to choose the position on the page, eg centered or right aligned etc. Help please. Hi, To get your printout to center on the page chose Page Layout, click the Page Setup quick launch button (bottom right corner of the Page Setup group). Choose the Margins tab, click Horizontal. There is no command to right align a printout, but you can change the left margin to force the printout to the left. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Eve Al...

Count unique values
Hi, I've some problems to count unique items (Invoice #) in a pivot table. There is the default solution "Add-a-new-calculated-column" as mentioned on http://contextures.com/xlPivot07.html#Unique, but in my case, it doesn't works. My problem is, that these values aren't in a Excel worksheet; it's a external data source - a SQL-Select via DAO/ADO. Because of that, I don't have the possibility to add a new column. Another reason is, that the pivot table should always be dynamic: Group over this field, group over another field - and always show the number of unique...

Clearing security settings
I am setting up a new server that will be home to GP version 8. I am migrating from 7.5 to 8. I want to clear out all users, advanced security, and standard security settings and recreate them on the new server. Anybody ever scripted this? I assume I want to truncate tables, but I don't know which ones, other than what is show in techkno Steps to remove Advanced Security (874401). TIA If you run a delete statement on the SY02000 table, it will remove all settings, then you can run a reconcile against security restrictions and user access. "Cindy Lange" wrote: > I...

Using Sumproduct when some of the values are null
I am having a problem using Sumproduct when some of the cells have null values. I will try to describe the problem. Please let me know if you need additional information. Do any of you have any ideas of how I can fix that sumproduct to get it to work? A B C Row Trend Claims 5 Emergency $0 6 Emergency $0 7 Emergency $0 8 Emergency 81.68% $24,444 9 Emergency 35.00% $164,758 10 Emergency 35.00% ...

please help with this query
Ost Ocity Dstate Dcity Carrier Price Rank Diff A B C D X 1200 1 100 A B C D Y 1300 2 100 A B C D Z 1350 3 100 A B C D W 1789 4 100 A1 B1 C1 D1 X1 785 1 A1 B1 C1 D1 Y1 789 2 The rank for every carrier is based on the price . If rank1 carrier is not a pariticular carrier(say if it is not X1 or Y1 or Z1), then i want to calculate the difference be...

Background changes conditionally
Hi all, I have a spreadsheet that shows the floor plan layout of my call centre. On each desk, the desk number and identity are displayed. I would like to colour the background for those pcs that are on the same subnet. I have a sheet called data - it contains Table Number, Desk Number, PC ID, IP Address. How could I get it to change the background colour if the pc is on subnet 162, and set a different colour for those on subnet 167 any ideas? -- PeterG ------------------------------------------------------------------------ PeterG's Profile: http://www.excelforum.com/member.p...

Getting Cell Value from the Concatenate formula
I built a concatenate formula that returns the following result: =Jul! $D27 I am looking for the cell contents of Sheet: July Column D Row 27. I tried to use offset, but I am stumped. Can I add something to the front of the concatenate to not only build the reference to the cell, but also return the value instead of the =Jul!$D27 ? Thanks John =indirect(yourformulahere) Don't include the equal sign in your formula. And match the name correctly (Jul or July???). Depending on the name of the worksheet, you may need to have a string that looks like: 'Sheet 99'!d27 =indirec...

Update Query
Access 2003 XP SP2 I am having a problem with an update query. Table is in a one-to-one relationship, referentail integrity and cascading data are checked. (The fileds I want to update are not in both tables) Table name= payForward Has 15 fields ie: ID, Name, MemID, Oct , Nov, Dec, etc Oct-Sep fields are yes/no type I want to "select" a field (Oct-Sep) via a query parameter and repalce "yes" with "no". Here is my query: UPDATE payForward SET [Enter month]=No The messages I get is 'operation must use an updateable query' Wh...

Employee - HR
I recently installed a SP against my test companies. After install, in the employee master, when I click on the Addiitonal Position button, I now see a screen labeled Employee Seat Assignments instead of the Employee Positions screen. I do see the Employee Positions screen in my Fabrikam company but not in some additional companies that I setup. Has anyone experienced this ? Do you know how I can set it back to the Employee Positions screen. Thanks !! ...

find match then change cell value
In column A I have cells filled with text and in column B I have cells filled with numbers. I need to check if the number in cell C1 equals any of the numbers in column B. If a match is found then I need to change the text in column A to CBO. e.g. Column A Column B Column C aep 5 7 apa 0 gci 59 xto 5000 xle 7 oih 253 ed 8 Since the cell C1 = 7 equals the 7 from column B, I need to change the data in column A from xle to cbo. Is this possible...

Set Enabled On All Controls in a Form?
I am trying to set all controls visible, unlocked, and enabled, as the baseline for disabling or locking down some fields based on other criteria. Here's what I tried, which gives me an "Invalid or unqualified reference" error: Function SetEnabledFieldsBase() Dim ctl As Control For Each ctl In Me.Controls .Enabled = True .Visible = True .Locked = False Next ctl End Function I based this on several examples that were intended to do something to all text boxes on a form. (I have never done this before, so sorry if this i...

VLookup #VALUE! error help needed to resolve
The following is the funcation I have: =VLOOKUP(B10,'FA CC Summary Report 1141'!F$9:G$92,2,0) I have all the columns formatted the same; as in the column that the function is using to lookup is text and so is the column for this figure in order to pull back the appropriate answer. I have keyed the data instead of having links. I have replaced the final '0' with TRUE & FALSE then put it back. I have formatted the columns for text and for numbers. But I am getting the #VALUE! error in SOME of the cells NOT all of the cells. I don't know what else to d...

Additional Lanes for Dongle
We have an NFR copy of RMS and need to add additional lanes to our dongle. How can we do this? Are you a reseller? you get one copy of 3 station license. If you only have a single station lic just call partnersource and they will send you a new one. "Mollie" <mollie@discussions.microsoft.com> wrote in message news:86D8A793-20B1-4590-BFF6-146C9F715594@microsoft.com... > We have an NFR copy of RMS and need to add additional lanes to our dongle. > How can we do this? Yes, we are a reseller. It does have three stations, however we would like to obtain more, is this...

how do I set up a spreadsheet file?
How do I set up a spread sheet file using Excel? Now this is going to be an interesting one to watch. Personally, I'd say you're going to need to be a lot more specific about what you want to do. Failing that, go buy some books on Excel. Regards Trevor "louis markos" <louis markos@discussions.microsoft.com> wrote in message news:A30023FF-61DE-4D21-8E31-77FF134B8F5C@microsoft.com... > How do I set up a spread sheet file using Excel? louis markos wrote: > How do I set up a spread sheet file using Excel? I did a quick search for an Excel tutori...

setting a column value in code for an updatable ViewGrid
Is there a way to set the value for a column in code when a ViewGrid row is edited? I have a row-last-updated date that is a required audit field in all our systems (required by data management). I don't really want the user updating this field as it should always be system controlled. If I create an editable ViewGrid tied to either an SqlDataSource or an ObjectDataSource, the field seems to only be updatable by the user. I can set it the first time by setting the DefaultValue in code but that has no effect after there is an initial value in the column. Do I need to co...

how to set up a query
I am using sql server 2005 express and have 3 tables Table1 Dept_Id (primary key) Dept_Name Table2 Employee_Id (primary key) Dept_Id (foreign key with table1) Employee_Name Table3 WorkSchedule_Id (primary key) Employee_Id (foreing key with table 2) Date_To_Work (date type) I want to list all the Departments (Dept_Name) that do not have anyone scheduled to work on a particular date (ie '1/20/2010' ) Any help would be appriciated. Thanks in advance, RABMissouri2010 Try this: SELECT dept_name FROM Table1 AS D WHERE NOT EXISTS(SELECT * ...

Excel comparative query
I'm respectfully requesting assistance with the correct formula to use for the following query: I'd searching all of column A on spreadsheet 1 to see if a value in column A of spreadsheet 2 is there, and placing a result X (or another) next to (adjacent cell) the confirmed value on spreadsheet 2. Please help if you can. Chris Hi Chris see your answer in puclic.excel -- Regards Frank Kabel Frankfurt, Germany Chris wrote: > I'm respectfully requesting assistance with the correct formula > to use for the following query: > > I'd searching all of column A on ...

Currency Format Lost in Union Query
Thanks to John Spencer, I resolved one problem in my union query (Thank you, John). But I have another problem. Some of my fields in my union query are currencies and others percents. I see both formats are lost. I am sure it is a union query problem, because when I ran an individual query, none of the formats were lost. Is there any way I can keep currency and percent formats without writing, FORMATCURRENCY, etc for each field? Thank you. What data types are these fields? The field in a UNION query will normally take on the data type of the field in the first SELECT. So, if ...

Setting up Outlook sync with RPC over http
I'm trying to setup my Outlook installation to sync with my school Outlook servers. The IT administration has hinted that it's possible and some students have figured out how to accomplish this, but they don't want to support it. Supposedly it's a nightmare to support the myriad hardware and software configurations of 450 students (doesn't sound so large does it?). The only supported means for accessing our email accounts and utilizing the calendar function of Outlook etc. is via webmail. However, I was told that if I could figure this out then no one would have to ...

Pivot Table data values
I need make a pivot table using the values as the data. I used to be able to do this, but now it only sums, counts or calculates. Is there a way to return the data values? ...

Getting all Sproc through one query
Hi all, can anyone help in getting below information from all stored procedure from one DB we have 100 procedure in one DB of sql server 2005.instead if of manully getting info for each procedure i want all below details in one shot or one query Procedure Name : Input Parameter : Output Parameter : Called By : Calls : Dependent tables : Thanks in advance ...

How to carry over date value from one record to the next.
I have searched this site and the internet in search of an answer to this problem but none of the solutions I've found have seemed to help. I have a form that is used for data entry. The first two text boxes are called Supervisor and W/E Date. These two values will remain the same for several dozen entries. I have tried just using CTRL + ' to repeat the data but that is getting quite time consuming as well. How do I automatically carry over the value to the next record once I input the first data in? Any help on this would be much appreciated. -- Message posted via AccessMo...

Page settings get lost ?
I have selected "Landscape" in the page settings of a report and then saved it. Every now and then, it appears to forget this setting when running the report. Anyone know how to avoid this problem ? What version of Access is this? If it is 2000, you need to apply the service packs. You can download them from: support.microsoft.com/sp Be sure to get the JET sp as well as the Office sp. You will also want to turn off Name AutoCorrect: http://support.microsoft.com/?id=240826 More info about this mis-feature: http://allenbrowne.com/bug-03.html If 2007, there's ...