Over 1 million tech questions and answers.

Help with date SQL query in ms query

Q: Help with date SQL query in ms query

Hello,
I am pulling information from another program into Excel via Microsoft Query and one of the columns I want to pull in involves a date a payment is due. I am wondering how to write the query to have it always pull today's date +1 so essentially it would always pull tomorrow's payments due. Thank you!

I tried to add Where date = DateAdd("d",1,date()) but I am getting this error: "general odbc error." What does this mean and is there a way to fix it or alter query?

RELEVANCY SCORE 200
Preferred Solution: Help with date SQL query in ms query

I recommend downloading and running Reimage. It's a computer repair tool that has been proven to identify and fix many Windows problems with a high level of success.

I've used it in the past to identify and fix everything from blue screens (BSOD's), ActiveX errors, corrupt files and processes, dll/exe/sys errors, recover lost memory, Windows update problems, defragging, malware removal etc.

You can download it direct from this link http://downloadreimage.com/download.php. (This link will automatically start a download of Reimage that you can save to your computer.)

A: Help with date SQL query in ms query

Hello, in Access Queries you can just use
Date + 1
so perhaps it will work for MS Query.

Read other 1 answers
RELEVANCY SCORE 94.8

NOTE: I could not post code because I must be really careful to protect proprietary information.

I work in an assembly plant and I've designed an Access 2003 program to deliver a set of 4 crucial parts to the floor all within a very narrow degree of tolerance. Further, the program integrates FIFO.

Ive done this by assigning each of the essential measurements codes and then running a series of queries:

Query 1 identifies the oldest Part A by sorting the available parts by the date they were inspected then by serial number (to break ties) and delivering the TOP 1.
Query 2 identifies the oldest Part B that is compatible with that Part A using the same method.
Query 3 identifies the oldest Part C that is compatible with that Part B.
Query 4 identifies the oldest Part D that is compatible with the other end of Part A. I have the parts in this order because we can begin assembly if we don't have a Part D on hand, but we must have Parts A, B, and C.
Finally, query 5 simply pulls the information from those queries into one location for a report.
As long as we have enough stock, life is good; but the moment a match cannot be made, it blows up. I cannot figure out how to make it go back to choose the next Part A. I need to figure out how and where to add code to each query to tell it that if no stock is compatible with the Part A produced by Query 1, to go back and pick the next one in line.

Can anyone help? Is the way I've gone about matching parts completely... Read more

A:Access 2003 – Query Based on Query – need code to rerun earlier query

Read other 11 answers
RELEVANCY SCORE 92

Hi there,

I'm very new to access and I hope I will get the simplest solution on my question below.

I have a table named "PurchaseOrder" and I'd want to create a query that will display the invoices that will due.

Eg:

Table1
PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
8697 D 888 $88 18-Oct-2010

I want to create 2 queries.

The first query will display all the invoices that will due 5 days before the exact due date.
Eg:
The invoice due date is 15-Oct-2010. The query will display information on 3241, 6589, and 2234.

For the second query, I want to display all the invoices that have exceeded the due date. The information on the respective invoices will be transfered from the query as mentioned earlier to the new query.

The scenario is as below:

Date: 16-Oct-2010

Query1

PONo Customer Quantity SalesPrice InvoiceDueDate
3241 A 421 $12 17-Oct-2010
8697 D 888 $88 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
Date: 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
8697 D 888 $88 18-Oct-2010

Query2

PONo Customer Quantity SalesPrice InvoiceDueDate
6589 B 521 $19 14-Oct-2010
2234 C 987 $32 15-Oct-2010
3241 A 421 $12 17-Oct-2010
Hope someone can help me with this. Really appreciate and hoping for ur help.

 

A:Query Access 2007 (how to transfer data from a query to another query)

The first query needs the following first Criteria Row of the InvoiceDueDate
between date() and date() + 5
The second query needs
< date()

I haven't tested it, just worked from memory. so let me know how it goes.
 

Read other 2 answers
RELEVANCY SCORE 86.4

