Date Function Issue

[lk2827]

Hi,

Could somebody please help me, Im having a bit of trouble making a automatic date appear in a table field.

What I want is as follows:

I have a date field called "Last Test Date" which I input a date manually, I also a text field called "Frequency Of Test" which I input either Daily, Monthly or Yearly manually.

I then have a text field called "Next Test Date" which I want the date to be automaticly entered based on the above.

I have tried a if statement below, date function in access etc but still can not get it to work.

Below is what I have tried.

If [Frequency Of Test] = 'Yearly' then
[Next Test Date] = [Last Test Date] + dateadd (y,1,date()))

ElseIf

If [Frequency Of Test] = 'Monthly' then
[Next Test Date] = [Last Test Date] + dateadd (m,1,date()))

Elseif

If [Frequency Of Test] = 'Daily' then
[Next Test Date] = [Last Test Date] + dateadd (d,1,date()))

EndIF

If anyone could help me further on this I would much appericate it.

Thanks

Lee

 

[ajetrumpet]

Just a couple of things here:

1) dateadd syntax is this:

dateadd("interval abbreviation", start date, end date)

2) elseif sections of 'IF' statements should look like this:

elseif "code"

elseif "code"
  else
end if

So, maybe the following correction will work for you:

If [Frequency Of Test] = 'Yearly' then 
[Next Test Date] = [Last Test Date] + dateadd ("y",1,date()))
 
ElseIf [Frequency Of Test] = 'Monthly' then 
[Next Test Date] = [Last Test Date] + dateadd ("m",1,date()))
 
Elseif [Frequency Of Test] = 'Daily' then 
[Next Test Date] = [Last Test Date] + dateadd ("d",1,date()))

else

EndIF

 

[Brianwarnock]

Erm
DateAdd Function


Returns a Variant (Date) containing a date to which a specified time interval has been added.

Syntax

DateAdd(interval, number, date)
ie

Dateadd("y",1,Date())
Adam there are not 2 dates however you then coded it correctly.
is the last eEseif necessary cannot that just be the Else?

Brian

 

[boblarson]

1) dateadd syntax is this:

dateadd("interval abbreviation", start date, end date)

Oops, a rare goof - actually that is the DateDiff syntax. The DateAdd syntax is like what Brian posted.

 

[lk2827]

Date Issue

Hi Brian & Adam,

Thanks for your suggestions, unfortunately it still does not work. When I tried your suggestions Adam VB said there was a syntax error.

Do you have any further suggestions on how I can get this working.

Thanks

Lee

 

[raskew]

Hi -

Change: Dateadd("y",1,Date()) to Dateadd("yyyy",1,Date())

"y" returns today's date, at least on my system.

Bob

 

[lk2827]

Date Issue

Hi Bob,

Thanks for your suggestion. I have now tried the function in the default value of the field form and now have a name error #Name? in the next test field where the date should be. I think #Name? means that the name is the same as the control source so I have changed it but the #Name? is still there.

Any suggestion on how I can get round this?

Thanks

Lee

 

[boblarson]

You don't happen to have a field with the name of DATE now do you? That is an Access reserved word and shouldn't be the name of any object or field name.

 

[lk2827]

Date Issue

Hi

Nope non of the fields that im am trying to use have the word date in them

Lee

 

[boblarson]

So, what is it you have actually entered into the default?

 

[lk2827]

I have entered
=if([Frequency Test Daily]='Daily',[Next Test]=[Last Test] +Dateadd("yyyy",1,Date()))

 

[raskew]

Hi -

Play with this and see if it provides any ideas. An Iif() statement must contain both True and False options.

Public Function Frequency(strFrequent As String) As Date

Dim x As String
Dim y As Date

x = strFrequent
y = DateAdd(IIf(x = "Yearly", "yyyy", Left(x, 1)), 1, Date)
Frequency = y

End Function

Once copied to a module, test it from the debug (immediate) window
like this:
? frequency("Daily")
5/11/2008
? frequency("Monthly")
6/10/2008
? frequency("Yearly")
5/10/2009

HTH - Bob

 

[boblarson]

Okay, you have a few issues here.

1. You can't use that in a control source as written.

2. I thought you had three possible types, ("Daily", "Monthly" and "Yearly).

3. I would just create a function to return the correct value:

Put this in a STANDARD (not form) module and name the module something like modTests.

Then, paste this function in:

Public Function NextTest(dteDate As Date, strInterval As String) As Date
     Select Case strInterval
         Case "Yearly" 
              NextTest = DateAdd("y", 1, [Last Test Date])
         Case "Monthly"
              NextTest = DateAdd("m", 1, [Last Test Date])
         Case "Daily"
              NextTest = DateAdd("d", 1, [Last Test Date])
     End Select
End Function

Then, just call it using

=NextTest([Forms]![YourFormName]![LastTest],[Forms]![YourFormName]![Frequency Test Daily])

In the text box's control source.

You don't need to store it, just calculate when needed.

 

[lk2827]

Hi Bob,
I created the module using the above code, then I tested it and there were no errors. I saved it and then ran it using a macro. The macro just opend the code. The next test field is still blank.

Am I doing something wrong?

 

[lk2827]

ignore my above post didnt see your reply. I will try it now

 

[lk2827]

I just tried what you said above in my form control and got a #name? error. So I tried it in my table and it could find the form?

 

[lk2827]

I just tried what you said above, I coped the function code created a new module and saved it as modTests. I then went the form and entered the other code to call it in the control. It displayed a #name error. I also tried it in the table and it couldnt find the form?

 

[boblarson]

When you entered this:
=NextTest([Forms]![YourFormName]![LastTest],[Forms]![YourFormName]![Frequency Test Daily])

into the form, did you change the YOURFORMNAME to the actual name of your form?

 

[ajetrumpet]

Oops, a rare goof - actually that is the DateDiff syntax. The DateAdd syntax is like what Brian posted.

Guess it's time to take a break. thanks...

 

[lk2827]

Hi Yes I did do that I changed the name to my form name