Between Dates Parameter Query - Extra Records Generated??

Apr 4, 2008

Good Evening Everybody,
I am currently helping some people out at work with their database. My knowledge of Access is very limited, and whilst my it is improving I have nonetheless come up against a ‘hurdle’ which I am struggling to jump. The database in question is not complex, quite the opposite in fact. Basically 1 table ‘Main Data’ , 1 data entry form and a few reports. The table includes 8 fields in the following formats:

ID: Auto Number
Area: Text
Equipment:Text
EquipSrlNo:Text
EquipLocalNo:Text
MOPNo:Number
DateDone:Date/Time
MOPPeriodicity:Text


I was recently asked to produce a report for them that would search between two dates using a parameter query and to then return the result. The problem is that when I generate it using the following SQL it returns records in the period that I asked for, but then collects ‘additional records’ which fall outside the date-span that I originally requested, i.e. search Between 03/04/08 And 30/04/08 produces data that relates to data in May, June, July 08 etc. I thought that what I was doing was correct, and it has worked for me in the past- but on this occasion I am absolutely confused???

SQL
SELECT [Main Data].Area, [Main Data].Equipment, [Main Data].EquipLocalNo, [Main Data].MOPNo, [Main Data].DateDone, [Main Data].EquipSrlNo, IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone]))))) AS TestDueNext, [Main Data].MOPPeriodicity
FROM [Main Data]
WHERE ((([Main Data].Area)=[Enter Area Type]) AND ((IIf([MOPPeriodicity]="1",DateAdd("m",1,[DateDone]),IIf([MOPPeriodicity]="3",DateAdd("m",3,[DateDone]),IIf([MOPPeriodicity]="6",DateAdd("m",6,[DateDone]),IIf([MOPPeriodicity]="WEEKLY",DateAdd("ww",1,[DateDone])))))) Between [Enter Date Start] And [Enter DateStop]));

Unfortunately, I do not have a copy of the database in question and so I am unable to post it for the purpose of explanation.

Can some kind person point me in the right direction as I am slowly losing the ‘will to live’ Ha? Ha? You have always been so helpful in the past and hence the reason I have called upon my ‘forum friends’ to help me out?


Look forward to your response(s)


Best Regards

CarolW

View Replies


ADVERTISEMENT

Is It Possible To Add Extra Fields To Dates?

Dec 3, 2007

Is it possible to have an extra field that is added to the calculated weeks left? Such as what if I want 2 extra weeks instead of the 12?

I know this is probally an advance question! Thanks in advance!!!


This is the awesome example from sbenj69:

