DateAdd() In WHERE Clause Not Working?

Aug 1, 2005

This is driving me crazy! What am I missing?

Here's my query:

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).

Anybody see my problem?

View Replies


ADVERTISEMENT

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.

What am I doing wrong?
:confused:

View 4 Replies View Related

IN Clause Not Working With Parameters

Nov 2, 2005

Hi all: I am trying to filter a query based on user input from a ListBox. I loop thru the ListBox and build a comma-delimited list of IDs. It takes one parameter, but if there are 2 or more items in the list, it returns nothing. It's not the query, because if I hard code the same comma-delimited list into the SQL query ("...WHERE CategoryID IN (1,2,3)") it works fine.

This is the query:

SELECT tbl_Subsidiary.SubsidiaryName AS Brand, Sum(tbl_Placeview.Duration) AS SumOfDuration
FROM tbl_Subsidiary INNER JOIN tbl_Placeview ON tbl_Subsidiary.SubsidiaryID = tbl_Placeview.SubsidiaryID
WHERE (((tbl_Placeview.AirTime) Between [Forms]![frmTopBrandsByCategory]![txt_DateFrom] And [Forms]![frmTopBrandsByCategory]![txt_DateTo]) AND ((tbl_Placeview.CategoryID) In ([Forms]![frmTopBrandsByCategory]![txt_CategoryList])))
GROUP BY tbl_Subsidiary.SubsidiaryName
ORDER BY Sum(tbl_Placeview.Duration) DESC;

You can see I'm getting the list of "CategoryID" from a text field; after getting my comma-delimited list from the list box, I assign it to a hidden text field. In debug I can see that the list of IDs is built correctly.

Any ideas? I'm pulling out my hair over this!

Thanks in advance,
james

View 11 Replies View Related

Need Help With DateAdd()

Sep 14, 2007

I have a table that includes a client account number, revenue, and a Date in the format YYYYMM. I would like to create a query that sums the revenue for a client where the date is six months prior to today.

Thanks in advance

View 2 Replies View Related

DateAdd

Oct 13, 2004

I think I have a simple problem: In a database table I've made there is a field called <date> (in the date&time format), in the table there is another date field, which should contain a date always 6 months later than the <date> field. For conveniance, I created a form to fill in all other fields in the table, including the <date> field. Succesfully I tried to calculate the 6-months-ahead-date in the form with DateAdd, but I tried everything I could (which is not so much) to store this result in the table, since I need it there for a few reports I generate.
Who can help me to solve this problem??

View 2 Replies View Related

Dateadd Issue

Feb 18, 2007

Hi,

This problem has to be easy but I can't make it work. The attached database has a table name BOL information. Inside that table are the two columns I'm having an issue with. I want ETA Date to be 30 days after BOL date. I have been screwing around with the Dateadd function and can't get it to work. I have been putting the following in the default value of ETA date:

Dateadd("d",30,[BOL Date])

The error message I have been getting is The database engine does not recognize either the field 'BOL Date' in a validation expression, or the default value in the table 'BOL information'.

What am I doing wrong? Should I not be putting the formula in the default value? Should I use another way to get this calculation? Your thoughts are most welcome.

Thanks in advance.

Eddie.

View 2 Replies View Related

Question On Using DateAdd....

Jan 3, 2008

I have a table that tracks the inspection dates for various structures. I have a field called InspectionDate in the form of 99/99/9999. Another field called Interval which can range from 6 months to 48 months. I have always gone in to edit another field called InspectionDueDate. Is there a way to have the InspectionDueDate field automatically be filled based on the other 2 fields? Does this make any sense? Thanks in advance.

View 4 Replies View Related

DateAdd Weirdness

Jun 28, 2005

Howdy all,

I've written a simple little function using the DateAdd system function which i call from query. Here's the logic

If (PassFail = "Pass") Then
IsNull (FirstRepairDueDate)
Else
FirstRepairDueDate = DateAdd("d", 5, InspectionDate)
End If

basically if the PassFail argument is "Pass" I would like the function to not display anything, however i'm not sure what to do because my function returns a data type of Date, which it defaults to 12:00:00 AM using the above logic.

Any ideas?

Many thanks

View 5 Replies View Related

DateAdd Problem

Oct 23, 2005

I'm VERY new at using VBA although I have been programming for many years.

I have a requirement that involves creating a new version of a record and setting the expiry date of the current version to one day less than the effective date of the new version.

