DatePart In Query Use

Sep 5, 2007

Hi

Can anyone advise on the following query problem:

I have been using the function DatePart to select records from a field of a datetime type, and had seen somewhere (an old Access refrence book?) that it is possible to use DatePart to select more than one time setting, but I have not found out if this is actually possible, or the syntax for it if it is.

In selecting a time range, I would like to use a single query (eg Between 0900 and 1730) to represent a working day, with the criteria "hn" in the datePart function. At present I have to use two selection criteria to achive this (h Between 9 and 16; h = 17 AND n <30)

Ay help/suggestions, and especially an example, would be greatly appreciated. I am using Access 2003.

View Replies


ADVERTISEMENT

DatePart And Between...And

Dec 13, 2006

Have been unable to find any solutions for this in the forum or the help files in Access.

Project has a [cutindate] and an [enddate].
A [flatamount] ($$ savings amount].

I am calculating the month savings by dividing the [flatamount] by the number of months between [cutindate] and [enddate] using DatePart().

With the help of someone on the forum I have a table "MonthOffset" which just has the numbers 1-12 in the [MonthOffSet] column. Using this, I can get my query to take the monthly savings amount and disperse it to each month for the next 12 months beginning with the [cutindate]. This was my original direction. They now don't want to see it dispersed over 12 months, but rather just show savings between the [cutindate] and [enddate]. I can get the correct monthly savings in my query, but it is still showing it for 12 months, i.e. [flatamount] is $50k, monthly savings is for 5 months, $10k showing for each of 12 months. I can just use this, then make another query to only show the monthly savings between the months I need but I would like to get it done in one query.

My query:

ProjectID CutInDate SavingsMonth
1 2/1/2006 2/1/2006
1 2/1/2006 3/1/2006
1 2/1/2006 4/1/2006
Through 1/1/2007

Project ID has a cut in date of 2/1/2006 but the savings will only last until 8/1/2006.

Has anyone had to do this before?

I have tried this in the criteria of my SavingsMonth field:

[MonthOffSet] Between DatePart("m",DateValue([cutindate]) AND DatePart("m",DateValue([enddate])))

But keep getting the message: You did not enter the keyword And in the Between...And operator. The correct syntax is "expression [Not] Between value1 and value2"

Does anyone know if this can be done?

Thanks,

Toni

View 3 Replies View Related

DatePart Help Please

Oct 15, 2006

If I can get any suggestions I would appreciate it. I need to run a report on total number of calls, daily and I need to sort it by week. I used the Sorting and Grouping options and I can sort them. what I need to accomplish is to have the week start on a Monday instead of the default Sunday. When the first data of the week does not start on a Monday, the heading date of that week will be the date of when it is first recorded. So, if i have no calls on Monday or Tuesday but there are records for Wednesday, it will have Wednesday date as the heading for that week. I went through the help online for Access and it mention I can create expressions to help using the Datepart heading. But, it didn't recognize the expression. Does anyone know how to solve this problem?

View 3 Replies View Related

This Is Odd...using An Iif With The Datepart Function

Jan 19, 2007

Here's the deal, I have a table that has a flag in it. It's either a 1 or a 0.

Expr1: IIf([Is it?]=1,[REQ DATE],IIf([Is it?]=0,(DatePart("yyyy",[REQ DATE])),"ERROR"))

If it's a 1, it displays the full date. If it's zero, it's supposed to display the year of the date.

The first part works fine. If it's a 1, it shows the date. However, if it's a 0...it shows a date not even remotely close...and they're all the same. 06/28/1905.

View 2 Replies View Related

DatePart Function

Jun 2, 2007

I am using a DatePart function to get the week number for various dates in my project (DatePart("ww",[date],vbsunday)). Is it possible to set the firstday setting of the function (vbsunday) by referring it to a field in another form. I have tried but get an error. I am hoping to achieve this because the database will be distributed to various agencies which have different first day of the week for their schedules. I want to avoid re-writing the code for each agency. Any help would be greatly appreciated.

View 3 Replies View Related

Can You Use Multiple Weeks In DatePart

Jun 27, 2005

I'm using the following in a query qwhich allows me to enter the week number as the criteria:

DatePart('ww',[Visitdate],2,2)

This works fine , no problems. What I would like to know, is it possible to enter muliptle week numbers in the criteria, say 14 16 21 to give me output for those weeks, I have tried different separators to no effect ie. : and ; It may be that it simply is not possible but it would be extremely useful if ti was.