First the background: We are gradually deploying Office 2007, but not everyone in the agency has it. I have both 2003 and 2007 installed on my system, but I do my work in 2003, and none of our databases have actually been converted to 2007 format (at least, none that I work with). There are occasionally people who use 2007 when viewing databases, but I don't know if any of the people who are working in this particular database are using 2007. The problem is reproducible in 2003 and so I've been trying to fix it here. The query behind the problem report used to work. It has been a problem for several months (it is run monthly, and every month for the past five they've had problems with it) and I've finally gotten to the root of the actual issue.

The purpose of the report is to generate invoice for outstanding loans. The DateNextPayment is a calculated field based on the payment plan for the loan. It is calculated using a function in the query named qryGetSitesDDInvoices1. This is the function that is called (I did not write this function, I'm supporting this db long after the original writer has left):
Code:
Function DateNextPayment(PaymentSchedule As String, DateFirstPayment As Date, Balance As Currency)

On Error GoTo Handle_err

Dim Date1 As Date
Dim Date2 As Date
Dim Date3 As Date
Dim NextQtrDate As Date

'The code adds one month to datenextpayment at start of each month. Or yearly or weekly

... Read more

A:Solved: Date Parameter Failing in Query from calculated date field using DateValue Ac

Read other 16 answers
RELEVANCY SCORE 85.2

Hello, I am having trouble finding the previous 2 days totals in a query. I have a totals query with the following fields:
JobID - Criteria: Between 500 And 599
WorkType
JulianDate
Batch
TotalProcessed - calculated field, grouped on Sum. sums field entry
ProdDate - Short Date - Grouped on Where - Criteria Date()-1 Or Date()-2

Today's Julian Date is 199 so query should give me Julian Dates 197 & 198, but instead returns Julian Dates 188 - 196.

Thank you for your help!
 

A:Solved: AC2007 Find Date()-1 And Date()-2 in Query

The criteria that you should use for a date period is
Between Date()-1 and Date()-2
 

Read other 2 answers
RELEVANCY SCORE 81.6

I am building a database in msaccess and I would like to build a query that has a criteria that will let me enter only the month and year (i.e. jan-05) and it will find all the records that have fit that criteria and ignore the day. Could someone tell me how to do this?
 

A:date query

Read other 16 answers
RELEVANCY SCORE 81.6

I am running query where I want Price data from price table for particular month.Here is my
tables.

See the attachment for tables.


Customer table has Date and Product code and Price table has date, code and price.I need query that will give me price from Price table based on Product code in Customer table.Example: In customer table product 100 sold in Jan , May and Aug. Price table has price for code 100 for Jan which got changed in Feb. then got changed again in April and so on.....I need a query for product 100 which got sold in may in customer table but available price in price table is in April. Query should pick first if the record available in same month (may) in price table if not in may the then the previous month like April.Thanks for help in advance.
 

A:Date query

Read other 6 answers
RELEVANCY SCORE 80.8

Hi

I am in the middle of a Biorhythm VB program and am wondering how to use Date Picker

Currently I am working on a phase of 18 days, so I assume MOD18 will come into it somewhere (you can tell I am a beginner)

My Date Picker seems to go back to Jan 1st 1601

On that day, the 18 day cycle is on day 16 of the 18 day cycle.

So what I need to know is - if I use Date Picker to choose a date of, say, March 9th 1967 - how can I get it to calculate what day of the 18 day cycle March 9th 1967 falls on (still assuming that Jan 1st 1601 is day 16 of the 18 day cycle)

AND is there a site which simply explains how to use DP ?

Thanks for looking

Mike
 

A:Date Picker Query

What exactly is Date Picker? I know that VB6 has a ton of built-in functions for managing and formatting dates and times, but I'm not framiliar with one named this.
 

Read other 2 answers
RELEVANCY SCORE 80.8

Access 2003
I am trying to query a table that contains only dates from 1 month, for example 09/01/2009 thru 09/30/2009 table field name is [ACTFINISH]

How do I write the query to return only the month and year, for example 09/2009 ???

Thanks, Joe
 

A:Solved: Query date

Read other 9 answers
RELEVANCY SCORE 80.8

I am having trouble getting a query to work! I have dates stored which are when a service was last completed eg. DateServiced = 10/07/02. Services have to be completed yearly. I need to enable the user to enter a start date and an end date as to when services are due to be completed again. eg 01/07/03 and 31/07/03 for services due in July. This needs to pull up all records where the DateServiced was in July 2002. I have tried to have an expression in the query which calculates the due date (as below) and then query on the value of this field. It brings up incorrect records. Hope this makes sense!!! I am confused!!!! Please help!!!

Here is the SQL:

SELECT tblHousing.strUPRN, tblHousing.dtmDateServiced, [dtmDateServiced]+365 AS DueDate
FROM tblHousing
WHERE ((([dtmDateServiced]+365) Between [Enter start date] And [Enter end date])) OR ((([dtmDateServiced]+365) Is Null));
UPRN is the primary key and is a reference code.
 

A:Date Query in Access

Read other 10 answers
RELEVANCY SCORE 80.8

Hello,
I am pulling information from another program into Excel via Microsoft Query and one of the columns I want to pull in involves a date a payment is due. I am wondering how to write the query to have it always pull today's date +1 so essentially it would always pull tomorrow's payments due. Thank you!
 

A:SQL date query in Excel

...
Where date = DateAdd("d",1,date())
...

I skimped on the initial SQL statement but DateAdd("d",1,date()) will return the next day in the date.
 

Read other 2 answers
RELEVANCY SCORE 80.8

I need to set up a query that will look for the latest date so I have a single record for each date. Can anyone help?

Basically, I have duplicate part numbers - each having their own date revision.

123456 Rev. 3/3/03
123456 Rev. 4/4/03
888888 Rev. 5/5/03
999999 Rev. 2/2/03
999999 Rev. 6/6/03

After the query, I want these results:

123456 Rev. 4/4/03
888888 Rev. 5/5/03
999999 Rev. 6/6/03

Thanks,
mslieder
 

A:Query on the latest date

Read other 7 answers
RELEVANCY SCORE 80.8

Hi,

I'm doing a database at the moment and I'm going to base a report on a query I'm doing. I want to know how many people this business has served over a year.
Can anyone tell me how i can filter down results so that they only fall between a certain year?
Eg i have their arrive data and then add criteria to the query to then filter down those people who have come in 09 from those in 08.
Hopefully that make sense
 

A:Access query date

Read other 16 answers
RELEVANCY SCORE 80.8

In Access I want to view all the records for one week. I was hoping this would work. "Between [Enter Beginning Date] and [Enter Beginning Date]+6" What am I missing? The goal is to only have to enter one date.
 

A:Access query date

mcookaz, it is better to use a form to either enter a date or select a date from an ActiveX calendar, you then use
Between forms![Formname]![Enter Beginning Date] and forms![Formname]![Enter Beginning Date]+6

It can be used on as many queries as you like as well.
 

Read other 1 answers
RELEVANCY SCORE 80.8

Is there a way to select all records of a specific month & day, regardless of the year in an MS Access database? For example: I may want to select all records occuring during the week of 5/24 through 6/1. This is for a church db to list anniversaries, birthdays, etc that are happening during said week. Basically, I would like one query to find all 'events' occuring during any given date range.

Any ideas are extremely appreciated!

Thanks,

Ringer

PS. Of course the MS Access Help section hasn't seemed to be very helpful so far.
 

A:MS Access date query

Format the date first in the column before setting the criteria. Format([DateField],"m/yy")
 

Read other 1 answers
RELEVANCY SCORE 80

I have written a macro to make a table from data in an existing table. Then I use the data in the new table to select information in another table. That select query does not allow me to update the information in the 2nd table. It is like the records are locked. Even if I create a new table myself the select queries are locked but all my pre-existing queries work fine. Is there some type of setting I am missing that causes the information to be locked in select queries? I am thinking somehow the setting may have been changed. Any thoughts would be appreciated.
 

A:Solved: Access Make Table Query and Select Query

Read other 7 answers
RELEVANCY SCORE 80

I've got a lending table in which we lend items out, now they can be loan or permanent.

I can run a report with the query parameter on the ReqDate_Fld criteria Between [Report Start Date] And [Report End Date]

The report prints and counts everything borrowed.

Now I want to add more records to the report to include everything borrowed that was returned during that period specified by the criteria Paramenter set in the query. But using the Return Date (RetDate_Fld)

How can I select rows from the query based on the parameter date filled in when the query ran?
[Report Start Date] and [Report End Date].

I can build a separate report just for the returns using the current parameter but on the RetDate_Fld Between [Report Start Date] And [Report End Date] set by the query.

I've got it grouping at 3 levels to get it to break by Borrower, ReqID, and Request Date

Does this make any sense?
Thanks
 

A:Solved: Access query report query paramater use the param

You would have to have another group for whether or not the RetDate_Fld was filled in or not.
Add a new column to the query with the following Heading

RetDatefilled: iif(not isnull(RetDate_Fld), 2, 1)

That should place a 2 in the new column when there is a date and a 1 when there isn't.
You can now group on that data.
 

Read other 2 answers
RELEVANCY SCORE 80

I.ve developed a Work Order form in access that has an request date field. I would like to create an query that will show the request from the last month or 30 days. I looked at datediff but I'm not too familar on the syntax. Please Help.
 

A:Date query in Access 2000

Read other 12 answers
RELEVANCY SCORE 80

Can someone help please?

I'm setting up a query in Excel from an SQL database. How do I (or what do I) do to ensure only todays data is shown - I suppose I'm looking for an"=TODAY" type thing but don't know enough to be able to do it.

Thanks in anticipation.

Moll )
 

A:Excel query on Date field

it can depend on how the date system is set up but the following page has pretty much every SQL date function going

http://www.connx.com/products/connx/Connx%208.5%20UserGuide/sql_date.htm
 

Read other 1 answers
RELEVANCY SCORE 80

Help! I'm using Microsoft access 2003. I have clients that are booked in for a block of ten treatment sessions. I can only schedule SMS reminders for them a week in advance. I need to create a query that allows me to see who is due for an SMS reminder in the next two weeks ie who has a treamtent coming up. Treatments are entered in as Treatment 1, Treatment 2 etc. When I add all the 10 treatments on as fields in a query in design view and enter the code <Date()+14 for all of them, it comes up with nothing. However, the query works if I only have Treatments 1 and 2 added, as all clients will have these treatments coming up (they aren't up to the later treatments yet). Is there a way that the fields can be searched as an "or" rather than "and" ? Do I need to change the code? I don't want to have to run a separate query for all ten treatments!
I'm an academic and a health practitioner and really not very good on Access! hope you can help
 