Expr1: 12-(datediff("ww",[join date], date())

What I want to do is add another column which will have an additional number. The additional number would just be added to the 12- part of the code. So if I have a field with 2 in it, the 12- would become: 14-(datediff("ww",[join date], date())

Is this truly possible or is it something that can't be done in access

View 5 Replies View Related

Parameter Query Using Dates Leading To Subtotals

Nov 4, 2011

I have a list of coded transactions in column A, Amounts in B and Dates in C.

I want to show subtotal for all amounts with the same codes in D

I can do this simply without the date parameters, but I need to give timeframes.

When I add the [From] and [Until] questions it then shows the amounts next to each date even though the date column is hidden.

I'm aiming for the subtotal of amounts for same codes between given dates:

Example
ABB 100 1/6/11
ABB 100 6/6/11
ABB 150 1/8/11
ABC 50 1/7/11
ABR 50 2/8/11
ABJ 50 1/5/11

If I enter 1/6/11 and 30/6/11 I would expect ABB to show 200 total as 150 is after that date and so not counted.

View 2 Replies View Related

Parameter (Date) Query To Get Input Dates On Report?

Apr 28, 2006

I have a report that has an underlying query that asks for Start Date and End Date.

Is there anyway that I can get what the user inputs into the box to be put into the Page header of the report?

ie: user enters into the parameters
[Start Date] 01/01/2006
[End Date] 04/04/2006

Then when the report displays it says

Report for the period 01/01/2006 to 04/04/2006

Thank you for your time

View 4 Replies View Related

Queries :: Parameter Query - How To Get Table Contents Between Dates

Feb 3, 2014

I have a parameter that I need to get a table contents between dates. In the query:

Birthdate: XXTable: criteria as follows: Between [Enter Start Date] and [Enter End Date:]

When run it gives me the Error - ! This expression is type incorrectly, or its too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables

I have tried almost everything. The formatting of the Birthdate is x/x/xxxx or shortdate. Will this affect the input thus affecting the outcome of the query.

View 4 Replies View Related

Queries :: Show Non Booked Vehicles For Given Dates In Parameter Query

Apr 12, 2013

I have a query with the following fields

*Date From
*Date Till

I have got the following formulas for each

Date From - >=[ENTER Start Date]
Date Till - <=[Enter End]

The results work fine to show which vehicles are booked between these dates, the only thing is I want to be able to show the vehicles that are not booked for the given dates in the parameter query is this possible?

View 2 Replies View Related

Queries :: Parameter Query Criteria To Return Dates With 2 Years Remaining

Sep 17, 2013

I am trying to create a parameter query to return dates that have 2 years remaining.

For example I have dates for when mortgages expire, and I want to recognise the dates that have two years remaining using a parameter query but I can't figure out if I use DateAdd or DateDiff.

View 2 Replies View Related

Remove Extra Records

Jan 9, 2007

I have a table t1 which contain many records, the field look like:

id f1 f2 f3 ---fn

Id is the primary key
I have some records which have different id but exact the same other fields.
like
id f1 f2 f3 ---fn
87 1 4 6 ---9
12 1 4 6 ---9
18 1 4 6 ---9
116 1 4 6 ---9
1287 1 4 6 ---9
98787 1 4 6 ---9

for those records, I only want to keep one record (any one) and remove all others. How can I do that?

Thanks.

Jeff

View 1 Replies View Related

Opening Forms Extra Records

Aug 31, 2011

When I open my access 2007 database from the switchboard, the form that it opens up to is a parent form with a subform embedded in it. The subform is linked by the 'org name' and by 'year'.

I am finding that when I open the parent form,there is always an additional empty record in the subform, alongside the record which has data in it.

I am not sure why it is doing this, especially as when you enter another record, this empty form vanishes. Its almost acting as the default form?

I have also noticed that when I go to another form which is displaying specific data linked to the 'org name' and 'year' it also has an extra record

However, the weird thing is it does not show up in the table. And again, once you go to a new organisation and input some data, and then go back to the other organisation,the additional record has gone?

Is there a simple property setting whereas each time you open the form, it always opens up to where you essentially left off, no empty record?

View 2 Replies View Related

Forms :: How To Avoid Creating Extra Empty Records In Input Form

Sep 18, 2014

I always have a problem with input forms in Access in that I usually end up with an empty excess record which is being created because I do not know how to program this correctly.

I have a Purchase Order Receipt form (frmPurchase). When I receive a certain quantity of an article associated with a lot number (or a serial number) I have another input form opening up (frmLotorSerial), passing to it through Openargs, the article and the quantity received.

Say we receive 10 units of which we want to register the serial number, I want the user to be able to enter a maximum of 10 units (could be 10 records if each record = 1 serial number, but it could be less than 10 if we have several units per lot number), and not one more, into this frmLotorSerial.

The record looks like this:
-key
-artno (article)
-recqty (quantity received)
-date
-warehouse
-lot number

I used the lost_focus event on lot number to check whether the total received quantity in this form matches the total passed to it from the Purchase form, and if it does to stop the input. Since I could not close the window in the code of this event (error 2585) I moved the close command to the current event of the form.

Doing this closes the form all right, but it always creates one record extra, which of course does not carry any information, apart from the date which has a default value of now.

How should I program/what events should I use to:
-test that the user should not go any further (total received in frmLotorSerial = total received in frmPurchase)
-close the window and, above all, not create this extra record.

View 2 Replies View Related

Queries :: Query For Records Between Two Dates

Jun 5, 2013

Only one table : Customer ID,Customer name , subscription period, subscription start date and subscription end date.

I need values where subscription start date is between date1 and date2 and subscription end date is between date 1 and date2.

Detail explanation:

customer1 subscribed from 2-feb 2012 to 2-feb 2013
customer2 subscribed from 5-aug-2012 to 5-aug-2013
customer3 subscribed from 1-Jan-2013 to 31-Dec-2013
when the user enters date 1 as 1-jan-2013 and date 2 as 30-Jun-2013

Even though the subscription start date is not within the range entered by the user.I need to get all three customers because the subscription enddate falls within the range.And customer 3 is also required as subscription start date is within the range.

View 2 Replies View Related

Reports :: Show Number Of Records Returned In Parameter Query

Mar 9, 2013

I am trying to use Dcount to find the number of records in a query and put it on a text box in the header of a report. The query is a parameter query. The button opens a form which asks for start and end dates, the query reads those dates from the form and the report displays the correct number of records that fall within the date range. The query is based on another query, which is based on the table. I tried =dcount("*","qryName") but I get #error. Maybe becauase of dates? or parameters?

Access 2010, Windows 7

View 3 Replies View Related

Queries :: Query To Filter Records Between Dates

Feb 19, 2015

I have a few queries which are used to create reminder email on training which is due for renewal.Some training required reminder 6 months before 2 year expiry. I use this in the criteria for the training date within query:

Between DateSerial(Year(Date()),Month(Date())-18,1) And DateSerial(Year(Date()),Month(Date())-17,0)

Some training required reminder 3 months before 1 year expiry. I use this in the criteria for the training date within query:

Between DateSerial(Year(Date()),Month(Date())-9,1) And DateSerial(Year(Date()),Month(Date())-8,0)

My problem is with training requiring reminder 6 months before 3 year expiry. Using this criteria:

Between DateSerial(Year(Date()),Month(Date())-30,1) And DateSerial(Year(Date()),Month(Date())-29,0)

Doesn't show any results (although there is training which was done 30months ago, expiring in 6 months time).

Changing the -30 (months) and -29 (months) in above down to -22 & -21 shows records as expected, but anything below -22/-21 doesn't show any records.

View 3 Replies View Related

Forms :: How To Ensure That All Values In Query Are Generated

Dec 8, 2013

I have created a form taking in Data from a query.On the form I have created a combo box. The selected value is used in an criteria of the query.The criteria in the query is:[Forms].[frmMyPortfolio]![cbodep].How can I ensure that all values in query are generated, if their is no selection in the combos.

View 1 Replies View Related

Union Query Prompts For Parameters - Returns All Records With Parameter Entered!

May 17, 2006

Hello all,

I realise this issue is a common one and it is usually down to simple typos (I've looked at several similar posts) but none of the advice I've seen has solved my problem. I've designed the following Union Query:

SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 1]

UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 2]

UNION ALL SELECT [Email],[Title],[First_Name],[Last_Name],[Company],[Phone],[Country],[TEST_AND_MEASUR],[REC_AND_PROD],[LOG_AND_TRANSC]
FROM [Region 3];


It should be very simple and I've checked it over and over, but when I run the query an Enter Parameter dialog appears prompting me to enter Email.

If I just click OK I get all the records but with the email field blank.

Similarly if I type x@y.com it returns all records but with the email fields all containing x@y.com

Advice would be very much appreciated!! Thank you in advance.

Abi

View 2 Replies View Related

Help Needed Please: Date Parameter In Query - Leaving Blank To Include All Records??

Aug 9, 2007

Hi

Bit of an Access beginner and am trying to sort something out for work - not sure why they've asked me!

I've created a query to search on a couple of items using drop down boxes on a search form I created. This bit of it works fine, I used this site http://www.fontstuff.com/access/acctut08.htm and copied what he had done. This is fine.

I now want to add a date search to the same query. I know I can use Between [..] AND [..] but if I leave the boxes blank it finds no records. I'd like it to search and include all.
Ideally I want to include 2 extra text boxes on my form that I can put a to and from date in (or not put a date in and it find everything).

Hope that makes sense, please can someone do me an idiots guide?

Many thanks
Phil

View 5 Replies View Related

Saving Daily Generated Query For Later Lookup By Date?

Feb 26, 2014

I have a query that reports the number of students that are currently enrolled in a given number of schools within an area for that given day that the query is ran. This number can fluctuate greatly day to day, so is there a way to create a table which can hold this information so that I can go back in history and look up what our numbers were on whatever given day (date). (The example below is just a representation of what I actually have.) The School number would be unique, as would the date (I assume), I would like to see all of our school on whatever given date to see what our numbers are or were.

I have an example of the query currently I have set up in access 2007:

School # School Name Qty Date
001 BLUE 300 02/26/2014
002 RED 468 02/26/2014
003 YELLOW 10 02/26/2014

VS

School # School Name Qty Date
001 BLUE 310 02/27/2014
002 RED 400 02/27/2014
003 YELLOW 8 02/27/2014

View 3 Replies View Related

Use Query Generated Field As Criteria For Another Query?

Apr 2, 2008

I am trying to use a single record and cell of data generated from a query as criteria in another query but can't figure out how to do it? Is there a way to reference a query field in the criteria in design view of another query?

Details:

I have a table of data for each month with supplier codes in each table, but no date field.

I am trying to build a query that will automatically pick the most recent month of data. I have built a query that uses an iif statement in SQL assigning a number to the most recent month of data and then I am using the max filter in that query to show the single highest number (But this logic misses suppliers some suppliers but does assign a number to the most recent set of data). In this case its 2 for february data. I want to use this digit to filter a seperate criteria that will show me all the supplier codes for the month of february. One problem is that some suppliers are in every month others, come and go throughout the year, so I have to assign this criteria for each month. Therefore I want to use the 2 from the first query and plug that into each criteria section under each month field of the second query.

The other option that I can think of but can't seem to find a fix, is to merge all 12 tables so there are duplicate entries and can have a date or number assigned for each month that the supplier code shows up, then just use the max number. I can't seem to find a way to add all the codes creating duplicates into one field. I used a union query but there are no duplicates with this method thus foiling my plan.

Please help
Thanks for reading

View 1 Replies View Related

Insert Parameter Dates In A Report

Jun 14, 2005

Hey folks,

sorry if this is an oldy but.........!!!!!!!!!

I need to insert the parameter dates for a query into a report. I have tried all the methods I can find but none seem to work. I think its to do with the way my queries are structured.

I have a basic query (q1)! Based on this is a grouping query (q2)! Based on this grouping query is my report. I have set up a form prompting for the parameter dates. This all works ok and my report displays the correct data (between the dates entered). The problem is that I cannot get the report to show the parameter dates.

Help. Its driving me nuts.

Billsack

View 1 Replies View Related

Question About Extra Text In Query

May 25, 2006

Hello,

I made an database with all addresses.
When persons live in an appartment, then the field "busnummer" will be filled in with a number. When they live not in an appartment, the field "busnummer" is empty.

In a query, I made an expression and filled the expressien in field (in the query):

Expr3: IIf([Busnummer]>0;"bus [Busnummer]";"")

When "Busnummer" is greater then 0 THEN write the text "bus" and the value of the field right behind, else leave the field empty.

The result of the expression above is:
"bus [Busnummer]"

I have already tried the following:
Expr3: IIf([Busnummer]>0;[Busnummer];"")

The result of the expression is the number I filled in in the database. Now I want to have the result: bus and the number I filled in... :)

