Selecting By Day From Date

Dec 6, 2005

Good Afternoon,

I am currently trying to create a query that will allow me to do the following:

Prompt the user with a pop up box, asking:

Please enter start date
Please enter end date

It will then select everything from my table between those two dates (this works), and then it will ignore all those items that have a time after 7am or before 7pm (this works), but I need it to also select all entries that fall on a saturday or sunday. Now because my raw data comes in the form of (australian dates): 7/09/2005 9:39:10 AM, I am using an update query to split the field into two fields: DateFrom and TimeFrom (both are date/time fields). I then need to create another field called Day, which takes the data in DateFrom and converts the date to dddd (using Format(Date()) i assume), giving me a field with the day name in it, ie: Sunday.

I can then use that to differentiate between weekdays and weekends.... as I use MySQL and PHP more often than I do access, I have no idea how to do this.

So in short:

How do I (this is what i think i need):

Select * from tblData WHERE Day!=Sunday OR Saturday AND TimeFrom > 7:00pm OR TimeFrom < 7:00am

Select * from tblData WHERE Day=Sunday OR Saturday


Both of those selections, will of course need to be performed within my date constraits specified by the user.

Am I making sense?

View Replies


ADVERTISEMENT

Forms :: Show Date Picker On Selecting Date Field?

Apr 26, 2014

I am using Access 2007 and I have had a curious issue arise.

I like to use the Date Picker feature on all my forms to make it easier to enter dates. I have set up each table's date field property to "show date picker for dates", as per attached (.jpg)

When I use that particular field in a form, I also set up the field to allow for the Date Picker option (date picker form property sheet.jpg)

The curious part is that not every form that I set up actually shows the date picker when I select the date field. Some forms it works perfectly, others it doesn't.

View 2 Replies View Related

Selecting A Date Range For A Calc'd Date

Jan 13, 2008

I Have a table that I collect data for numerous ID's. One ID may have 5 dates that is was serviced. It is due to be serviced at set intervals which is calculated from the most recent service date. I'm calculating the next service date in a text box with the DateAdd func. =(DateAdd('m',[Cal Freq],[MaxCalDate])) with the MaxCalDate being a Max func in a different text box for selecting the most recent sevice date. I'm trying to update the [Next Due] field in the [Master Asset] table with the calculated next service date so I can do queries on items that are due service within a date range. If I do an Nex Due Expr1: in the queries to calc the next due service date then do a criteria on that Expr1: such as >=[Start Date] And <=[End Date] it states datatype mismatch? Is there a way to select a requested data range on a calculated type date/time? Or How can I update the [Next Due] filed in a different table?

View 8 Replies View Related

Selecting Records By Date

May 18, 2007

I created a personal database for keeping track of my meeting schedules. My table has Date field, Time field and Subject field. I have made several entries. On a given day, I have several meetings at different times.

I created a combo box in my form to choose the Date and see that day’s meetings. The meetings list of the date chosen by combo box should appear in my form. But here I think I made a mistake. Each record has date, time and subject. When I click the combo box, the drop down list shows the same date many times (as I have entered the same date but different time and subject for each record). How do I make the combo box display the date only once and not same date several times? Do I have to change the table set up? Please guide me.

View 1 Replies View Related

Selecting Part Of A Date

Mar 9, 2005

I have a table with the field "Date". The date is in the format mm/dd/yyyy.

I would like to create a query that selects only the year of each record, and disregards the month and day.

Is this possible?

View 3 Replies View Related

Selecting Records With A Prompt For Date

Nov 29, 2007

I have looked but have been unable to find the answer in past posts and this must be a common problem. I wish to include all records up to and including a date passed in the prompt "Criteria: <=[enter end date]". I changes the date format in the back end to short and I am in the US. However I get only dates less then the the prompted date.
Thanks for any help
Jim

View 10 Replies View Related

Capturing A Date By Selecting A Button

May 16, 2006

Hello all.

We have product orders that get held in process for a variety of reasons (contract not signed properly, missing drawings, etc). It is our Order Processing Rep's (OPR) responsibility to review the requirements, follow up with the salesman, get the proper documents together and get the order released for product assembly. We capture the date the order was originally held with a date-formatted text field, and then we later capture the release date with a similar field, entered by the OPR.

The OPR is graded on the average time it takes to release a held order. Since they can enter text into the release date field, they can fudge their metric. I'd like to fix this by having a button on the form say "Order Released" and, when it is selected and verified by a second click, the date that occurs is captured in the table under "Release Date" but is hidden from the OPR's ability to edit it.

Can this be done? Any help is greatly appreciated!!

Tom

View 5 Replies View Related

Selecting A Short Date In A Range

Aug 11, 2006

I have a field defined as Date/Time Short Date which shows values like 8/01/2006, 8/10/2006, etc and I need to select dates in a range.

However, when I write a query with code like:
SELECT AMOUNT
FROM INVOICE_TBL
WHERE PICK_UP_DATE BETWEEN 08/01/2006 AND 08/31/2006

It doesn't pick up the records I also tried >= <= with the same results.