A:Date query in Microsoft Access

In your query designer if you put the criteria all all on the same criteria row it will be an AND. If you put them on separate lines for each column it will be OR
 

Read other 1 answers
RELEVANCY SCORE 80

I am looking to run a query on a date field to begin on a date say Monday 08/25/2008 and having only every other Monday show up or be eliminated (whichever is easiest).

My data is split into two tables one with the (Dates) in one field and (days) in another going out several years and then the (start date) in another table.

[Date][Date Tbl],[Day][Date Tbl] comparing to [Bi-Weekly Start Date][Team_Mbrs] and showing [ID][Team_Mbrs], the employee.

I am attempting to show the days off an employee will have when they start a day off and have every other off. I hope this makes sense.

Thanks.
 

A:Access 2000 Query on Date

Date()+7
 

Read other 2 answers
RELEVANCY SCORE 80

Hello,
I have two date fields, Arrived and Passed.
I need to be able to count the numbers of arrivals and passes for each date in a date range entered by the user.

So, I just need an expression that effectively combines the two fields. It is not an either/or situation - the item could pass on the same day it arrives.

The result I'm after would include each date in the date range even if there were no arrivals or passes on that date. So if I included parameter criteria and the user entered the date range 15/05/06 to 20/05/06, the result should look something like:

EventDate__Arrivals___Passes
15/05/06____10______20
16/05/06_____6______10
17/05/06_____0_______0
18/05/06____18_______5
19/05/06_____0______12
20/05/06_____0_______0

I'm sure this is dead easy, I just can't find what how to create the EventDate expression.
 

A:MS Access - Simple date query?

Read other 14 answers
RELEVANCY SCORE 80

I'm trying to create a query that prompts the end-user for the date fields to search.

For example, I have an Invoice table that contains a "InvoiceDate" field. I want the end-user to be able to search for invoice dates between 'date A' and 'date B'.

More or less, I need to know how to setup the "criteria" in design view of the query.
Any/all help is appriciated...

I'm using MS Access 2K
 

A:MS Access 2K: Date (Criteria) Query

Between [Begin Date"] and [End Date:]

It will prompt them for each value.
 

Read other 2 answers
RELEVANCY SCORE 80

how can i create or enter a multiple date range for a query. The date is in a 5/28/2007 format. I tried what I thought would work.
 

A:multiple date range for a query

Read other 7 answers
RELEVANCY SCORE 80

Hi!
I have one question about Date() in Access:
Ex:
One customer ordering one product so there is Date Order (2-Jan-02) in table.
I want to know how can I make one query which can check that the period of time from Date Order to Now is over 2 years or not. I have tried using Date() and Now() but could not.
Please help me
Thanks a lot!
 

A:Date query in MS Access 2000

dungnhat,

the following code snippet will return the number of days between OrderDate and date now.

Sub test()

Dim datenow As Date
Dim OrderDate As Date

datenow = Now
OrderDate = DateValue("1 January, 2002")

MsgBox DateDiff("d", OrderDate, datenow)
End Sub

this will return 499 days

you can then calculate if the elapsed time is greater than 2 years by comparing the result of the date function to 730 days (2years)

if (DateDiff("d", OrderDate, datenow) < 730 then
do something
else
do someting else
end if

If you use the yyyy interval argument for the datediff function the returned answer possibly will not be the result you expect.

For example using the values above
DateDiff("yyyy", OrderDate, datenow)

will return 2 years

Hope this helps,

Tom
 

Read other 1 answers
RELEVANCY SCORE 79.2

Hello,

I'm relatively new to access and need a bit of help (more like a lot).

I have a table that lists record id's, inspection purposes, and inspection dates (looks like this):

1234 Routine 3/30/2013
4567 Investigation 1/20/2013
4567 Routine 2/20/2013
7890 Approval 12/21/2012
7890 Routine 1/12/2013

I want to recover the maximum date for each record id (without duplicate records). I want the result to look like this:

1234 Routine 3/30/2013
4567 Routine 2/20/2013
7890 Routine 1/12/2013

Can anyone explain to me how this can be done?

Thanks much!
 

A:Solved: Access Query: Most Recent Date

Read other 6 answers
RELEVANCY SCORE 79.2

Trying to finish my group project in my senior AIS class. I need to design a query that pulls the date value from an employee list if the current date is 6 months from the date when employment began.

This is the only part of the query that I cannot seem to get to work and is holding up the update function of the query to increase the employee's pay rate.

I followed what was in the help function of Access and thought I had corrected it look back over 6 months but it didn't work at all. Myself and the rest of my group would appreciate the help.

The spider web as we have nick named it.

Disclaimer: The above is a fictional company created for the project including fictional data for testing purposes. Just saw the disclaimer at the header of the business app forum and wanted to get that in there.
 

A:Access 2007 and date query functions

Read other 16 answers
RELEVANCY SCORE 79.2

Hi all
Have started up a new machine and upgraded from Access 2000 to 2003 and transferred our database over. We have just over 5,000 records in the contact table which we update daily with the current date as various repeat contacts are made.
The relevant field is set to Long date.
Every evening we have a Word 2003 file that completes a mail merge to a database query that pulls records with the current date. That query obtains its data from the contacts table. Or at least, it used to.
Since the change something has gone wrong and I cannot sort out what. The query will not let me use the =Date() function in the criteria row. As a test I set up a dummy database with a simple table and a single query. In the query grid I added =Date() in the criteria row and low and behold it worked. So obviously there is a problem in the original table. Now I am lost. Does anyone have any pointers please?

BigAl
 

A:Using the =Date() function in Access 2003 query

That is odd I have just answered another poster with date problems.
can you open your New table and check the Actual dates in the field ensuring that you expand it>
Does it have the "Time" in the field with the date, you may have to change the Date/Time format to Short or long time to see it?
Has the Date field always been "Long" date rather than Short date?
Also try in the Query in a new Column
Format([Your Date Field],"dd/mm/yyyy") and put the date() criteria in that column instead.
I have just checked it and it works whereas Long date with a time element won't.
 

Read other 1 answers
RELEVANCY SCORE 79.2

Hi

I have a Users DB that I cannot make any changes on it. One of the fields has a Date field. I need to run a query on that table to create reports, but the query creates multiple records for the same user with the only difference in the Date filed. I need to have only the records with the latest date in the Date field.

Is there a way to set a query that does this?

Thanks,
Barbos
 

A:Solved: access 2003 query by last date

You need 2 queries, the first has the userrecordId and date fields, set Totals>Grouping the userrecordid Grouped and the date set to Max.
Then in the second query you want the main table and the 1st query joined by the userrecordid and the date.
So the first query finds the last and the second show the data from just that record.
 

Read other 2 answers
RELEVANCY SCORE 79.2

Hi to All!

I have a database of vendors. 2 of the fields are "Exp_Month" & "Exp_Yr". The data for "Exp_Month" is entered as "JAN" etc. The data entered for "Exp_Yr" is "01", etc. This was uploaded from a program called ADPICS and imported into Access.

I am trying to generate a report that will give me all vendors who will expire in 90 days. I was tying to make this happen in a query then would base my report off the query. But I'm having trouble with proper syntax for the date and year.

Will someone please help me??? THANKS!!!!!
 

A:Solved: Access 03 - Date Format in Query

ddw, what format are the 2 fields in are they "Date" fields, Text or Numbers?
 

Read other 3 answers
RELEVANCY SCORE 79.2

I am trying to design a query where only records with the current date are printed. When I put &lt;Now() in the criteria of the query for some reason records with todays date and less than todays date are printed When I put &gt;Now() only records greater than are printed, when I put =Now() I get nothing. I would appreciate any input thanks.
 

A:ACCESS - Query base on current date

=NOW() means RIGHT NOW, not TODAY, which is what you want.

=today()

=now() is 10:29 EST on 10/3/2001 (at least right now it does, LOL)

=today() is 10/3/2001
 

Read other 3 answers
RELEVANCY SCORE 79.2

I have a date column displaying all payment dates.

Is it possible for the user to query by year only, thus displaying payments only from the desired year? [DD/MM/####]

I know how to set up input queries, but am not sure how to prompt for only the year correctly. Thanks for any help!!
 

A:Date Column, Query Criteria only Year

Read other 6 answers
RELEVANCY SCORE 79.2

I have a table in which I want to calculate Field 1 to pull all dates between 21-56 days. Does anyone know how I should type the expression?

Also, in the same table, I'd like to write a query to calculate a certain number of days between Field 2 and Field 3. I would like to pull all dates that are within 42 days. How should I type this expression? Also, where exactly in Design query should I type this expression? I know it would go under criteria, but under what field?

Lastly, I would also like to run a query that will calculate the number of weeks/days between Field 2 and Field 3.

If anyone has any tips on how to create calculated date expressions, I would very much appreciate it.

Thanks in advance for your input.
 

A:Access: Writing Date Calculations within a Query

I see no one answered my previous question, but that's okay because I figured out the correct criteria.

Now I have another problem. I have several tables in my database. Table one lists a lot of information related to deliveries. My primary key in this table is SSN. Table two lists demographic information with the primary key being Sample Order. There is a field in table two called SSN that I've linked to table one. I am trying to add the "DOB" and "Sample Order" fields from table 2 to table one. However, table one does not have every record that table 2 has. Table two has 493 records whereas table one has 392 records. Table one has everyone in table two so I figured I should still be able to link the two records so that I can create query that will add the "DOB" field and "Sample Order" field to table one. Is this possible and how? I really would appreciate help on this issue.

Thanks.
 

Read other 3 answers
RELEVANCY SCORE 79.2

Greetings

It's been a long time since I last posted here, but hopefully all you wonderful people will be able to help...

Here is my problem:

A customer of mine has a SQL database where one of the fields is a date/time field that looks like this:

13/02/2003 14:32:58
14/02/2003 08:59:10
15/02/2003 17:09:37

Now, I need to delete all the records for a particular day (One of their HD's failed, and they had to restore the data and lost a day, but the entries are still in the database, and can not be re-entered until the old entries are deleted).

How do I structure a query to delete all the records for a particular day? Preferably, I would like a SELECT statement first, just to check that the records found correspond to the delete criteria, and then the DELETE statement.

Thanks

Reuel Miller

The Ex-NT moderator on this board

Every morning is the dawn of a new error
 

A:SQL Query help required with date/time field

Greetings

Not to worry chaps, I figured out the answer all by my lonesome

the statements I used were:

select * from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'

delete from {database name} where {date field} > '14 February 2003' and {date field} < '15 February 2003'
cheers

Reuel Miller
 

Read other 1 answers
RELEVANCY SCORE 79.2

Hi,

I believe this is a really easy thing to do but i can not get it to work,

I have a table that contains a name and the dates that they are on holiday. i am wanting to have a query that will show me everyone who is on holiday in February.

I thought this was simple but i have not been able to get it to work, i think i am missing something really really simple.

I have attached a copy of the table, the table is part of a college project and all data is fictional.

The attachment is zipped so it could be attached

Thanks
James
 

A:Solved: Access 2007: date Query

James, to find all dates for February can be done in 2 ways, you can use from the 1st (startdate) to the 29th or 28th (eneddate) and then type in the Query Criteria Row
between 01/02/2008 and 29/02/2008
or you could use the Date part or date Format to extract February form you date and then compare it to February, but that only works if you have one years worth of data.
I can't help with your zipped file as I do bnot have Access 2007.
 

Read other 2 answers
RELEVANCY SCORE 78.4

Hi Everyone,
I am trying to create a query that selects all records with a future date. The table that I am trying to select the records from is named "Events" and the field containing the date is "Date"
I am using Access 2007 and the date field is formated as 'Date/Time' data. I tried multiples ways, and even tried the 1000 year add date function pasted in below. Does anyone have any insights--I'm sure its something that is quite simple.

Thanks,
Joe

SELECT Events.Date, Events.Event
FROM Events
WHERE (((Events.Date)=DateAdd("yyyy",1000,Now())));
 

A:Solved: Access 2007 Future Date Query

Read other 6 answers
RELEVANCY SCORE 78.4

This question was asked by someone several years ago, but I did not find the complete answer.

I need to know how to create a QBF using a date range as part of the criteria.

I have a form to run a query. The form has a combo box to limit "approved" or "pending" new purchase orders (PO) (criteria one), but I would like to add a second criteria for POs created between "07/01/2009" and "07/06/2009" (criteria two).

I want the end-users to be able to select the date range (enter the start date and end date). I don't want to limit their selection.

I know how to use the date range in "Design View" of a query (ex: >=[Start Date] And <=[End Date], or,
Between #07/01/2009# and #07/06/2009#) but I don't know how to implement that into the Form.

I know I need to put something like the following in the query criteria field for PO_Created_Date:

Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]

But how to make it work in the form?

Please help!

Thanks,

Yuan
 

A:Access 2003: Query by Form using a date range

Yuan, welcome to the Forum.
You appear to understand the query requirement, especially the "Between [Forms]![Formname]![BeginningDate] And [Forms]![Formname]![Text_EndDate]" part.
So which part don't you understand?
as long as the you put in the name of your Form in the Fromname brackets and you have 2 fields on the form called BeginningDate and Text_EndDate that Criteria will work.
 

Read other 3 answers
RELEVANCY SCORE 78.4

Hello!

I have an issue I've looked at 50 different ways from Sunday and am stuck on the resolution. I am tracking metrics for a project, stored on SharePoint, which I dump into Excel and use Access 2007 for my reports.

The report has multiple date fields, i.e.: R4RPlan_Date, R4RActual_Date, BusPlan_Date, BusActual_Date, etc... on table RICEFW_Dates.

What I am trying to do is set a parameter to query all the fields for a specific date, i.e. <[Report Date], without having to run multiple parameter queries. The end result will be a count of the number of dates in each field meeting the criteria.

The problem comes in when teams complete their criteria before it was planned, i.e., R4RPlan_Date = 3/23/12, R4RActual_Date = 2/6/12, <[Report Date] = 2/22/12.

If I add <[Report Date] as a criteria for the R4RPlan_Date, I will get all the plan dates I'm looking for, but I will not get all of the actuals since the plan date is after the specified [Report Date], but the actual was completed w/in the frame of the parameter. If I put the [Report Date] parameter in the R4RActual_Date field, I get the correct number of actuals, but since I am querying the field with lesser amount of data, I do not get all of the planned dates which meet the [Report Date] parameter. So if the plan date is greater than the parameter date, I want it to show up IF the actual date is not null, otherwise it should follow the parameter criteria (<[Report Date]). This needs to completed fo... Read more

A:Solved: Access multiple date query issue

Solved in another forum.
 

Read other 1 answers
RELEVANCY SCORE 78.4

A while back you (I don't remember the exact members) helped me create a query by form in Access 2000... Thank you. Now I need to know how to create a QBF using a date range as part of the criteria.

I know how to use the date range in "Design View" of a query (ex: Between #07/01/2004# and #07/06/2004#) but I don't know how to implement that into the Form.

As an example, I would want to pull all records of "Apples" (criteria one) sold between "07/01/2004" and "07/06/2004" (criteria two).

Please help!!!
 

A:Access 2000: Query By Form Using A Date Range

Read other 7 answers
RELEVANCY SCORE 78.4

Hi all.

I hope somone can help this is starting to annoy me.

I am building a database for the charity i work for to track our collection boxes that are out and about.
I am trying to put a button in so it will pull out all boxes that have been out for greater than 30 days. To do this i am using the field "Date Out" in the query and the criteria is:

>Date()-30

This works but pulls our 200,000 results when there are only 1400 records in the table.

any ideas?
 

A:Solved: Access 2007 Date Query Problem.

Read other 7 answers
RELEVANCY SCORE 78.4

I will need to combine two tables on a weekly basis. Table 1 has the Product ID and the date and some important data. Table 2 has the Product ID, the date, and some other important data. For table 1, I only need to append the table once a month because the value needed stays consistent. However, for table 2, the values needed change on a daily basis.

How do I create a query that will command you for the date from table 2 and take the values from the month on Table 1?

The ID is not unique in either table, but it is unique for the date in each table.
EX:
ID Date Forecast
1 June, 2012 5
2 June, 2012 3
3 June, 2012 2
1 July, 2012 7
2 July, 2012 9
3 July, 2012 1

Sorry if I'm not too clear in what I need!
 

A:Utilizing Date/Time in Query to combine tables

You can "Join" the 2 tables using the date fields, that will show all the values that match from both tables.
You don't actually show the end result you want.
 

Read other 3 answers
RELEVANCY SCORE 78.4

Hi all,

I have problem with query a date range... I know how to use in "Design View" of a query (ex: Between #01/01/2004# and #05/07/2004#) but I don't know how to implement that into the Form, for eg: In Criteria: Between (Forms!FormName!From_textbox) And (Forms!FormName!To_textbox) but it just returns nothing...

Please help!

Thanks
 

Read other answers
RELEVANCY SCORE 78

For a report I have a customer table, an invoice table and a revenue or payment table.

I need to produce a report that shows all the invoices created during a given month and year - that part is easy.

But then I also need to show the first payment given on that invoice. This is a situation where there is always a down payment made at the issuance of the invoice - but sometimes people will make more than one payment during a month.

Example:

I want the customer listed for each new invoice, the total amount of that invoice and what the inital payment on that invoice was: [CustomerID][Customer Name] [Invoice No][Total Invoice Amount] [Payment Amt]

So if there was an invoice issued for $100 and they paid 50 and 25 during the month it was issued and the balance on the next month, I still want to show it on this report as a $100 invoice with a $50 downpayment only.

(The tables are established and populated so it is too late to change the payment table to allow for a unique downpayment field. They all go in as payments.)

My problem is that if they made more than one payment during the month, the whole line with the customer name and invoice number shows up for each payment made. I only wnat the FIRST payment. I need a payment query that shows only the first payment in the month of invoice issuance.

Does that make sense?
 

A:SQL Query question for Access 2010 query

Yes it makes sense, I would create a pre-query that uses the Month & Year but is set to "Totals" and Date Minimum.
Then create the Report query using the pre-query linked to the new query's table via the InvoiceID this will limit both the month, year and minimum payment.
 

Read other 1 answers
RELEVANCY SCORE 78

I have a previously designed Access DB and there is a query that I am trying to understand. It has several tables that are queried more than once in a single query. First how do I determine the actual table name? I can only see the alias name. Also what is the purpose of using the same table twice in a query?
 

A:Solved: Access Query using tables more than once in same query

It is normally to do calculations or comparisons on prior or next records. It is the one weekness of Access Queries, the Totals function has some good arithmetic calculations like average, Count Sum etc but manipulating previous records like add or subtract values from the current record it can't do. So it requires more advanced SQl. Which is apperas you have.
Have you looked at the query in SQL view?
It should use the word "As" to designate the alias.
 

Read other 2 answers
RELEVANCY SCORE 78

Hi there,

Once again I need your help in Access. I'm trying to do a calculated field in a query that based on a query. For example, query B is based on query A. I want to add a calculated field called Totals in query B. The field totals does not exist in any of my tables.

Here is an example.

Totals: [Field1]+[Field2]+[Field3]

In the totals line I clicked on Expression. But the totals do not appear when I view my query. I get no errors on the formula. What at I doing wrong??

Mario
 

A:Calculated field in a query based on a query

Read other 7 answers
RELEVANCY SCORE 78

I have created a search engine for a data base using Access 2007
The Form Basically Runs a query of multiple fields and loads the result into a Combo box.
The problem is the data base is not fully complete and the records with blanks in some of their fields do not appear in the search results.
For example lets say I am looking for a People with the same surname and in my database is
Name Surname
Mike Norris
Chuck Norris
Norris
The query will not find the record only containing Norris
Here is the query I have used.
Like "*" & [Forms]![Entry]![txtSurname] & "*"

 

A:Query, Query Searching Blank Matches

Read other 8 answers