Regardless of what I try, the new expiry date that gets set is always 12/30/1899.

I suspect there is something very elementary that I'm missing, but haven't been able to figure out what it might be. Perhaps I need to apply a function to the setExpiryDate variable?

Any suggestions would be appreciated. Here is the current version of the code I'm working with:

Private Sub createNewVersion_Click()

Dim strUpdate As String
Dim intervalType As String
Dim adjustment As Integer
Dim setExpiryDate As Date
Dim newEffDate As Date

intervalType = "d"
adjustment = -1
newEffDate = Forms!fCreateDomainVersion!NewEffectiveDate
setExpiryDate = DateAdd("d", -1, newEffDate)

strUpdate = "UPDATE tDomain " & _
"SET ExpiryDate = " & setExpiryDate & _
" WHERE DomainName = Forms!fCreateDomainVersion!DomainName AND " & _
"EffectiveDate = Forms!fCreateDomainVersion!EffectiveDate;"

DoCmd.RunSQL strUpdate

End Sub

View 1 Replies View Related

DateAdd Question

Nov 27, 2005

Hello

I'm very new at this - just found out about the DateAdd function today trying to solve my problem, so that you know who you're dealing with.

Let's say Im operating a video rental shop, and want to calculate a DateDue (=when videos need to be returned) based on DateRented and number of videos rented..

DateAdd ("d", 5, [DateRented]) would be appropriate for 5 videos rented at once.

I want to replace 5 here with a number of videos rented, and this number (TAPECOUNT below) is also calculated in a query using count function (to count the number of videos associated with each rental)

Preferably I would end up with a single query consisting of 4 columns:
RENTAL_ID, TAPECOUNT, DATERENTED, DATEDUE, of which both 2nd and 4th column would be calculated, 2nd in combination with 3rd serving as input for 4th.

Can it be done like this and how? Would I need to store TAPECOUNT value first? Is DateAdd even a proper function for my task? I'm open to all suggestions, just keep in mind I'm a newbie.

View 4 Replies View Related

DateAdd + Days

Jun 19, 2006

I have a table that has several fields however the ones I'm working with are Date, Letter, RollOff.

Date being the date the record was entered example 6/1/2006

Letter can be letter codes simply a,b,c,d,e etc

RollOff is 1 year from the Date the record was entered.

I've searched though several pages of dateadd info but haven't ran across an exact similar situation so far.

Getting the RollOff is no problem, simply DateAdd("yyyy",1, [Date]). However what I'm attempting to do is I need to add a certain amount of days to the calc between the date entered and the 1 year rolloff that match the Letter of "B".

Each record entered will only have 1 Letter and each entry is only for 1 day.

I'm thinking I will need a second query that will gather the sum of "B" codes to determine the amount of days needed to add to my DateAdd calc.

sample layout would be.

Date Letter RollOff
6/1/2006 A DateAdd("yyyy", 1, [Date]) + 3 days of B Codes for 6/2,3,4
6/2/2006 B
6/3/2006 B
6/4/2006 B
6/5/2006 C

Since 6/1/2006 RollOff is 1 year 6/1/2007 however since there was "B" enteries between the time the record was entered and the RollOff date the amount of days needed to be added so the RollOff would actually be 6/4/2007.

Any help would be appreciated.

View 5 Replies View Related

DateAdd Problem

Jan 13, 2007

I have table with two fields, [StartTime] and [FinishingTime].

On an unbound form (frmGameBooking), the [StartTime] is entered and also (via a combo box) the number of hours i.e. 1 hour, 2 hours, etc.

On pressing a command button the form, an update query is run to populate the [FinishingTime]. If the [StartTime] is chosen to be say 11:00 AM and the number os hours is selected to be say 2 hours, the update query places the value 13:00 for the [FinishingTime]. However if I choose a [StartTime] of say 23:30 PM and select 1 hour, the update query doesn't update the [FinishingTime].

Can anyone help. Here's the SQL code of the update query:-

UPDATE tblTables SET tblTables.BookingTime = Forms!frmGameBooking!TimeValue, tblTables.FinishingTime = Right(DateAdd("s",3600*CInt(Left(Forms!frmGameBooking!No_of_Hours,1 )),Forms!frmGameBooking!TimeValue),8);

The Right function is used to obtain the time portion.

View 8 Replies View Related

DateAdd Syntax

Feb 22, 2005

Hi