Any help would be appreciated.

Thanks,
Steve

View 2 Replies View Related

Modules & VBA :: Selecting Date Given In A Form

Feb 16, 2014

I want to select a subset of table called TRP. It should extract me all records

which got the property Valid to date < enddate.

enddate is a date selected by the user in a form .

Somehow it doesn't recognize the enddate in the following :

Code:
Public Sub test()
DoCmd.SetWarnings False
DoCmd.RunSQL "SELECT TRP.* INTO [TEST] " & _
" FROM TRP " & _
" Where TRP.[Valid to] < enddate"
DoCmd.SetWarnings True
End Sub

View 14 Replies View Related

Selecting Week Of Records From This Weeks Date

Jun 27, 2005

Hi,

I am trying, and getting nowhere...so would appreciate your help... to create a query where records with dates from 8 weeks, or two months ahead appear in the query.

That is I have contracts ending in two months in my table and I want to run a query on who I should call now to renew contracts.

I have a contract end date in table.

I have tried
>=DateAdd("m",2,Now())<=DateAdd("m",2,Now())
only to get all sorts?

>=DateAdd("d",60,Now())<=DateAdd("d",68,Now())
only to get all sorts of things appearing?

Anyone that could help would be great.
I have contact info in a TBLCONTACTS and linked to TBLMOBILES via COMPANY_ID

Thanks heaps!

C

View 2 Replies View Related

Selecting Data From Date And Viewing In A Table

Jul 19, 2006

in tblquery i have a field date.

it its formated to =Date()

i also have two more fields

1- month
2- year.

what i want is to extract the month and year from the date field.

but the thing is, i was it to be visiable when you are in the table "view".

but i dont know how to format this.

View 9 Replies View Related

Queries :: Selecting All Date Fields Between Two Dates?

Jul 31, 2013

I'm trying to select a range of relevant dates for an amortization calculation (see my earlier thread on this subject here), but I'm having a bit of trouble making the SQL work.

I have a table called "t_AllMonths" that has only one field: MonthStartDate which contains the first day of the month for a very wide range of months over something like a ten-year period. I'm calculating amort for assets which will be amortized for some subset of those months (defined by the asset's Amort Start Date and Amort End Date). Further complicating matters, the amortization may be suspended during certain "hiatus" periods when the asset it not planned to be in use, and may differ by which business units make use of the asset.

So, I have three tables.

Table: t_Assets
Fields: AssetID (autonumber; primary key), Asset_Name, Asset_Cost

Table: t_AllMonths
Fields: MonthStartDate