View 12 Replies View Related

Datepart Function Problem

Jan 19, 2006

Hi,

I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.

For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"

I've tried using the datepart function i.e. datepart("mm-yyyy",[date but to no avail.... can anyone help???

Many Thanks in advance

Jason

View 1 Replies View Related

Datepart Function Problem

Jan 19, 2006

Hi,

I seem to be stuck when trying to get "mm-yy" from a "dd/mm/yyyy" field.

For example I want the query to bring back "Jan 05" or "January 2005" from "01/01/2005"

I've tried using the datepart function i.e. datepart("mm-yyyy",[date]) but to no avail.... can anyone help???

Many Thanks in advance

Jason

View 2 Replies View Related

Datepart To Show Month

Sep 6, 2006

I know this must be simple…

How do you show the actual month from the results of using the datepart function in a query

Query xMonth:DatePart("m",[ContractStartDate],[MaturityDate])

I want to show Jan, Feb march not the integer value.

Cheers

View 1 Replies View Related

DatePart Function, String Functions

Nov 1, 2005

Hey all I have a I was wondering if you knew how I can get a part of my datepart function out, I have taken the year from the the date with the date part function and now I need only the 05, so what can I do to get it out? Thanks MY CODE: Dim Num as string Num = DatePart("yyyy", STRDATE)
I looked up the code for a right function and it is suppose to work on a string, so I tryed it on this. My CODE:
Dim Year As String
Year = RIGHT(Num, 2)
I obtained a type mismatch WHY???

View 7 Replies View Related

DatePart - Can I Change The Default «firstweek»

Apr 28, 2006

This is my first thread so be gentle.

I curently have a query based on a payments table. In the payments table there is a field called date (when the payment was processed).

I want to be able to display, in my query, the quarter that this payment was made in. It is based on financial quarters so quarter 1 starts on April the 1st.

The default of firstweek seems to be January the 1st. Can I change it to April the 1st - if so how?

Many Thanks

View 3 Replies View Related

DatePart Will Only Accept 6th Jan 2005 As Week 2

Jun 2, 2005

When using 'ww' as the criteria in the DatePart calculation it will not accept 1 for 6th Jan 2005 which is week 1, you have to enter 2 and it will select it, in fact all the weeks so far in 2005 are out by 1. To cure it you can add on -1 to the query and it works fine until you go back to the previous year and it screws up those dates.

Any ideas?

View 11 Replies View Related

Forms :: DatePart Function To Filter Out Sales Tax By Quarter

Apr 26, 2013

Was wondering if there is a way to incorporate the DatePart function in the below statement to filter out sales tax by quarter? I have two drop down boxes that filter the year and the state but the below only totals the tax for the year and state. I am trying to add 4 text boxes to show the quarterly break down of sales tax.

The below text box is in a form pulling the data from a query.

Text Box

=DSum("[SalesTaxCharged]","[Sales Tax Calculation Qry]","Year([Order Date])=[SelectedYear] And
[StateProv]=[SelectedState] And Not [Tax Exempt]")

View 6 Replies View Related

Query In Forms Question (printing A Single Selected Query Record From A Form?)

Oct 19, 2004

What is the best way to impliment a query in a form so that the user can view the query records, and have the option to print or save the selected record using command buttons?

I tried subforms but I could not get the command buttons to work in the subform after it went into the form, it wanted to print the entire form instead of the selected record from the subform.

So in a nutshell I have 3-4 queries that are built, and I want to have them show up on my form in a format that the user can scroll through the results and select a single record of the results and then print or save that individual record from the form, if such a thing is possible.

Thanks in advance

Todd

View 1 Replies View Related

Queries :: Join Results Of Unmatched Query With Matched Query To Include Null

Mar 24, 2013

I am trying to do the good 'ol sales report (query) to include customers with no sales.

I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)

If I run a query to show customers (in the customer table) with account numbers, that works

An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).

How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?

I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?

View 3 Replies View Related

Queries :: Opening Design View Of Append Query Without Timing Out Source Query

Mar 4, 2015

In some cases I create pass-through queries and use these in an Append or Make-table query to bring data locally.

All is well and fine until source data changes and the pass-through query runs too long and times out.

If needed, I can extend the timeout value in the Parameters of the pass-through query no problem, but when I try to open the Append or Make-table query in Design view to do the same, the pass-through query is first triggered and then throws the timeout, and I cannot access the Design view of the Append or Make-table

