Ac97 Replacement For DateAdd()
Mar 15, 2006Can anyone suggest a work around for the DateAdd() function. I have Access 97 and this function is not available.
View RepliesCan anyone suggest a work around for the DateAdd() function. I have Access 97 and this function is not available.
View RepliesA curious problem, I import CSV data into an AC97 table and this has worked without any problems for years. However we have recently taken on a new member of staff and one of the fields being loaded are his initials 'SF' and this results in Import errors. I tested any other letters 'ME','MF' etc. and no problem - is 'SF' some kind of reserved word ?.
Simple solution - call him 'SEB' not 'SF', but I wondered if anybody else has come across this ?
First off, hello to everyone here. I have been using these forums for a couple of months now and they have helped me solve many, many problems.
I've not needed to register before now because everything I needed to find out I was able to find an answer for, so THANK YOU:)
Anyways, back to business............................
I've built a reporting application where the users basically enter details on a form that performs a few lookups to autofill some info and they input the rest.
What happens next is when they submit the 'form', it appends details to a table and sends an email with the details entered to an address specified by a populated text box on the form
One of 3rd party contractors has requested we send this info in .snp format instead of just referencing the textboxes in the body of the mail.
I have created a duplicate of the form in snapshot but cannot figure out how to do the following:
1. Reference the open form to populate the fields
2. Then attach this to the email
Any help would be greatly appreciated.......as I'm tearing my hair out (and I don't really have much left to tear out anyway)
Many thx in adv
Hello,
If somebody mistakenly replaced a database, is there a way to get it back? The database was created with Access 2000 and the OS is XP.
Thanks,
Maritza
I encountered a problem in MS Access 2000. When I needed to replace '#' with letter 'c' there was a problem in first the 'find' and then the 'replace'. Is there any other way to do this?
View 4 Replies View RelatedIs there a way to DCount Multiples?
=DCount("*","[Personnel Information]","[prp-requires replacement] = -1"&"[PRP Position Status]='Certified' or 'postured to PRP position'")
is what I got and obviously having issues with. I basically want to count who has prp requires replacement check that are currently certified or postured.
I am in the midst of migrating an Access 2003 database to Access 2007 and one of its most important module uses the filesearch object to transfer table data from word files into a table in the databse.
Code:
Option Compare Database
Option Explicit
Public Function ImportPathTableData()
Dim objWord As Object
Dim objDoc As Word.Document
Dim db As Database
Dim data As Recordset
Dim bWord As Boolean
Dim strFolderName, strNPID, strTemp As String
Dim tablenb, table, row, column, i As Integer
[code]....
Is there a good replacement for the use of local forms in an Access database? We have a large A97 replicated database application that we are trying to update. We use local forms for data output. Since these are no longer available we are looking for a workaround.
many thanks
a couple of months ago i found a celndar control replacement that Microsoft kindly provided. i have made a few mods that allow me to change the day/week etc. i would now like to tell it to open on a certain date. the reason is that on each of my client records i have 'Future Appointments'. i would like to be able to select a future appointment and click a button that then opens a form that includes the calendar and go to the date of the future appointment. the calendar controls the diary. whatever date the calendar is on the diary matches. so to change to the date of the appointment i need to change the calendar date.
i tried the obvious first.
[Forms]![frmDiaryMultiView]![frmCalendar].Value=#01/01/2012#
that didnt work as the calendar does not seem to recognise it.
i am totally lost with this as i dont even understand most of the code that is behind the calendar, so i dont know what i am looking for to use to open it on a given date.
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
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??
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.
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 RelatedHowdy 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
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
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.
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.
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.
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
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?
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
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
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
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?
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
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