Who can give me a tip to solve this problem?


Kindly regards

View 3 Replies View Related

Enter Parameter Dates Once For Multiple Queries?

Jan 16, 2008

I have created a db with 10 linked tables from 10 db's.
I then build 10 queries associated to these linked tables.
The 10 queries filter data by start date and end date.
The data generated from each query is then exported to excel (each query has its own sheet in the woorkbook).

Dim Output_Path_And_File As String

Output_Path_And_File = "C:" & "Production Labor hrs Querie.xls"


DoCmd.TransferSpreadsheet acExport, 8, "labor hrs 3-WAY", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-ACV", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-EAP", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-EVMV", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-PFE", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-propor", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-SEGR", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-TBO", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-VCA", Output_Path_And_File, False, ""
DoCmd.TransferSpreadsheet acExport, 8, "labor hrs-VFS", Output_Path_And_File, False, ""


How can I set these queries up by typing the start date and end date only once rather than 10 times?

Thank you

View 5 Replies View Related

Unwanted Extra Results From Select Query

Mar 9, 2006

Hi,

I am a novice with Access and would like a steer with what I am sure is a simple issue but I can't find an answer. :confused:
I currently have 2 queries based on 2 separate tables.

Ops_Log_996_Query
SELECT [996_Table].Unit, [996_Table].Location, [996_Table].hiredate
FROM 996_Table
WHERE ((([996_Table].hiredate)=Date()));