Is there a way to open an Append or Make-table query in Design view without invoking the source query?

View 1 Replies View Related

UNION Query MS Jet Database Engine Cannot Find The Input Table Or Query.

Oct 28, 2005

Hello All,

I'm trying to run a UNION query that joins five queries through a MS WorkSpace into a DAO.recordset in VB. I'm pulling the data from a SQL Server Database through VB in Access. I'm attempting to open a recordset with a query passed to it as a string. The query is below. For some reason, I'm receiving a message: "MS Jet database engine cannot find the input table or query. Runtime Error 3078".

Here's what's puzzling. When I run a single query without any UNION statement, the code finds the table and runs fine without error, but anytime I join two or more queries with a UNION statement in the VB, it gives me the error.

I've executed the same UNION query in both Access Query Builder and SQL Server's Query Analyzer and they work fine in both environments. It's only when I call the query from a DAO.Recodset with VB that it causes this problem. The following is a sample of the UNION query joining two of the five queries. Does anyone have any idea what could be the problem? The following query executes in about 5 seconds so I don't think there's a "time-out" issue. I'm thinking that the UNION statement may be the culprit. Maybe there's another way to approach joining these separate queries? Any help would be most appreciated. Thanks.

SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((CategoryID) = 'HCPROD') AND ((BrandID)<>'CSS')) AND (((BrandID)<>'1356')) AND (((BrandID)<>'1400')) AND (((BrandID)<>'1551')) AND (((BrandID)<>'555')) AND (((BrandID)<>'66'))
AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID
UNION
SELECT SalespersonID, Sum([SlsPrice]-[RtnPrice]-[SlsDiscnt]+[RtnDiscnt]) AS fldPrice FROM MyTable WHERE (((Source)='d') AND ((DistrictID)='01') AND ((ProductID) = '0029800')) AND (TransDate >= 07/01/2005) AND (TransDate <= 07/31/2005) GROUP BY SalespersonID

Set wrkJet = CreateWorkspace("", "pw", "", dbUseJet)
Set db = wrkJet.OpenDatabase("DW", _
dbDriverNoPrompt, True, _
"ODBC;DATABASE=DW;DSN=DW2")
'Set rs1 = db.OpenRecordset(strSQL)

View 9 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)

I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.

I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.

View 5 Replies View Related

Queries :: Update A Query Based On Results From Another Query Using Count Function

Apr 2, 2013

I run a physical therapy office and patients come in for treatment either 3, 4 or 5 times per week. My database is used to track these frequencies (among other things).

I have 3 queries which count how many patients come in 5, 4 and 3 times/week.

In my main table I have fields called "how many 5's", "how many 4's" and "how many 3's".

I have tried to design an update query which will update those fileds in my main table to reflect the counts in the 3 queries mentioned above.

(I'm not using SQL view, I'm using the query design view)

In the "update to:" row, I use the Build function and locate the count I'm looking for.

Problem: when I run the query I get the error: Operation must use an updateable query.

View 3 Replies View Related

Queries :: Crsosstab Query Doesn't Recognize Expression In Source Query

Aug 12, 2015

I have a query where these are the fields:

ProductRevType
RevLag
RevFlowThru
CloseMoYr
ProjRevDate
CurrentMRC
ProjRevMRC

The ProjRevMRC field is an expression that reads:ProjRevMRC: IIf([ProjRevDate]>=DateSerial(Year(Date()),Month(Date()),1),[CurrentMRC]*[qry303a_ SFADetailMRC_ONLY]![Rev Flow Through],0)

When I run the query, it works perfectly, but when I created a crosstab query to show totals by month, I wanted the totals to be zero for the months less than the current month. Is there a way for the crosstab query to execute the expression and put zeroes for those months?

View 4 Replies View Related

Queries :: How To Insert A Prompt For Number In A Query To Calculate Against Another Field In Query

Jul 15, 2014

I have a field that is giving me the number of business days between a period of time and then I want to subtract that number - the person's PTO time to see the actual days they were available...when I simply type the number in (see below) it works great but I want to set up a prompt that will ask me how many PTO Days to calculate as it will be different for each person I am quering...is this possible?

View 9 Replies View Related

Forms :: Update Form / Query Without Updating Underlying Tables To Query

Jul 23, 2015