I have a combo box on my form. I wish to set the value of a text box on lost focus of the combo to be the value of the combo cbxDateFrom +6 months

The code I am using is

Private Sub cbxDateFrom_LostFocus()
cbxDateTo.Value = [DateAdd("mmmm", 6, me.cbxDateFrom.Value)]
End Sub

But it does not appear to work in any variant I have tried. If it is easier to do it in properties or event builder I don't mind that, I just need the right syntax

Regards

View 5 Replies View Related

DateAdd Function

Sep 24, 2004

I am using the following to populate text boxes that are functioning as labels in my report. I am getting an error: #NAME. Do you know what's wrong with this code??

=DateAdd("d",-1,Forms!frmDates!txtEnd)

This prompts me for txtEnd. When I put in the date (which is a parameter from my SQL cross tab query, the values in the report WORK, but the label doesn't. Any ideas?

View 1 Replies View Related

DateAdd Functions

Mar 10, 2005

I enter this statement on my control source in a text box
of a report for a date field.

=DateAdd("d",1,[startdate]

I get error when I preview the report. I trying to add 1 day
to the date.

Thanks in Advance

View 2 Replies View Related

Dateadd (Validation Rule)

Jan 12, 2005

Hi,

I have a field "dimissioni" data/time field and I' m trying validatation rule Dateadd ("d"; [giorni_somma] ; [data_ingresso ]) where "giorni_somma" is a field number integer and data_ingresso is other field Date/Time; my problem is access does't find field "data_ingresso" and "giorni_somma". If I use Dateadd ("d";"2";Now()) access works fine and validation rule is ok.
I hope in help.
Best Regards.

Delfo

View 1 Replies View Related

Complex DateAdd Function

Aug 4, 2006

Hi All,

i have a slight problem i have a access table which has the following fields:

Name
Photocard ID
Purchase Date
Photo
Cashsaver Zone
Valid From
Period of Validation
Expiry

the period of validation field has a lookup to another table with the folwing values: 28 Days, 3 Months, 6 Months and Annual.

the valid from date is entered manually.

basically (maybe not uite the right word!) i need the the expiry field to automatically insert the correct expiry date by looking in the valid from date and adding the correct amount of time onto it according to what is selected in the period of validation field.

example:
Valid from date is 01/01/2006
Period of validation is 6 Months
Expiry date should be 01/06/2006 (01/01/2006 + 6 Months)

i came up with the following formula although i know its not really correct and some of the words arent functions at all just to show what is should be based on:

WHERE Period of validation <= DataValue ("28 Days") then DateAdd("d",28, Period of validation)
WHERE Period of validation <= DataValue ("3 Months") then DateAdd("m",3, Period of validation)
WHERE Period of validation <= DataValue ("6 Months") then DateAdd("m",6, Period of validation)
WHERE Period of validation <= DataValue ("Annual") then DateAdd("m",12, Period of validation)

also i am unsure into what box to type this into?

i would greatly apperciate some help

Spindlemania

View 6 Replies View Related

Simple Dateadd Query

Sep 12, 2005

Hi

Sorry if sometihng similar has been posted.

But all I want to do is get this to return data that is a month from now

Gate: IIf(Format([7 PDB],"mmyy")=Format(Date(),"mmyy"),"Move to Delivery")

Any help appreciated

PK

View 1 Replies View Related

Dateadd Not Calculating Properly??

Nov 18, 2005

Hi,

When i use this

between #01/07/2005# and #01/09/2005#


It all works ok and pull outs what i need.

But when i do

between #01/07/2005# and DateAdd("m",3,#01/07/2005#)


I have also tried it without the # and with 2 instead of 3.

No luck :(

Any ideas?

Thanks
k0r54

View 11 Replies View Related

Ac97 Replacement For DateAdd()

Mar 15, 2006

Can anyone suggest a work around for the DateAdd() function. I have Access 97 and this function is not available.

View 3 Replies View Related

Dateadd Query Problem

Sep 2, 2007

Hi all,

I am trying to create a query that will calculate the date for a follow up appointment based on a dateadd function which uses a starting date and an interval (both held in a table) and then only display certain of the results based on a parameter (another date) defined when the query is run. The SQL statement I have used is as follows;

SELECT Schedule.[Pt Name], Schedule.Regime, Schedule.[Recall Interval], DateAdd("d",[Recall Interval],[Appointment Date]) AS [Next Appointment]
FROM Schedule
WHERE (((DateAdd("d",[Recall Interval],[Appointment Date]))=[Enter Date]));

However when I run the query i get an error message "This expression is typed incorrectly, or it is too complex to be evaluated." The dateadd function works fine without the parameter query so I take it that this means access doesn't like working out a date and then filtering the results according to a parameter in the same query. I am clearly going about this in the wrong way so can anyone suggest a better way of achieving the same outcome.

Thanks.

View 14 Replies View Related

DateAdd / Comparison A97 Problem

Feb 8, 2008

I'm running a query to find records over one year old. When I use Date()-"365" or DateAdd (365 days, 12 months, 4 quarters, or 1 year) the records from one year ago today come up just like they are suppose to. The problem comes in when I put the comparison operator out front.

If I put < in front of either Date() or DateAdd minus one year, I get all records from yesterday and earlier as if it is ignoring the year and only looking at the month and day. If I put > in front, I get today's records.

Date() and DateAdd by themselves are working as advertised. Things only get weird when I put < or > out front.

What am I doing wrong?

Thanks,
Forrest

View 2 Replies View Related

DateAdd Or DLookup Problem

Jul 11, 2005

I am using a DLooking Statement to pull a persons birthdate from another table and populate a unboundbound text box in my form bound to different table. This statement appears to work and pulls in the create date. I say appears because it put a # on either side of the date it pulls in-ie #06-June-91#
Here is the code:

--------
DLookup("Birthdate", "tbl_clientinfo", "ClientID = " & Me.SCVClientID) & _
"#"
-------

I then use the dateadd function to determine the value of a boundfield named "statoflimits". When I fire the event by putting adate in my accidentdate field I get an errorcode 13 typemismatch error on the first line of my subroutine. Here is the subroutine.
Private Sub AccidentDate_AfterUpdate()
If DateAdd("yyyy", 18, Me.Txt14) > Me.AccidentDate Then
Me.StatofLimits = DateAdd("yyyy", 20, Me.Txt14)
Else
Me.StatofLimits = DateAdd("yyyy", 2, Me.AccidentDate)
End If

End Sub

Can anyone help?

View 14 Replies View Related

Can I Use DateAdd To Refer To A Pre-entered Date?

Apr 20, 2008

Hi again

I've done some searching on this question and it seem everyone is pointing to the DateAdd function, so here is my current problem.

I have a date/time field called SponsorshipContractDate. When a contract is signed the date is entered. I need the system to calulate the renewal date for the contract which is alway 1 year ahead.

The DateAdd function works only if I hard code at date into it e.g.

DateAdd ("yyyy", 1, "01-Jan-08")

But as contracts are signed throughout the year this just doesn't work so I tried this:

DateAdd ("yyyy", 1, "SponsorshipContractDate") but the table won't save and this is abviously not right. In my head it makes sense but I don't understand how to get the formula to look at another field during it's calculations

Am I using the wrong function?

I will keep looking around.

Thanks heaps

View 4 Replies View Related

How To Get DateAdd In A Query To Work Through Other Fields?

Sep 6, 2006

Hi,

I am trying to get DATEADD to work in a query where the parts are called by different colums on the query like so:

Code:ExpiryDate: DateAdd("" & [DDType] & "",[DDNum],[Date])
At the moment the above will return the [DATE] without adding on the numbers

The [DDNum] is the number and works fine
The [Date] is the date and works fine

I know this becase the following code will give a new date
Code:ExpiryDate: DateAdd("d",[DDNum],[Date])

Could someone kindly let me know what I am missing in my syntax to get this working - I can get it working in ASP but I also need it to work in an Access Database

thank you for your time

View 2 Replies View Related

How To Update A Field Using DateAdd And IIF In A Form

Dec 13, 2014

I have a field in a form which indicates which date the person was last contacted [LastContact_Date] and I have a second field which states if the person is either ACTIVE or INACTIVE [ActiveInactive]. You would become inactive if the last time you were contacted was greater that 6 months ago.

I built an update query which works. It has the following IIf statement

IIf(DateAdd("m",6,[tblMAIN]![LastContact_Date])>Now(),"ACTIVE","INACTIVE")

It would be good if the [ActiveInactive] field would update when you tab past the [LastContact_Date] field but not essential.

Which is the best way to update a form field based on another form field? Do I use this update query in the [LastContact_Date] field or the [ActiveInactive] field in the form or table? Is there a better way than an update query to do the same thing?

View 4 Replies View Related







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