Ops_Log_SQTU_Query
SELECT SQTU_Table.Unit, SQTU_Table.Location, SQTU_Table.hiredate
FROM SQTU_Table
WHERE (((SQTU_Table.hiredate)=Date()));


When run separately the first query returns 2 results and the other 1 result - fine so far.
I am now trying to combine the results for display in a report so I have a third query which takes its info from the first two -

SELECT DISTINCTROW Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate
FROM Ops_Log_996_Query, Ops_Log_SQTU_Query
GROUP BY Ops_Log_996_Query.Unit, Ops_Log_996_Query.Location, Ops_Log_996_Query.hiredate, Ops_Log_SQTU_Query.Unit, Ops_Log_SQTU_Query.Location, Ops_Log_SQTU_Query.hiredate;

This displays the 2 separate records in the first 3 columns ok but in the last 3 columns the info in record 2 is a repeat of record 1. :confused:

Can anyone help me out please ?
Thanks, Oscar

View 2 Replies View Related

Extra Lines Appearing In Query Builder!

Jul 1, 2006

Can anyone help please?I have a search form that passes 4 parameterers to a query. In the query builder it reads:Like [FORMS].[frmCourseSearch].[txtCourse] & "*" >=[FORMS].[frmCourseSearch].[txtStartDate]<=[FORMS].[frmCourseSearch].[txtEndDate][FORMS].[frmCourseSearch].[txtCourseID]This works fine. But I also want it to search on only 1 or 2 parameters and set the rest to Null so I have used this statement for each one. Like [FORMS].[frmCourseSearch].[txtCourse] & "*" OR Like [FORMS].[frmCourseSearch].[txtCourse] & "*" Is Null This again works ok and I can search on any parameter. However, every time I enter data into the search form and run the query it repeats the query string in the query builder. i.eLike [FORMS].[frmCourseSearch].[txtCourse] & "*"Like [FORMS].[frmCourseSearch].[txtCourse] & "*"Like [FORMS].[frmCourseSearch].[txtCourse] & "*"etc...It does this with all 4 parameters and it's starting to look very messy. The SQL view is the same with a new repeated line added each time I run the query.How do I stop this happening? Thanks in advance.

