WHERE Clause Using Between And DateAdd Not Returning Correct Results
May 4, 2006
I have the following two querydefs. Here is the SQL;
qry1
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE DateDiff("n",[dtReading],[dtFind]) Between 1 And CInt([intMins]);
qry2
SELECT [dtFind], tblData.dtReading, tblData.dblValue
FROM tblData
WHERE dtReading Between DateAdd("n",-1*[intMins],[dtFind])
And DateAdd("n",-1,[dtFind]);
[dtFind] and [intMins] are parameters.
I use the querydefs is VBA code as such
Dim db As DAO.Database
Dim rstDataSQL as DAO.Recordset
Dim qdfData As DAO.QueryDef
Dim strQdef As String
Set db = CurrentDb()
strQdef = "qry1" ‘or qry2
Set qdfData = db.QueryDefs(strQdef)
‘Set values of parameters
qdfData![dtFind] = dtDate
qdfData![intMins] = intMins
Set rstDataSQL = qdfData.OpenRecordset
qry2 executes significantly faster than qry1, but I am having issues getting the correct results.
If I set [dtFind] = 12/28/2005 10:47:00 AM, both queries work fine. The last returned record has a value for dtReading of 12/28/2005 10:46:00 AM.
However, if I set [dtFind] = 12/28/2005 10:48:00 AM, only qry1 returns the right records. qry2 will not return the record with dtReading = 12/28/2005 10:47:00 AM, but qry1 will.
:mad:
I changed the Between statement to “Between DateAdd("n",-1*[intMins],[dtFind]) And DateAdd("n", 0 ,[dtFind])” to see what happens. As I expected, records where dtReading = 12/28/2005 10:47:00 AM and 12/28/2005 10:48:00 AM are returned.
The data should be in increments of 1 minute, although there are periods where data is missing. None of the dates have values like 12/28/2005 10:47:01 AM, i.e. seconds value is always 0.
I’ve tried adding “PARAMETERS [dtFind] DateTime, [intMins] Short;”, and also using CDate(DateAdd()) without any luck.
Whether I am using the queries using VBA/DAO or user input to set the parameter values, the results are the same.
I'm having an issue with a query I created. When run the query requests an Artist Name. I enter this and it returns one result. However in actual table ther are two results for that artist. The only difference is that for the record that doesnt appear the field labelled "Gallery" has a zero value.
I have checked the table and the gallery field is not set to a required field so I dont understand why it wont show it as a result of the query.
I'm working on a select query that I have created through the visual query design window in Access 2003, I have multiple criteria but the query is not filtering the results properly.
I'm selecting only records :
where the DeliveryCountry field is within the EU - works where the ProductID does not contain Repair, Upgrade or Rpr - doesn't work where the Demo/Sale ID is 2 - works
I've viewed the code in SQL view but I still can't locate the problem, though I suspect it lies in the treatment of ANDs and ORs.
The SQL is pasted below (I have reformatted with indentations so I hope it is easier to read).
One thing to note: the reason the countries list is split in two is because Access did this automatically when I had one long list. Is there a maximum length for WHERE clauses?
FROM Products RIGHT JOIN (([Demo/Sale] RIGHT JOIN Orders ON [Demo/Sale].[Demo/SaleID] = Orders.[Demo/SaleID]) LEFT JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") AND (([Order Details].ProductID) Not Like "*Upgrade") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") AND (([Order Details].ProductID) Not Like "*Repair") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Austria" Or (Orders.OrdDeliveryCountry)="Belgium" Or (Orders.OrdDeliveryCountry)="Cyprus" Or (Orders.OrdDeliveryCountry)="Czech Republic" Or (Orders.OrdDeliveryCountry)="Denmark" Or (Orders.OrdDeliveryCountry)="Estonia" Or (Orders.OrdDeliveryCountry)="Finland" Or (Orders.OrdDeliveryCountry)="France" Or (Orders.OrdDeliveryCountry)="Germany" Or (Orders.OrdDeliveryCountry)="Greece" Or (Orders.OrdDeliveryCountry)="Hungary" Or (Orders.OrdDeliveryCountry)="Ireland" Or (Orders.OrdDeliveryCountry)="Italy" Or (Orders.OrdDeliveryCountry)="Latvia" Or (Orders.OrdDeliveryCountry)="Lithuania" Or (Orders.OrdDeliveryCountry)="Luxembourg" Or (Orders.OrdDeliveryCountry)="Malta") AND (([Order Details].ProductID) Not Like "*Rpr") AND ((Orders.[Demo/SaleID])=2))
OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Upgrade") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Repair") AND ((Orders.[Demo/SaleID])=2)) OR (((Orders.OrdDeliveryCountry)="Holland" Or (Orders.OrdDeliveryCountry)="Poland" Or (Orders.OrdDeliveryCountry)="Portugal" Or (Orders.OrdDeliveryCountry)="Slovakia" Or (Orders.OrdDeliveryCountry)="Slovenia" Or (Orders.OrdDeliveryCountry)="Spain" Or (Orders.OrdDeliveryCountry)="Sweden") AND (([Order Details].ProductID) Not Like "*Rpr") AND ((Orders.[Demo/SaleID])=2))
SELECT tblRelease.ID, tblRelease.Application, tblRelease.Date, DateAdd("d",tblRelease.DateVariance,tblRelease.Date) AS ToDate, tblRelease.Time, tblRelease.Description FROM tblRelease WHERE (DateAdd("d",tblRelease.DateVariance,tblRelease.Date)>=[Enter Effective Date]) ORDER BY tblRelease.Application, tblRelease.Date;
I've chopped out a few fields to make it a bit smaller and easier to read.
The problem is that whilst the DateAdd() in the SELECT clause produces exactly the date that I want, the DateAdd() in the WHERE clause does not seem to work. I always get all records no matter what date I enter for [Enter Effective Date]. If I change the WHERE clause to
WHERE (tblRelease.Date>=[Enter Effective Date])
then everything works perfectly and I get different numbers of records depending on the date I enter.
I am entering the date in the form 01/08/2005 and this works for the second variation just fine. It's dd/mm/yyyy in case that's important (my local default).
I've been trying to work up a where clause that is generated by a button click event on a report. The workflow that i'm trying to obtain is as follows:
1) A report is run to determine the remaining work orders that need to be processed. 2) A button that is placed on that report is to be clicked, taking the user to the form associated with that work order, so it can be processed.
What i've been able to do so far is capture the unique ID for the work order and then print that in a message box. I can then open the form.
What i haven't been able to accomplish thus far is to open the form to the correct work order.
Things I've tried : I started trying to use the macro with the search for record option and using the where clause. Not successful. I am a little more comfortable in using vba so i switched to that pretty quickly.
Code: Private Sub btnJobEntry_Click() 'GOAL: open the work order form to the correct entry 'METHOD: store the uniqueID to a variable, then use that in the open command's where clause Dim strJobID As String 'store the unique ID in the variable
[Code] ....
I've put the strJobID variable in both the filter and where clause sections of the DoCmd but it just opens the form to the first entry. I'm fairly confident i'm not applying the filter/where clause correctly by using the incorrect syntax.
I am using MS Access with SharePoint Lists and some Access Tables to generate reports in pre-formatted MS Excel Worksheets. My main module opens several classes. One of the classes receives a worksheet to keep in a historical book. This class can receive any one of several different types of pre-formatted worksheets. After all is said an done the class adds a cover sheet to the consolidated workbook and adds hyperlinks to the other sheets, kinda like a table of content.In the class I have the following in the general declaration.
code: Public Enum BookType A = 1 B = 2 End Enum Private pBookType As BookType
Later in the class I have the following:
Code: Public Property Let LoadBookType(ByVal btNewValue As BookType) pBookType = btNewValue AddWorkBook End Property
in the main modual I have the following
Code: classConsolidated.LoadBookType = A
When assigning the above property it passes in "11"
if I change it to the following
Code: classConsolidated.LoadBookType = BookType.A
it passes in "1" as I expect and use in a Select Case statement later in the class.So why does one pass in "11" but the other passes in "1"?
I am having trouble with the below returning the correct number of records, and can't see why.
I have one table, tblDevice, which has 4 columns, ID | DeviceRecNo | ExcludeFromCheck | StockLocationID
ID - Autonumber DeviceRecNo - Number ExcludeFromCheck - Number (1 = yes, 0 - No) StockLocationID - Number
I have the following running as part of some code, but it is not returning the correct number of records, and I cant see why not. I have tried creating this in a query in Access itself, and copy the SQL into VBA and it still doesn't return the correct number records.
I have 4 records in there for testing, all with StockLocationID = 3, all with different DeviceRecNo, and two each of ExcludeFromCheck, 2x0 and 2x1
Code: Public Function test() Dim db As dao.Database Dim rs1 As dao.Recordset Set db = CurrentDb Set rs1 = db.OpenRecordset("SELECT DeviceRecNo FROM tblDevice WHERE StockLocationID = 3 AND ExcludeFromCheck = 'No'") Debug.Print rs1.RecordCount End Function
I have a query with two table's in them. One is filled, the other is still empty. Both table's are related to each other with the field "document number".
This was working fine in access 2000, now in 2003 it returns zero results. I am starting to think it is because one table is empty it returns nothing. How can i adapt the query criteria or something else that it shows the results from table one, while table two can still be empty. One table holds the documents and all information about it, the other holds the information wich document is referenced on wich document. Now since i can have documens which don't reference to a document, i still want those in the list.
I have a table listing a number of cell phone products and their carrier along with their status' in the production process. I want the query to return the most recent status for each product for each carrier. However, out of the 20+ products that I'd like the most recent status for for each carrier, it's only returning about 12 products of the most recent status for one carrier.
Any help would be much appreciated! Leave a message if you need more clarification.
Item: Access app front end for an SQL database (production tracking/job logging utility)
Problem: The app has a main search page that allows job entries to be located and opened by varies criteria (job#, job name, etc). I can currently run multiple searches on different criteria successfully but if I open a job window(which has been returned as result of my search) and then close that job window to do a new search my search function will no longer return any results(other then “Calculating….”) unless I close out the access app and reload.
Thanks in advance for any ideas that can point me in the right direction to hunt down my bug.
running access 2000. I have a query that I selected to return the Top 1 record sorted descending on the date so it would pick the most recent entry. I did it by selecting Top 5 from the drop down box in query design mode and then changed the 5 to a 1 using SQL View from qry design.
SELECT TOP 1 Class.CourseID, User.UserName, Class.TrainerID, Class.ClassLocationID, Class.ElapsedTime, Class.ClassDate, Class.Comments FROM [User] INNER JOIN Class ON User.UserID = Class.UserID ORDER BY Class.ClassDate DESC;
It still gives me as many records as are entered on the system. How can I get JUST the most recent record entry?
I have built a Project/Resource database for our department, and I am having an issue with 3 computers in the group. First, all of our computers are setup the same (as far as I can tell), however, there are 3 users that have the same issue...when selecting an item from a dropdown, it should populate the dropdown below it with a new list, based on the first dropdown. It is only on certain forms, and only happens on these three computers. If the users log into any other computers, it works fine. I know it's not alot to go on, but does this sound familiar to anyone? I'm at a complete loss!
When I run this query, it shows that 41 records are going to be appended. I click on yes, but when I go into the table to view the appended records they do not show.
Code: Public Sub test() Dim frm As Form Set frm = Forms!StationLevelSummary
[code]...
At the bottom im printing the content of the controls which are on my form. These should return 1 number, but for some reason it does not. Ive used this code many times but I cant figure out why nothing is being returned.
I'm trying to do a string compare between two variables. One string variable is part of an array (which I'm looping through), the other is passed to the function as an argument.the function should return the position of a field in the OrderBy string of a subform.Here's the VBA :
Code:
Private Function SortPosition(strOrderBy As String, strField As String) As StringDim arrSortedFields() As String Dim i As Long If Len(strOrderBy) > 0 And InStr(strOrderBy, strField) > 0 Then arrSortedFields = Split(strOrderBy, ",")
[code]....
The weird thing is, the line in blue returns True when, by watching the variables, it appears that it should not, and returns False when it appears that it should?
I would have thought the expression "[RandomField]" Like "[RandomField]*" should return True?And similarly the expression "[SomeFieldName]" Like "[ADifferentField]*" should return False?Have been using 'Like' for donkey's years and never seen it throw results like this before?
(P.S. The reason I need to use 'Like' rather than a straight = is to account for the possibility that a field may be sorted descending, and therefore to nullify the DESC keyword which may follow any given field...)
I have a db with 2 tables, one containing basic info FirstName, Surname, YearGroup, and another table with other data. I imported a list of names into the first table, firstname and surname only were imported, other field was left empty, other table was also left empty. I made a simple select query to pull all records from both tables and i get no results at all. None of the imported names show on the query results. what I am missing?
SELECT CPL_CLIENTS.CODE, CPL_CLIENTS.FULLNAME FROM CPL_CLIENTS WHERE (((CPL_CLIENTS.FULLNAME) Like "*" & Forms!frmSafeKeep_AddClient_SearchDesig!txtSearchT ext & "*"));
As the control source of a listbox, so that the user can enter something in "txtSearchText", and the listbox will be narrowed to only those those clients that have a matching part of their name.
The problem is that the user enters, for example, "duncan", and the list returns nothing, when in actual fact there are 5 matching records. If the user enters "Duncan" (note the capital letter), they get all 5 records as they should.
If memory serves, access should only be case sensitive if you tell it to be - and this is working fine on other forms.
The only difference between them is the data source. Other forms are getting data from linked tables in the backend. This form is getting it from a linked Oracle table.
I have a query which pulls up results based on the value of a text box. All works well except one field has a DOB (date) and if I search say for " /1976" I get no results, however search /76 and all the people with that year of birth show then show up. The date is stored in the dd/mm/yyyy format.
I'm using the like [forms]![clients]![searchfield] in the criteria of the query to bring up results.
I have two tables Contacts & Allowance. There is a relationship defined between the two based on Contact ID (primary field/contacts - foreign key/allowance). The Contacts table has Firstname + Surname. I have produced a query based on certain fields in both tables however the result is totally pulling incorrect info.
I have a switchboard which runs a google style search query to find people based on their first name, surname, date of birth or NI number on clicking the search button. It uses the following criteria in the query Like "*" & [Forms]![SearchF]![Firstname] & "*" for each of the above fields.
This works perfectly until someone inputs a record that doesn't have anything in one of the fields. ie, full name and DoB, but no NI number. When a record is entered in this way, the table stores the record but the search query cannot find it.
What is the best approach to returning calculated results in a query. I have been using nested IIf statements that include DateAdd but I think that I am at a point where there must be a better way.
I want my query to calculate a date based on: 1) A Type field from my table 2) A calculated date based on other query fields.
My query has the following fields: [Type] [Action 1] [Projected Action 1] [Projected Action 1 Revised] [Action 2]
I want to create a calculated field for [Projected Action 2] that says:
If [Type] is "A" then if [Action 2] Is NotNull, Null if [Action 1] is NotNull, [Action 1] + 10 workdays if [Projected Action 1 Revised] is NotNull, [Projected Action 1 Revised] + 10 workdays if [Projected Action 1] is NotNull, [Projected Action 1] + 10 workdays
[Code] ....
I would like to keep using nested IIf but I keep running into problems and I thought that there is probably a better way.
Multi search wont return results: Obviously I have my table and search form. But have hit a problem that is probably simple to overcome, but for the life of me I cant see it.
The small peice of code I have used = forms]![searchF]![name1] I have also tried Like"*" & forms]![searchF]![name1] & "*" still not working. The qurey accompanying form (searchF) works to displaying searchF form unpopulated which I believe is correct. I should at this stage be able to fill out the one table heading (name1) where the query should return a result in the (searchf) form but it does not return.
The tables, forms and quries, have all been saved and closed and re opened many times
The form (searchF) propeties have been checked and double checked. I have even built a test table but the same things happen as my main table.
I am building a prototype database and currently has two records in it (I know for certain they are not duplicates). Attached is the relationship and below is the query. The query keeps returning the correct results but twice. (Duplicate results.)
SELECT DISTINCT WeekdayName([Weekdaynumber],False,1) AS Weekday, [Master Data].Date, [Master Data].Machine, [Master Data].Shift, [Master Data Query].[Master Data_Product Code], [Master Data Query].[Cases/Pallet], [Master Data].[Expected Pallets to Produce], [Master Data].[Actual Pallets Produced], [Master Data].[Damage (Units)],
I have an unbound combo box that I set up in a form using the combo box wizard in Access 2010. I selected the option that says, "Find a record on my form based on the value I selected in my combo box." The problem is that the combo box will not always display the correct record. It seems to only display the first record of a group of similarly named records in a table.
Example:
Code: Last Name First Name SSN Smith Alex 123-45-6789 Smith Jane 234-56-7891 Smith Mary 345-67-8910
If I select Smith Jane, the record for Smith Alex will display. I know that this has something to do with the bound column property, but I am unsure what to do to fix the issue.
What I have tried: I tried setting the primary key as the first field, but then I could only search by the primary key, which is unrealistic for this database. Users will be searching based on last name. How do I make the combo box select whichever record I select from the drop down list?
Code: Private Sub Command26_Click() If Forms![test site]![prp test].Form.[A Right Answer] = -1 Then Forms![test site]![number correct] = Forms![test site]![number correct] + 1 End If DoCmd.FindNext End Sub
Then when clicked it checks a yes/no box to see if "A right Answer" is the correct yes. Then it should pop to the main form and take the number correct cell and add one to it. I am trying to get the record to go to the next record inside the sub-form but docmd.findnext seems to be wrong too.
