Tough Date Query.

Feb 15, 2007

G'day. This is a tough one and I could use some help.

I work for the military and day to day there are people on orders. My finance folks are providing me with an Excel spreadsheet with all the names and start/finish dates of people on orders and I import that into a table.

What I need to do is query out the orders that fall on drill weekends.

An example would be; Joe is on orders from 1-Feb-07 to 15-Feb-07, drill is on the 5-6 of Feb. The queiry would need to "pick" him out as being on orders over that drill date period. Dave is on orders from 10-Feb-07 to 10-Mar-07, for the drill period of 5-6 Feb he would be left out of the results. I would also need to change the drill dates month to month to reflect each drill weekend. Any help is welcome and appriciated, Tim

View Replies


ADVERTISEMENT

Help - Tough Query

Jun 22, 2005

I want to return a list that contains each employee's ID, the date of their last payrate adjustment, and their current payrate. Note that in the table below, employee 1002 was a bad boy in March of 2005, and his hourly rate was reduced to 14.00.


TableName: Employees

EmployeeID............Date.............PayRate
-------------------------------------------
....1001...............1/24/03............12.50
....1002...............2/28/03............12.75
....1003...............5/14/03............10.50
....1002...............3/15/04............15.00
....1001...............6/22/04............14.00
....1002...............3/16/05............14.00

The result set should look like:

EmployeeID...........Date.............PayRate
-----------------------------------------
...1003................5/14/03............10.50
...1001................6/22/04............14.00
...1002................3/16/05............14.00

View 1 Replies View Related

Query - It Is Tough

Jul 18, 2006

Every body has worked on a query using parameter
[Forms]![Form Name]![Name of Field of Form] to get the desired result.

It works fine to use for single field. But I could never see this to be used in may fields at one go. For example how about this logic.

Table Name : Document Records, Fields
1st submission letter no
2nd submission letter no.
3 submission letter no.

A query using all three fields based on form field [Text2].

Means if may be letter no. 12 was used for two submissions, now if I put 12 in form filed and run query it does not display any thing.
Can some body figure it out and help?

A sample question is attached

View 2 Replies View Related

Tough Modification Of Existing SQL Query

Mar 7, 2007

I’m trying to modify an existing SQL query that calculates actual working days between start date in tblECN and end date in tblECNDetail to do the same between start date in tblECNDetail and end date in tblECNDetail (same table).

Where I’ve gotten into a bind is with the joins of the two tables that are needed in the first query but not in the second as both fields come from the same query.

In the current working query I join about tblECN.ECNID and tblECNDetail.ECNID. In the new query I don’t think I need to create these joins at all as BOMEntryStart and BOMEntryEnd both come from the same table (tblECNDetail). I’ve bolded all references to the table no longer required for join but I don’t know which join statements I can be rid of. When trying to save I get an error in the FROM statement if I leave as is. If I get rid of the join statement I get an error

working query
SELECT [tblECN].[ECNID], [tblECN].[RelDate], [tblECNDetail].[PendDate], DateDiff("d",[tblECN].[RelDate],[tblECNDetail].[PendDate],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[PendDate],2)>5,5-Weekday([tblECNDetail].[PendDate],2),0)+IIf(Weekday([tblECN].[RelDate],2)=6,1,0)-IIf(Weekday([tblECN].[RelDate],2)>Weekday([tblECNDetail].[PendDate],2),2,0)+IIf([tblECN].[RelDate]=[tblECNDetail].[PendDate],1,0) AS weekdays, Query5.HCOUNT
FROM (tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL
FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
WHERE (((tblHoliday.Holiday) Between [tblECN].[RelDate] And [tblECNDetail].[PendDate]))) AS Query4
GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE ((([tblECNDetail].[PendDate]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));


Not working query
SELECT [tblECN].[ECNID], [tblECNDetail]., [tblECNDetail].[BOMEntryEnd], DateDiff("d",[tblECNDetail].[BOMEntryStart],[tblECNDetail].[BOMEntryEnd],2)- IIf([HCOUNT]>0,[HCOUNT],0) AS DaysDiff, Int([daysdiff]/7) AS Weeks, [daysdiff]-[weeks]*3+IIf(Weekday([tblECNDetail].[BOMEntryEnd],2)>5,5-Weekday([tblECNDetail].[BOMEntryEnd],2),0)+IIf(Weekday([tblECNDetail].[BOMEntryStart],2)=6,1,0)-IIf(Weekday([tblECNDetail].[BOMEntryStart],2)>Weekday([tblECNDetail].[BOMEntryEnd],2),2,0)+IIf([tblECNDetail].[BOMEntryStart]=[tblECNDetail].[BOMEntryEnd],1,0) AS weekdays, Query5.HCOUNT
FROM ([b]tblECN INNER JOIN tblECNDetail ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]) LEFT JOIN [SELECT Query4.[tblECN].[ECNID], Sum(Query4.TEMPVAL) AS HCOUNT
FROM (SELECT [tblECN].[ECNID], 1 AS TEMPVAL
FROM tblHoliday, [tblECN] INNER JOIN [tblECNDetail] ON [tblECN].[ECNID] = [tblECNDetail].[ECNID]
WHERE (((tblHoliday.Holiday) Between [tblECNDetail].[BOMEntryStart] And [tblECNDetail].[BOMEntryEnd]))) AS Query4
GROUP BY Query4.[tblECN].[ECNID]]. AS Query5 ON [tblECN].[ECNID] = Query5.[tblECN].[ECNID]
WHERE ((([tblECNDetail].[BOMEntryEnd]) Between [Forms]![frmDates]![StartDate] And [Forms]![frmDates]![StopDate]) AND (([tblECN].[DoNotProcess])<>"Do Not Process"));