View 1 Replies View Related

Syntax Error: Extra) In Query Expression

Oct 11, 2007

Hello I'm having trouble getting my nested Iif statement to run. Can anyone help??? I've attached a screen shot of the syntax error that I'm receiving. The example code below needs tweaking.



Update [Goodrec-copy3] set [Goodrec-copy3].shortname = Iif (Not Null([shortname]),[Goodrec-copy3].shortname Like "*,JR*" Or ([Goodrec-copy3].[shortname] Like "*, SR*" Or ([Goodrec-copy3].[shortname]) Like "*, II*" Or [Goodrec-copy3].[shortname]) Like "*, III*", InStrRev([shortname])," ",InStr([shortname]," ,")+1,50) &" "& Left([shortname],InStrRev([shortname])," ,")-1) WHERE ((([Goodrec-copy3].[ctype])="I"));

What I'm attempting to accomplish with this query is to keep the field shortname the same if not null and if it doesn't meet the criteria of having a string value of "JR", "SR", "II", or "III". If the field does have a string value of "JR", "SR", "II", or "III" reverse the string (example John Gissom JR) to reflect shortname as such for example: "Gissom JR John".

Thanks in advance!

View 2 Replies View Related

Update Query Creating Extra Record

Jan 5, 2005

i have this problem that is bugging the crud out of me:
sql="UPDATE bedrifter SET pr=" & Request.Form("pr") & ",totalindexedpages=" & Request.Form("tip") & ",totalinboundlinks=" & Request.Form("til") & ",description='" & Request.Form("dsc") & "' WHERE created='" & Request.Form("ts") & "'"

conn.Open connStr
conn.Execute(sql)
conn.close()
Set conn = nothing

when i run this code it updates the correct record (line in my access db) but then it also adds a new line with only that info in the update query. why is it doing this? when i update using the ID instead of using the timestamp in the WHERE clause it works fine. really frustrated...

View 1 Replies View Related

Queries :: Combine 4 Tables Data That Falls Within Dates - SQL Query Returns No Records

Oct 2, 2013

I have a complex database app that has a form called from the main form. It requires two inputs: BeginningDate and EndingDate and I use a calendar picker for date selection. Using data assigned to a variable, I build the SQL query in VBA. The result is:

Code:
SELECT [1733_All Print Orders].[Application], Sum([1733_All Print Orders].[TotalImages]) AS SumOfCCPC
FROM [1733_All Print Orders]
WHERE [Application] = 'CCPC' AND [StatementDate] >= #9/3/2013# AND [StatementDate] <= #9/30/2013#
GROUP BY [1733_All Print Orders];[Application]

[1733_All Print Orders] is a defined query that combines 4 tables together and there are data that falls within the dates for CCPC. But the query returns no records.

I pasted the query to the query builder and using different combinations, I isolated that the [StatementDate] >= #9/3/2013# portion is what returns no records

To complicate matters even worse, prior to today, it worked. I made some adds and changes to another area of the application, but did not touch this code.

View 1 Replies View Related







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