I have a form which will be used as the basis to print a label.

It is bound to a query and when I open the form I pass over a 'where' condition to return 1 record. I then use the query to produce a report/label.

What I want to do is to update the form/query without updating the underlying tables to the query.

View 14 Replies View Related

Blank Fields In Crosstab Query Based On Previous Query

May 31, 2006

:confused:
I am trying to help someone with a complex problem (so it seems to me) but I will first ask about what should be a simple thing....

First goal: to COUNT the number of times a TYPE of visit is made.
There are several different TYPEs but only interested in tracking 2 of them.

When a crosstab query is created - if one of the 2 parameters are not "met", a blank is returned. I have been reading posts about using NZ and IIf IsNull, etc to get past that - but none of them make any sense to me and the Access help suggestions do not work. Hope someone can make it clear with this information: (can't give more specifics to keep privacy intact)

The SQL was written by Access not by me. :)

Here is an example of the Crosstab SQL (which is using a previous query):

TRANSFORM Count([qryTest2.TYPE]) AS CountOfTYPE
SELECT qryTest2.CID
FROM qryTest2
GROUP BY qryTest2.CID
PIVOT qryTest2.TYPE;

-----------
qryTest2 SQL: (Grouping by to remove dups)

SELECT DISTINCTROW tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
FROM tblM LEFT JOIN tblC ON tblM.[M#] = tblC.[M#]
GROUP BY tblM.CID, tblM.[M#], tblM.LNAME, tblM.FNAME, tblM.YMDBIRTH, tblC.ClDOS, tblC.TYPE
HAVING (((tblC.TYPE)="Out" Or (tblC.TYPE)="In"))
ORDER BY tblM.CID, tblM.LNAME, tblM.FNAME;

Thanks for you time! :)

View 1 Replies View Related

Show All Records From One Query And The Sum Of A Field In A Related Query Record

Mar 8, 2005

I'm building a report for annual software license renewals. The report data source is a query that combines the customer information, their computer information, and the licenses purchased for that computer. I am having no trouble with the form displaying the customer info page, then a page with the computer info at the top and a list of licenses purchased for that computer underneath.

That would be great, if that's what my boss wanted. However, she wants the whole list of available licenses displayed on each page, in the event someone want to purchase additional licenses with this year's renewal.

I'm trying to figure out how I can set up a query/report grouping to do that. I've tried making a new query, relating the qryLicense!licenseID to the qryPurchase!purchLicense and setting the relationship option to show all records from qryLicense and only those related from qryPurchase. I added the qryPurchase!purchCPU field to my query, hidden it, and set the criteria to “=1” (the computer ID of one of my dummy computer records). I also have a Sum of the qryPurchase!purchQty field included in the new query that I want to display the total number of that particular license purchased (and 0 if there are no corresponding records). All fields except for the quantity field are set to Group By.

What I’m getting from this is simply a list of the licenses purchased for that computer, not the complete list of licenses available showing the quantity purchased where applicable.

Can someone see where I’m going wrong?

Slaughter
slaughter at mizzou dot edu

View 9 Replies View Related

Queries :: Query To Insert Missing Rows From Another Query / Table

Oct 8, 2013

I am using an Access 2010 DB to keep track of a schedule. Essentially, at least one person needs to be signed up to work for every hour of every day in a week.

Tables:
Days with 7 records
Hours with 24 records
Workers with as many people that sign up to work the different hours
Schedule signifying the worker, day, and hour which are signed up.

As of now i have a query that relates these results and gives me a line detailing the worker/time information for the slots that are signed up for.What I'm TRYING to do is to create a query that gives me BLANK worker info when there is no one signed up for a particular hour.Currently my Schedule table has the following:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
16 | 5 | 15


What I'm looking to do is have this table matched up with another table (or query) that provides every combination of day/hour. When an day/hour combination is skipped, the query will be able to "fill in the blank" with a row. Like this:

WorkerID | DayID | HourID
----------+---------+--------
1 | 5 | 12
4 | 5 | 13
| | 14
16 | 5 | 15

View 2 Replies View Related

Queries :: When One Sub Query Has No Records Entire Main Query Is Blank

Oct 1, 2014

I have a query with three sub queries, all returning a number from different tables. But when any one of the sub queries returns a blank, the entire main query is blank.

How do I stop this from happening? I have tried NZ() on the sub queries and on the main query, but the blank still happens.

View 1 Replies View Related







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