Any help is greatly approciated.

View 13 Replies View Related

Tough One

Apr 5, 2005

Hi,
I've been away from Access for a while but now I'm back in action :) . Maybe I'm rusty from the lack of practice but I think this one will be hard to resolve. So here it is:

I have a form in datasheet presentation and when I double-click on a record, I need to open another form. This I can easly do, now for the hard part, the form I must open has two tabs, one for a specific region and another for the rest of the world. I want to make the sub-form point to the concern record, which I can easily know, from the first form. So, basicly, I need to make a sub-form point to a record from another form.

Currently, I can open the sub-form alone, without going through the parent form, but I can't make it point to the concern record. I can use the "where" clause of the "docmd.openform" command but I would like the form to just be on the concern record and still have access to the entire recordset.

Well, that's about it. I told you it was going to be a hard one, well I still think it's hard :) .

Maybe a little code snippet would help:

Private Sub PEIN_DblClick(Cancel As Integer)
Dim MemberID As Long
Dim ComputerID As Long

MemberID = Me.MemberID
ComputerID = Me.ComputerID

If Me.Region = "ON" Then
'This is where I don't know how to do it
'I tried a "OpenForm" but I can only access the parent form
'And not the sub-form
Else

End If
End Sub

If you need more infos to help me, please don't hesitate to ask, I'll be monitoring my e-mail box closely :D .

Thx,
JCA

View 3 Replies View Related

Tough Issue Securing Database

Aug 23, 2005

Hello,

for our company we have an access application we use to keep track of our customer-info.

The databse consists of 3 parts:
The (replicated) frontend
The databse holding the changing data
The database holding the unchanged data (lookup db)

We have the following problem:
somewhere in our front-end db is a bug that allows users to change the contents of the lookup db. In our case this can result in a major problem because the users are able to change the city-zip code table.
I have tried to figure out where things go wrong but so far no result.

We have picked up the idea of making the lookup db read-only for normal users. This will prevent them from modifying the contents, and will also result in error messages. Hopefully will these error messages point me in the right direction of the bug.

Problem is that the ldb, created when opening the mdb, inheritates the same security settings of the mdb. If we set the mdb to read only, the user gets an error-message stating it can not find the lookup db or that the lookup mdb is locked. This is because the user can not create or modify the ldb. On the other hand, if we set the security to create and modify for the mdb, the user still is able to change the data in our lookup db.

I have never worked with the security in access itself. and I want to try to avoid that. Is there a way of securing the data in our lookup db. So I'm able to figure out what really goes wrong.

Can someone help me out on this major problem.

View 10 Replies View Related

A Tough One - Importing A File Into A NEW Table

May 25, 2006

I work for a company that has a mainframe application that produces "outgoing" files. They are downloaded to a server and converted to ASCII (all at one time) and then ftp'ed to various clients. We are converting to a client server application and I am in charge of verifying that the mainframe application files (converted) are EXACTLY the same as the server application files. I came up with a process of loading the files to access databases and then with a series of queries and reports produce a list of exactly what doesn't match, which record, what positions and display the two fields that are mismatching.

My problem is that when I try to load the Mainframe file (note that it has already been converted and "eyeball to eyeball" the files are alike) using the "new" / import functions, Access rejects the file because it is greater than 65000 bytes. If I cut the file down to under 65k bytes it loads fine. I can load a 500,000 byte file that was created on the server in the first place.

Any suggestions? I can't change any of the processes that create the files. But I can change copies of the files so that I might be able to get them to load so I can verify that the data inside is the same. I'll let the geeks figure out how to fix the file/record control stuff.