Table: t_AmortPeriods
Fields: AmortPeriodID, AssetID, Amt_Period_Num (which I don't expect to use in this), StartDate and EndDate

Right now, I'm just trying to pull the range of dates between the earliest amort start date and the latest amort end date. (Min of StartDate and Max of EndDate, respectively) for a given AssetID.

My sql looks like this:

SELECT t_AllMonths.MonthStartDate,
Min(t_AmortPeriods.StartDate) AS MinOfStartDate, Max(t_AmortPeriods.EndDate) AS MaxOfEndDate,
t_Assets.AssetID
FROM t_AllMonths,
t_Assets INNER JOIN t_AmortPeriods ON t_Assets.AssetID = t_AmortPeriods.AssetID
WHERE ((t_AllMonths.MonthStartDate) Between [MinOfStartDate] and [MaxOfEndDate]);

I keep getting an error message that reads "Run-Time Error 3122: You tried to execute a query that does not include the specified expression MonthStartDate as part of an aggregate function."

View 5 Replies View Related

Modules & VBA :: Selecting Date Range And Calculate A Sum?

Aug 8, 2013

I want to select a date range from "Production" table where it agrees to the Dept_ID too. And then calculate the summation of the columns "hours", "produced" & "waste" of that particular range selection.

This is my code:

Code:
Option Compare Database
Private Sub cmdCal_Click()
Dim sql As String
Dim rs As Recordset
Dim qdef As DAO.QueryDef
Dim hours, waste, produced As Integer

[code]....

But it returns nothing, When i remove the errorHandler, it says that no records were found.

View 2 Replies View Related

Selecting Field Based On Effective Date

Mar 27, 2015

I am trying to create a query in Access 2010. I have 2 tables BU and Color. BU has employee ID, the BU the employee is assigned as of a date. The Color table has the employee ID, a color and a date. Based on the date in the Color table, I want to select the BU from the BU table. The query should bring in the BU that the employee was assigned to as of the date in the Color table.

For example, if emplid 12345 was hired into BU abc as of 12/31/2013. Emplid 12345 was transferred to BU xyz on 1/15/2015. In the Color there are 2 rows for 12345. One with a date of 12/31/2014 and one with a date of 2/15/2015. I want the query to return abc on the row with the 12/31/2014 date and xyz on the row that contains 2/15/2015.

Attached are the two tables. I have highlighted the expected results in yellow on the Color spreadsheet.

View 1 Replies View Related

Export Data By Selecting Date Range

Oct 27, 2013

I would like to create a excel file to let the user to input the following column.

Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "

I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure

For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?

if the data is very large , I want to create a access db to store it , how to do it ?

View 5 Replies View Related

Selecting Based On A Text Field And Most Recent Date

Jul 16, 2007

Hello, I'm working with SQL and databases in general for the first time, and was wondering: how would I select just the most recent entry for each device? my data looks [roughly] like this:

device1 data_1a 15.2.2000
device1 data_1b 15.2.2001
device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3a 15.2.2001
device3 data_3b 15.2.2002

So what I'm looking for is:

device1 data_1c 15.2.2002
device2 data_2a 15.2.2000
device3 data_3b 15.2.2002

Thanks for any help you can offer!
-Eric

View 2 Replies View Related

Reports :: Export Data By Selecting Date Range

Oct 27, 2013

I would like to create a excel file to let the user to input the following column.

Date No. of success No. of failure
==== ============ ==============
" " "
" " "
" " "

I would like to have a function that can allow the user to select the date range and its total no. of success and no. of failure. For example , the user select from 31-03-2001 to 31-12-2012 , then output the report which show the total no. of success and no. of failure in this date range , is there existing tamplate that I can use ? if no , what is the simplier way to do it ?

If the data is very large , I want to create a access db to store it, how to do it ?

View 4 Replies View Related

Selecting Only The Best Three

Nov 13, 2007

Hello All,

I have a small problem which I feel I should be able to solve but the hours of trying are proving me wrong!

From a choice of 16 events, competitors can enter as many as they like providing they are eligible. For each event they receive performance marks which are duly stored in a table. Some compete in 1 event others in 5 or 6. (I do not have a control showing ‘number of events entered’ and have tried several options, all unsuccessful, to create a ‘count’ of events entered. Is there a way?) My main problem is, from each individuals’ records, how do I select only the highest 3 marks from the classes each individual entered.

I’d be very grateful for any advice/guidance.

Bomac.

View 4 Replies View Related

Selecting Record

Apr 20, 2006

I have a table with customer details, probs over 3000 customer, and i need a method of selecting the customer in a form by their surname. Currently i have a combo box, but as you can imagine, this is not practical with so many records. anyone think of any other way to do it?

View 4 Replies View Related

Selecting First 10 Records

Apr 10, 2006

Hi,

I have a query that gives an out put of 'worst offenders'. these offenders are in order of on field ascending (number of entries made per week) and another field descending (total sales).
It is by the combination of the sorting of these fields that we have an ordered list of offenders.

For my report i want to only see the top 10 records of this sorted list.

so for this selection i simply want to select the [B]first 10 records [B]of the ordered list. I cant make a selection by any one particular field(as explained above)

i have looked into the 'select top' functions and 'dfirst', but I dont think they'll work for me.

Any ideas?
Slighlty confused,
Rosxx

View 3 Replies View Related

ARGHHH Selecting All

Jan 17, 2007

Can anyone tell me why sometimes when i'm using access all of a sudden when i click in cells, control boxes etc that i select everything and i cant position the mouse with a string of text with selecting all of it.


VERY frustrating.

cheers,
Spinkung :confused:

View 2 Replies View Related

Selecting Records

Jun 11, 2007

hey guys, im having one last problem with a report im making. just want to thank boblarson, Rich and Dennisk for all the help they have given me upto now, i have added to your reputation guys, thanks!

My problem is as follows:

I have a form for my products and a subform for the suppliers, each product can have many suppliers.

I need to have a report of products to send out to customers, so i created a check box to "tick" if i want to include it in the report. So far so good, however, the report shows each product several times for each supplier :(

can anyone tell me how to select only the supplier with the lowest supplier price for each product

Cheers guys

View 9 Replies View Related

Selecting One Instance

Dec 16, 2005

Hi guys,

Still trying to get used to this Access lark.

A table contains a column with numbers i.e - 1,1,3,4,2,2,2,3,4,4

If i just want a query that shows one of each i.e - 1,2,3,4

what do i need in the criteria line?

Thanks in advance

View 2 Replies View Related

Selecting Rows

May 17, 2006

Hallo Everyone,

I have a small problem in query selection. I have a query that selects values based on a criteria. [value from a combobox]. The problem is that i need to select all the rows if the value of the combobox is empty.





aravind.s

View 1 Replies View Related

Selecting Most Current Value

Jun 8, 2006

I am working on a query to select the most current value for a given record
based on dates. The record has an associated cost for a given year say 2000.
For years 2001 to 2006 there is no cost. I want to use the cost in 2000 for
the most current cost even though my date criteria specifies say 2002. Can
this be done within the select query?

Thanks

Tony

View 2 Replies View Related

Selecting Dates

Jun 29, 2006

Quick one for you geniuses out there!

I would like a command button to print records from a select query displaying dates for the past 2 days but not for todays date.
e.g. today is the 29th, I want to print records for the 27th & 28th.

Data is being recorded hourly and kept for 2 months so there will be new and old data that I don't want printed.

Thanks for the help!!

View 1 Replies View Related







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