Solved

Performing dateadd on nulls

Posted on 2008-09-29
10
285 Views
Last Modified: 2013-11-27
I have a form that passes a set of dates to another form. The number of dates passed can vary from between 1 and 18. The user will select the dates and then press a command button that passes the dates and then performs dateadd on the dates.

I am running into a problem with null values. When a user only enters for example, 6 dates, my code falls over as it is trying to perform dateadd on a null value.

How can I stop this from happening?

Thanks in advance

Ben
0
Comment
Question by:benissitt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
10 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22598107
you can test for null values before performing the DateAdd function.

give more details .. post the codes behind the command button click
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 22599808
(Assuming you are calling a function with Date parameters)

You need to decide if you want to fix this on the calling side or the code that receives the dates.  You can use Nz(YourField, 0) to coerce the nulls into a Date type but you're going have to test for those "magic date" (December 30, 1899) values within the function with DateAdd.

--------
(Assuming the code is just in the button click event,)

If Not IsNull(YourDateField) Then
   'do your DateAdd stuff
Else
   'how do you want to handle this?
End If

------

HTH,

Pteranodon
0
 

Author Comment

by:benissitt
ID: 22603364
Thanks chaps. The code I am using is as follows. I have a possible 18 dates that can be added. The code follows this pattern for the rest of the year. Once it has been passed to the form I click another command button that removes weekends
Forms!MonthlySchedule![txtFebToUs] = DateAdd("m", 1, Me![1st Date])
Forms!MonthlySchedule![txtDate2Feb] = DateAdd("m", 1, Me![2nd Date])
Forms!MonthlySchedule![txtDate3Feb] = DateAdd("m", 1, Me![3rd Date])
Forms!MonthlySchedule![txtDate4feb] = DateAdd("m", 1, Me![4th Date])
Forms!MonthlySchedule![txtDate5Feb] = DateAdd("m", 1, Me![5th Date])
Forms!MonthlySchedule![txtDate6Feb] = DateAdd("m", 1, Me![6th Date])
Forms!MonthlySchedule![txtDate7Feb] = DateAdd("m", 1, Me![7th Date])
 
Forms!MonthlySchedule![txtMarchToUs] = DateAdd("m", 1, Forms!MonthlySchedule![txtFebToUs])
Forms!MonthlySchedule![txtDate2Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate2Feb])
Forms!MonthlySchedule![txtDate3Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate3Feb])
Forms!MonthlySchedule![txtDate4Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate4feb])
Forms!MonthlySchedule![txtDate5Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate5Feb])
Forms!MonthlySchedule![txtDate6Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate6Feb])
Forms!MonthlySchedule![txtDate7Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate7Feb])
Forms!MonthlySchedule![txtDate8Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate8Feb])
Forms!MonthlySchedule![txtDate9Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate9Feb])
Forms!MonthlySchedule![txtDate10Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate10Feb])
Forms!MonthlySchedule![txtDate11Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate11Feb])
Forms!MonthlySchedule![txtDate12Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate12Feb])
Forms!MonthlySchedule![txtDate13Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate13Feb])
Forms!MonthlySchedule![txtDate14Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate14Feb])
Forms!MonthlySchedule![txtDate15Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate15Feb])
Forms!MonthlySchedule![txtDate16Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate16Feb])
Forms!MonthlySchedule![txtDate17Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate17Feb])
Forms!MonthlySchedule![txtDate18Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate18Feb])
Forms!MonthlySchedule![txtDate8Feb] = DateAdd("m", 1, Me![8th Date])
Forms!MonthlySchedule![txtDate9Feb] = DateAdd("m", 1, Me![9th Date])
Forms!MonthlySchedule![txtDate10Feb] = DateAdd("m", 1, Me![10th Date])
Forms!MonthlySchedule![txtDate11Feb] = DateAdd("m", 1, Me![11th Date])
Forms!MonthlySchedule![txtDate12Feb] = DateAdd("m", 1, Me![12th Date])
Forms!MonthlySchedule![txtDate13Feb] = DateAdd("m", 1, Me![13th Date])
Forms!MonthlySchedule![txtDate14Feb] = DateAdd("m", 1, Me![14th Date])
Forms!MonthlySchedule![txtDate15Feb] = DateAdd("m", 1, Me![15th Date])
Forms!MonthlySchedule![txtDate16Feb] = DateAdd("m", 1, Me![16th Date])
Forms!MonthlySchedule![txtDate17Feb] = DateAdd("m", 1, Me![17th Date])
Forms!MonthlySchedule![txtDate18Feb] = DateAdd("m", 1, Me![18th date])

Open in new window

0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 22604417
use an iF then statement

If not isnull(Me.[1st Date]) then Forms!MonthlySchedule![txtFebToUs] = DateAdd("m", 1, Me![1st Date])

If not isnull(Me.[2nd Date]) then Forms!MonthlySchedule![txtDate2Feb] = DateAdd("m", 1, Me![2nd Date])


etc....
0
 

Author Comment

by:benissitt
ID: 22604830
Hi

This seems to work for the first 7 dates that I have on my form but not for any dates after that. If I leave dates 1-7 blank then it works. However, when I leave dates 8-18 blank it falls over and gives runtime error 13 type mismatch. However, all of my textbox references are correct. When I go to debug and hover over the textbox reference in my code, I can see that it is falling over at any date after Date 7 that doesn't have a value. This is really confusing me.

One thing I can think of that may be contributing is that I originally designed the form with only 7 dates. I recently added dates 8-18 by copying the text boxes on the form and renaming them Date 8 - Date18 etc
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22604887
check the codes that you created


If not isnull(Me.[1st Date]) then Forms!MonthlySchedule![txtFebToUs] = DateAdd("m", 1, Me![1st Date])

If not isnull(Me.[2nd Date]) then Forms!MonthlySchedule![txtDate2Feb] = DateAdd("m", 1, Me![2nd Date])

'to

'7


if not isnull(Forms!MonthlySchedule![txtFebToUs]) then Forms!MonthlySchedule![txtMarchToUs] = DateAdd("m", 1, Forms!MonthlySchedule![txtFebToUs])

if not isnull(Forms!MonthlySchedule![txtDate2Feb]) then Forms!MonthlySchedule![txtDate2Mar] = DateAdd("m", 1, Forms!MonthlySchedule![txtDate2Feb])

and so on...



0
 

Author Comment

by:benissitt
ID: 22605191
I have worked this issue out now, it was due to the data types being different in my table.

I am going to be removing weekend dates from the dates that are created, am I correct in thinking that I will avoid problems with null if I use the following code. (I am just wanting to add 1 date onto a Sunday and 2 dates onto a Saturday)

If Not IsNull(Weekday(Me.[txtJanToUs])) Then
txtJanToUs = DateAdd("d", 1, [txtJanToUs])
End If
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22605265

If Not IsNull(Weekday(nz(Me.[txtJanToUs],0))) Then
txtJanToUs = DateAdd("d", 1, [txtJanToUs])
End If
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 22605295
better, to handle sunday

If  Weekday(nz(Me.[txtJanToUs],0))=1 Then
txtJanToUs = DateAdd("d", 1, [txtJanToUs])
End If
0
 

Author Closing Comment

by:benissitt
ID: 31501247
Thankyou this works great
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question