(wouldn't have this problem if I was back on a mainframe!!!!)

Mac

View 3 Replies View Related

Queries :: Date / Time Query - Return All Records Of Specified Date Or Date Range

Aug 19, 2015

I have a table that has entries recorded with date and time in one field, and I want to have a query that returns all records of a specified date or date range, regardless of the time in the field.

I have tried

Code:
Between [StartDate:] And [EndDate:]

And

Code:
Between [StartDate:] & "00:00" And [EndDate:] & "23:59"

Neither of which work ....

View 13 Replies View Related

Queries :: Date Query To Match Other Date Query?

Nov 3, 2014

running 3 queries together.

1 qry has a date parameter of start/end date(running before the 3 are together)

Have a field in 1 of the 3 queries with a date field that I need to show being <= the date parameter...

How/where do I use this criteria?

View 3 Replies View Related

Query Date From DateTime - General Date Format Field.

Mar 10, 2006

I have a date and time stamp in a Date/Time field of General Date format (3/1/2006 7:52:25 AM).

I wish to select query on the table's Date/Time field by date portion only (3/1/2006) and not include the time portion (7:52:25 AM) of the field.

Using this expression in the query's criteria - "Between [Enter Start Date: (MM/DD/YY Format)] And [End Date: (MM/DD/YY]" will not return the date ranges as desired without also typing in the full time string.

How can the date integer be parsed out and the query properly expression ed on the criteria field without using VB?

View 7 Replies View Related

Queries :: Set Up Query Which Responds And Filters By Date (Older Than X Date)

Jul 11, 2014

I currently have a query set up which responds and filters by the date.

I use the current code in the criteria on the query for todays date less 7 days

Code:
DateAdd("d",-7,Date())

This works perfectly and I had no problem with it. However I need to adjust the criteria so it is less than 7days ago OR OLDER....

How would I go about doing that?

View 4 Replies View Related

Queries :: Date Field With Time - Query For Date Only And Get All Records

Apr 26, 2013

I have a query based on a table which has a date field. the field both in the table and the query have the time also in the date value so when I try to query on a date I get nothing if I copy the date and time from the field I will get the result for that record if I just use the date I get nothing. I have tried the format which does display just date but if you click on the field the time is also there You must be able to query for a date only and get all the records.

View 11 Replies View Related

Reports :: Report Won't Sort By Date When Query Contains Date Formatting

Jun 21, 2013

A few months ago I created a report that displays the results of a long union query comprising a dozen or so individual queries, each containing an expression that yields a date (or sometimes date and time). I set the report to group by query and then sort by the date expression. Now for some reason that I can't fathom the report has always only ever offered me the option to sort the date "A to Z", I infer it thinks the date is text, but this misunderstanding has never actually stopped it sorting by date perfectly well. It worked. No problems.

However I have recently added formatting to some of the queries so that they just display date, not date and time e.g. Format([dateandtime],"dd/mm/yyyy"), and now the sort by date in the report no longer works. None of the sorting or grouping options have changed, but it now sorts just by the "dd" component of the date - so it thinks 21st June is later than 20th July. why?

View 14 Replies View Related

Queries :: Start Date And End Date Query?

Mar 26, 2013

I've attached a stripped down version of a small order database I'm working on.

A user would enter an order, the amount and the date the order is required by.

As you can see from tbl_seasons, the business has financial periods that match the first and last 6 months of each year. Each season has a start date and end date.

What I'm trying to build are two queries:

1. A query which lists all orders and has an extra field which shows the "season_id" that the order (date) relates to (based one the start date and end date in tbl_seasons)

2. A totals query which shows the total order amounts by season

how I might build these 2 queries.

View 2 Replies View Related

Hard Code TIME To Selected Date On Form (to Make It Date&time) For My Query Criteria

Aug 17, 2006

Hello buddies :D, do you have any idea how to make this work?

To select data that falls within this criteria of date range between cboDate and cboDate2 (fields on my form). The date in [tblJobDetails]![timeIn] come in this format "08/17/06 10:24 AM", but the cboDate/cboDate2 (takes in date only e.g 08/17/06) what i am after is to evaluate specific hard coded time in addition to the date entered, i.e. even tho, i haven't entered time on the cboDate/cboDate2, I want specific time hard coded where e.g If i select a date range of 08/17/06 and 08/18/06 on my cboDate and cboDate2 it should really be evaluating: 08/17/06 8:00 AM to 08/18/06 8:00 AM.

This is the criteria i curentlly have on my query in design view tha works perfect in selecting date only.
([tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Or [tblJobDetails]![timeIn]>=[Forms]![frmPendingJobs]![cboDate] Is Null) And ([tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Or [tblJobDetails]![timeIn]<=[Forms]![frmPendingJobs]![cboDate2] Is Null)
How can I incorporate 8:00am to 8:00am into my cboDate and cboDate2. What can i do to make this happen? Your kindness will be greatly appreciated http://www.naijaryders.com/forums/images/smilies/thankyou.gif

View 10 Replies View Related

MAX Date Query - How To Have A 'unique' Date?

May 10, 2007

My database holds details of visits made to customers by support staff.
I am running a query which returns details of the last visit made to a customer as below with results displayed on a webpage.

SELECT Visits.Date1, customers.CustID AS customers_CustID, customers.Customer, Visits.CustID AS Visits_CustID, Visits.Visitee, Visits.VisitType, customers.RegionID, customers.NextVisit, customers.NextVisitBy, customers.Confirmed
FROM customers INNER JOIN Visits ON customers.CustID = Visits.CustID
WHERE (((Visits.Date1)=(SELECT MAX (Visits2.Date1) FROM Visits AS Visits2 WHERE Visits2.CustID = Visits.CustID)))
ORDER BY customers.RegionID, customers.CustID

SELECT MAX on Date1 is used to select the most recent (largest) date. The problem I have is that if 2 different staff members visit the same customer on the same day, the query logically returns 2 MAX date results for that customer, whereas I only want 1 result per customer. As the dates are the same I am not sure how to proceed. It doesn't matter which of the 2 results are returned. Does anyone have any ideas? Thanks in advance :)

ps - I can't perform MAX on the VisitID as the support staff don't always enter the visit data in the correct order meaning that an older visit date might have a higher VisitID than a more recent visit.

View 3 Replies View Related

Want To Query To Ask Date From And Date To

Mar 7, 2007

I have a database that has a query created, I want to refine the query to ask first for datefrom and dateto then display the relevant info between those dates.

View 7 Replies View Related

Modules & VBA :: Date Field To AutoPopulate Other Date Fields To Future Date

Oct 24, 2013

I'm trying to get my "IncidentDate" field to autopopulate two other date fields to a few days from the "IncidentDate". The other two date fields are "ContainDueDate" and "RootDueDate". I'm trying to accomplish this on my "Test CAP Form"

I tried using the following in the BeforeUpdate of "ContainDueDate" and received a complier error: expected =

Code : DateAdd(d,2,[IncidentDate])

so I removed the parenthesis and nothing happened

Code : DateAdd d,2,[IncidentDate]

I even tried redoing it in the AfterUpdate of "IncidentDate" and nothing happened either

Code : DateAdd d,2,[ContainDueDate]

I'm not sure if I'm even using the right function to get what I want.

View 4 Replies View Related

Year To Date Totals, Month To Date, Week To Date

Oct 9, 2005

Can someone tell me how to get year to date totals, month to date totals, week to dates in a query? I need to get all three for three different fields.

I was not able to get the totals with the formulas given. I received the totals for each day instead. Are there any other suggestions? I am trying to different formulas, but they are not working either. I did try doing different queries with the formulas to see if that would work.

View 9 Replies View Related

Query Date

Aug 12, 2005

In a report which asks for the users to enter a date from and date to (based on a query). Is it possible for this data to be shown on the report?

View 4 Replies View Related

Query By Date

Jan 13, 2008

Hi,

i have a query that populates all payments by date however, on lots of occasions, i have more than 1 payment per person per day so there are many payments made to 1 person in the same day. how can i sum this so my form that i am using for viewing purposes will show the total amount paid per day rather than per payment?

many thanks,

NS

View 6 Replies View Related

Query A Date

Feb 12, 2008

hi
how do i make a query that prompts a date and searches for anything that occured after that date?


thanks:eek:

View 1 Replies View Related

Date Query

May 27, 2005

Hi Everyone i am trying to run a query that gets the records within a date range, asking the user for a start date and end date. How i think the problem is because the field is date & time.

>= [Please enter first date] <= [Please enter last date]

This seems to produce all the results. I even tried just writing an actual date in the criteria.

View 2 Replies View Related

Date In Query

Jun 3, 2005

In access 2002 i'm trying to create DAO recordset from this query

SQLString = "SELECT * FROM [izposoja] WHERE (datum_posoje) LIKE " & Date

i get the following error (http://img.villagephotos.com/p/2005-4/988932/access.JPG)

The table IZPOSOJA looks like this (http://img.villagephotos.com/p/2005-4/988932/tabela1.JPG)

Basicly i yust wont to make a recordset of all records in IZPOSOJA where date is like todays date.


Well sorry for my english .... but i did my best :o

View 4 Replies View Related

Date Less Than Query

Jun 22, 2005

Hello,

I am trying to create a query where I can get all records that are 120 days past a date. I tried running a query where the date criteria is:

date()<120

But that did not work. Does anyone have a suggestion?

Thanks,
Lisa

View 2 Replies View Related

Up Date Query...

Jul 3, 2005

I need to set up an update query for stock levels. How do I go about doing this? (this should be up-dated directly from the database window)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved