467,104 Members | 1,055 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,104 developers. It's quick & easy.

Building an expression in Default Value to return max value for multiple fields

I have fields in a table that include dates of meetings, phone calls, mailings, and other contact with clients. I have one field that is "most recent contact date". I want that default value to be the most recent date among all the types of contacts. How would I build this expression in the default value area of the table? I basically need something that will return the most recent date among 5 fields. Thanks!
May 25 '07 #1
  • viewed: 2330
Share:
37 Replies
JConsulting
Expert 512MB
I have fields in a table that include dates of meetings, phone calls, mailings, and other contact with clients. I have one field that is "most recent contact date". I want that default value to be the most recent date among all the types of contacts. How would I build this expression in the default value area of the table? I basically need something that will return the most recent date among 5 fields. Thanks!
Drop this into a code module. Notice the way you use it is in comment # 1

Expand|Select|Wrap|Line Numbers
  1. Function MaxFromList(ParamArray Values())
  2. ' Used in a query   MyHighDate:MaxFromList([date1],[date2],[date3])
  3. ' Selects highest value from the arguments.
  4. ' Use arguments of same type for good results.
  5. ' This uses the date serial by turning the date into Long
  6. Dim intN As Long
  7. 'On Error GoTo Failure
  8.     MaxFromList = Null
  9.     For intN = 0 To UBound(Values)
  10.         If IsNull(MaxFromList) Then
  11.             MaxFromList = Values(intN)
  12.         ElseIf Values(intN) > MaxFromList Then
  13.             MaxFromList = Values(intN)
  14.         End If
  15.     Next intN
  16.     Exit Function
  17. Failure:
  18.     MaxFromList = Null
  19. End Function
  20.  
May 25 '07 #2
I am a novice Access user, so I couldn't understand entirely how to use the information you gave me. Can I cut and paste some code into the "Default Value" line in my table design, such as:

=MaxFromList([first call],[second call],[mailing],[meeting date],[thank you],[follow up call])

I know this isn't right, because I get an error message from it. But I think I need something along these lines.

Are you saying I have to run a query first to get the default value I want for my "most recent contact date" field?

Thanks again!
May 25 '07 #3
JConsulting
Expert 512MB
I am a novice Access user, so I couldn't understand entirely how to use the information you gave me. Can I cut and paste some code into the "Default Value" line in my table design, such as:

=MaxFromList([first call],[second call],[mailing],[meeting date],[thank you],[follow up call])

I know this isn't right, because I get an error message from it. But I think I need something along these lines.

Are you saying I have to run a query first to get the default value I want for my "most recent contact date" field?

Thanks again!
the way I read your question, you had moer than one field in your table that had dates in it, and you wanted the highest date in a separate field. If that's not correct, can you elaborate a little for me?
J
May 26 '07 #4
NeoPa
Expert Mod 16PB
I think what you need is a data entry form which includes all the date fields mentioned. In the OnCurrent event of the form, you would put a call like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.LatestDate) Then _
  3.     LatestDate = MaxFromList([first call], _
  4.                              [second call], _
  5.                              [mailing], _
  6.                              [meeting date], _
  7.                              [thank you], _
  8.                              [follow up call])
  9. End Sub
May 26 '07 #5
I think what you need is a data entry form which includes all the date fields mentioned. In the OnCurrent event of the form, you would put a call like :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.LatestDate) Then _
  3.     LatestDate = MaxFromList([first call], _
  4.                              [second call], _
  5.                              [mailing], _
  6.                              [meeting date], _
  7.                              [thank you], _
  8.                              [follow up call])
  9. End Sub
I think this is just what I need. I have 2 questions, though. What do I fill in the blanks with? And what do I put in for (Me.LatestDate)? Would it be (latest contact date), which is the name of that field? I've never used the Code Builder, but I essentially copied and pasted your code into it. Once I have everything right, do I need to do anything else? When I open the form again, should it just work like magic? Thanks!
May 29 '07 #6
NeoPa
Expert Mod 16PB
Firstly, although the code will work perfectly well as-is, I should have started line 3 with "Me.". It should have said :
Expand|Select|Wrap|Line Numbers
  1. Me.LatestDate = ...
Although the effect is exactly the same, I should have for consistency and understandability. Is that what led to your second question? Otherwise I have no idea what you're asking there I'm afraid.
The same is true for the question about blanks. What blanks are you referring to :confused: ?
You did the right thing to copy the code into the VBA Code window. That much I understood :) And yes, once it's all correct it should just work like magic. If you ever start playing with the code you'll be amazed and the clever things you can make it do.
May 29 '07 #7
NeoPa
Expert Mod 16PB
You know I presume, that the MaxFromList() function must also be added to your project before the code I posted can work, don't you?
May 29 '07 #8
You know I presume, that the MaxFromList() function must also be added to your project before the code I posted can work, don't you?
You'll have to assume I don't know anything. I don't know about the MaxFromList() function. Where do I add that? Thanks!
Jun 12 '07 #9
NeoPa
Expert Mod 16PB
You'll have to assume I don't know anything. I don't know about the MaxFromList() function. Where do I add that? Thanks!
You'll find it in post #2. If you're still not clear what you're doing after rereading that then please come back and explain clearly and precisely where you're stuck.
(Hint) I don't want to spoonfeed too much so an "I don't know anything" response will not be looked upon with too much favour.
Jun 12 '07 #10
You know I presume, that the MaxFromList() function must also be added to your project before the code I posted can work, don't you?
I input this into the "Control Source" line under the "Data" tab in the Properties of the text field for "latest contact date":
Expand|Select|Wrap|Line Numbers
  1. =MaxFromList([contacts]![first call],[contacts]![second call],[contacts]![mailing],[contacts]![meeting date],[contacts]![thank you],[contacts]![follow up call])
I pasted the code into the On Current Events tab of the form the code you gave me:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.LatestDate) Then _
  3.     Me.LatestDate = MaxFromList([first call], _
  4.                                 [second call], _
  5.                                 [mailing], _
  6.                                 [meeting date], _
  7.                                 [thank you], _
  8.                                 [follow up call])
  9. End Sub
This did not work. What am I doing wrong? Thanks.

Becca
Jun 12 '07 #11
NeoPa
Expert Mod 16PB
Me.LatestDate is what I used as the name of the control on your form which shows the latest date. If this is incorrect then you need replace the name with the correct version that you've used. If your name includes spaces then [] will be needed to enclose the name (EG. Me.[Field Name]).

For the procedure (MaxFromList()), you need to Copy and Paste the code into your database. If it is only to be used in this one form then you can paste it into the module for the form, otherwise it needs to be in a normal module (not associated with any object like form, report or class).
To get to the right place open the form in design view, then switch to the VBA window using Alt-F11. In the Project Explorer pane (Ctrl-R) double-click on the form and the module for that form will open in the Code Pane.
If you need a general module select Insert / Module instead.
I would change the first line though, depending on whether it is in the form's module (A) or the general one (B).
  1. Expand|Select|Wrap|Line Numbers
    1. Private Function MaxFromList(ParamArray Values())
  2. Expand|Select|Wrap|Line Numbers
    1. Public Function MaxFromList(ParamArray Values())
Jun 12 '07 #12
NeoPa
Expert Mod 16PB
Expand|Select|Wrap|Line Numbers
  1. =MaxFromList([contacts]![first call],[contacts]![second call],[contacts]![mailing],[contacts]![meeting date],[contacts]![thank you],[contacts]![follow up call])
This is not any use I'm afraid.
I pasted the code into the On Current Events tab of the form the code you gave me:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.LatestDate) Then _
  3.     Me.LatestDate = MaxFromList([first call], _
  4.                                 [second call], _
  5.                                 [mailing], _
  6.                                 [meeting date], _
  7.                                 [thank you], _
  8.                                 [follow up call])
  9. End Sub
This is exactlly right - even your adding of the Me. for consistency. Both would have worked (assuming the fieldname was right).
Jun 12 '07 #13
This is not any use I'm afraid.
This is exactlly right - even your adding of the Me. for consistency. Both would have worked (assuming the fieldname was right).
I appreciate your help, but I'm still trying to interpret your instructions. I have a general Module now, where I pasted this code:

Expand|Select|Wrap|Line Numbers
  1. Public Function MaxFromList(ParamArray Values())
  2.   If IsNull(Me.[latest contact date) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                              [second call], _
  5.                              [mailing], _
  6.                              [meeting date], _
  7.                              [thank you], _
  8.                              [follow up call], _
  9.                              [postcard])
  10. End Function
I have read and reread the posts, and have tried applying my interpretations of them, but I am not understanding, obviously. Imagine you've been asked to perform emergency surgery to remove the duodenum and suture the ileum! I'm working in the dark, here, and appreciate your patience.

Where do I go from having created this general module to getting the "latest contact date" to appear on my form? Thanks.
Jun 12 '07 #14
NeoPa
Expert Mod 16PB
Becca (or should that be Dr Becca :D),
I know where the ilium and duodenum are (generally - probably get completely lost inside a living breathing human body) but my stitching rather depends on the materials being dry ;) Seriously though, I'm sorry if I've seemed impatient. I do get that way sometimes.
Anyway, on to the problem.
I assume you already have a form created with a TextBox control called [latest contact date]. I assume also that the controls [first call], [second call], [mailing], [meeting date], [thank you] & [follow up call] also exist as either TextBoxes or ComboBoxes or a mixture on this same form.
If all this is true then the Form_Current() event procedure should invoke the MaxFromList() function perfectly.
Jun 12 '07 #15
FishVal
Expert 2GB
I appreciate your help, but I'm still trying to interpret your instructions. I have a general Module now, where I pasted this code:

Expand|Select|Wrap|Line Numbers
  1. Public Function MaxFromList(ParamArray Values())
  2.   If IsNull(Me.[latest contact date) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                              [second call], _
  5.                              [mailing], _
  6.                              [meeting date], _
  7.                              [thank you], _
  8.                              [follow up call], _
  9.                              [postcard])
  10. End Function
I have read and reread the posts, and have tried applying my interpretations of them, but I am not understanding, obviously. Imagine you've been asked to perform emergency surgery to remove the duodenum and suture the ileum! I'm working in the dark, here, and appreciate your patience.

Where do I go from having created this general module to getting the "latest contact date" to appear on my form? Thanks.
Hi!

It seems to me I've got a simple solution. I've tested it succesfully. See whether it is what you are expecting for.
  • Create a form from you table
  • In design view set the DefaultValue property of the text box (in the example named dteDate) to
    =DMax([dteDate].[ControlSource],[RecordSource])
  • Be aware that default value appears only when you insert new record, not on current when the control is empty.

Good luck.
Jun 12 '07 #16
NeoPa
Expert Mod 16PB
Expand|Select|Wrap|Line Numbers
  1. Public Function MaxFromList(ParamArray Values())
  2.   If IsNull(Me.[latest contact date]) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                              [second call], _
  5.                              [mailing], _
  6.                              [meeting date], _
  7.                              [thank you], _
  8.                              [follow up call], _
  9.                              [postcard])
  10. End Function
I just looked at this again and noticed that you've done a bit of mix-n-matching with the code (You doctors eh?).
This bit of code should be in the form's module and should be :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.[latest contact date) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                                            [second call], _
  5.                                            [mailing], _
  6.                                            [meeting date], _
  7.                                            [thank you], _
  8.                                            [follow up call], _
  9.                                            [postcard])
  10. End Sub
Jun 12 '07 #17
NeoPa
Expert Mod 16PB
Drop this into a code module. Notice the way you use it is in comment # 1

Expand|Select|Wrap|Line Numbers
  1. Function MaxFromList(ParamArray Values())
  2. ' Used in a query   MyHighDate:MaxFromList([date1],[date2],[date3])
  3. ' Selects highest value from the arguments.
  4. ' Use arguments of same type for good results.
  5. ' This uses the date serial by turning the date into Long
  6. Dim intN As Long
  7. 'On Error GoTo Failure
  8.     MaxFromList = Null
  9.     For intN = 0 To UBound(Values)
  10.         If IsNull(MaxFromList) Then
  11.             MaxFromList = Values(intN)
  12.         ElseIf Values(intN) > MaxFromList Then
  13.             MaxFromList = Values(intN)
  14.         End If
  15.     Next intN
  16.     Exit Function
  17. Failure:
  18.     MaxFromList = Null
  19. End Function
This is post #2 quoted again for you.
This is the code which is copied and pasted into the general module (or can be the form module if easier and you feel you won't need it elsewhere in your project).
Let me know if there are any difficulties with any of this. I will try to help (patiently) where I can.
Good luck and let us know how you get on or if you need any help :)
Jun 12 '07 #18
FishVal
Expert 2GB
Hi!

It seems to me I've got a simple solution. I've tested it succesfully. See whether it is what you are expecting for.
  • Create a form from you table
  • In design view set the DefaultValue property of the text box (in the example named dteDate) to
    =DMax([dteDate].[ControlSource],[RecordSource])
  • Be aware that default value appears only when you insert new record, not on current when the control is empty.

Good luck.

Sorry.

This seems to work from time to time. I think the problem is with underlayer table updating. Another solution works good so far.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub dteDate_AfterUpdate()
  3.     Me.Refresh
  4.     Me.dteDate.DefaultValue = _
  5.         Format(DMax("dteDate", Me.RecordSource), "\#mm/dd/yyyy\#")
  6. End Sub
  7.  
  8. Private Sub Form_Open(Cancel As Integer)
  9.     Me.dteDate.DefaultValue = _
  10.         Format(DMax("dteDate", Me.RecordSource), "\#mm/dd/yyyy\#")
  11. End Sub
  12.  
  13.  
However this may be dependant on you system date format ... or may be not :)
Jun 12 '07 #19
FishVal
Expert 2GB
Sorry.

This seems to work from time to time. I think the problem is with underlayer table updating. Another solution works good so far.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub dteDate_AfterUpdate()
  3.     Me.Refresh
  4.     Me.dteDate.DefaultValue = _
  5.         Format(DMax("dteDate", Me.RecordSource), "\#mm/dd/yyyy\#")
  6. End Sub
  7.  
  8. Private Sub Form_Open(Cancel As Integer)
  9.     Me.dteDate.DefaultValue = _
  10.         Format(DMax("dteDate", Me.RecordSource), "\#mm/dd/yyyy\#")
  11. End Sub
  12.  
  13.  
However this may be dependant on you system date format ... or may be not :)

Pardon.

I've not read you first message carefully. The solution above is not relevant for you.

Good luck
Jun 12 '07 #20
NeoPa
Expert Mod 16PB
Pardon.

I've not read you first message carefully. The solution above is not relevant for you.

Good luck
Never mind Fish, that happens to all of us sometimes.

Becca,
You will need to go all the way back to the post following your last (#15) to catch up with all that's happened since.
Jun 12 '07 #21
FishVal
Expert 2GB
I have fields in a table that include dates of meetings, phone calls, mailings, and other contact with clients. I have one field that is "most recent contact date". I want that default value to be the most recent date among all the types of contacts. How would I build this expression in the default value area of the table? I basically need something that will return the most recent date among 5 fields. Thanks!
Hi!

Definitely, your db is not normalized. Have you thought about it?
Currently you have spreadsheetlike table (like that one in Excel) and want spreadsheetlike solution.

I suggest you the following db structure:

tblClients
keyClientID (PK, Autonumber, Long)
txtClientName (Text)

tblActivityNames
keyActivityNameID (PK, Autonumber, Long)
txtActivityName (Text)

tblActivityDates
keyActivityDateID (PK, Autonumber, Long)
keyClientID (FK, Autonumber, Long)
keyActivityNameID (FK, Autonumber, Long)
dteActivityDate (Date)

The advantages are the following:
  • tblClients is in one-to-many relationship with tblActivityDates:
    you can store the quantity of activity dates associated with a particular client limited only by db size (e.g. multiple meetings, callings etc) and retrieve the latest date easily
    DMax("dteActivityDate", "tblActivityDates", "keyClientID=" & ClientID)
    or
    SELECT MAX(dteActivityDate) FROM tblActivityDates WHERE keyClientID=ClientID;
    or design a CrosstabQuery
  • tblActivityNames is in one-to-many relationship with tblActivityDates:
    whether you want to add new ActivityName (e.g. "Fax sent") you don't have to add new field to the table, simply add a new record to tblActivityNames; this will not affect latest date selection mechanism while adding new field will force you to modify it
  • you can easily design flexible interface on normalized tables

If you provide me with your table metadata I will be able to explain you the way you can split you table (w/o data loss of course).

P.S. If you realize the situation but nevertheless want to proceed with unnormalized db, don't keep this post in mind.
Jun 13 '07 #22
Never mind Fish, that happens to all of us sometimes.

Becca,
You will need to go all the way back to the post following your last (#15) to catch up with all that's happened since.
Wow, did I ever have some catching up to do! O.K. So I've pasted the code from post #2 into a general module and I pasted code from post #17 into the onCurrent event of the form. Now I get an error message when I try to view the form design that reads: "The expression On Current you entered as the event property setting produced the following error: Expected: line number or label or statement or end of statement."

Also, was I supposed to copy and paste the code as is, with the line item numbers? Thanks.

I'm ready to give up here...I do appreciate all your help, but my brain obviously can't grasp this. So much easier with web design questions--the helpers can just go to my site and see the html!
Jun 15 '07 #23
NeoPa
Expert Mod 16PB
Some browsers have difficulty copying and pasting code from our code panels. The line numbers are certainly not required.
To get a good copy, simply click on the Reply to Post and, without submitting the reply at all, copy the text between the [code] tags.
If you post all the code that you get as a result, I'll check it over for you to see if it 'looks' ok.
Jun 15 '07 #24
Some browsers have difficulty copying and pasting code from our code panels. The line numbers are certainly not required.
To get a good copy, simply click on the Reply to Post and, without submitting the reply at all, copy the text between the [ CODE ] tags.
If you post all the code that you get as a result, I'll check it over for you to see if it 'looks' ok.
Here's the code I pasted into the general module:

Expand|Select|Wrap|Line Numbers
  1. Function MaxFromList(ParamArray Values())
  2. Dim intN As Long
  3.     MaxFromList = Null
  4.     For intN = 0 To UBound(Values)
  5.         If IsNull(MaxFromList) Then
  6.             MaxFromList = Values(intN)
  7.         ElseIf Values(intN) > MaxFromList Then
  8.             MaxFromList = Values(intN)
  9.         End If
  10.     Next intN
  11.     Exit Function
  12. Failure:
  13.     MaxFromList = Null
  14. End Function
and here's the code from the OnCurrent event of the form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.[latest contact date) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                                            [second call], _
  5.                                            [mailing], _
  6.                                            [meeting date], _
  7.                                            [thank you], _
  8.                                            [follow up call], _
  9.                                            [postcard])
  10. End Sub
I guess I'm expecting that after I do that, I can go to my form and see that the "latest contact date" field has been filled in, but it's not. I also tried adding a new record and filling in some of the dates, also expecting that field to fill in automatically, but it does not. At least the error message went away, though!

Becca
Jun 15 '07 #25
JConsulting
Expert 512MB
Here's the code I pasted into the general module:

Expand|Select|Wrap|Line Numbers
  1. Function MaxFromList(ParamArray Values())
  2. Dim intN As Long
  3.     MaxFromList = Null
  4.     For intN = 0 To UBound(Values)
  5.         If IsNull(MaxFromList) Then
  6.             MaxFromList = Values(intN)
  7.         ElseIf Values(intN) > MaxFromList Then
  8.             MaxFromList = Values(intN)
  9.         End If
  10.     Next intN
  11.     Exit Function
  12. Failure:
  13.     MaxFromList = Null
  14. End Function
and here's the code from the OnCurrent event of the form:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.[latest contact date) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                                            [second call], _
  5.                                            [mailing], _
  6.                                            [meeting date], _
  7.                                            [thank you], _
  8.                                            [follow up call], _
  9.                                            [postcard])
  10. End Sub
I guess I'm expecting that after I do that, I can go to my form and see that the "latest contact date" field has been filled in, but it's not. I also tried adding a new record and filling in some of the dates, also expecting that field to fill in automatically, but it does not. At least the error message went away, though!

Becca
Hi Becca,
Are all of those fields the same type? (all dates)?

Are any of them null?
Jun 15 '07 #26
Hi Becca,
Are all of those fields the same type? (all dates)?

Are any of them null?

They are all dates, and yes, some of them are null. For example, if I've only sent a mailing and made one phone call, the other date fields would be blank. Although, even with records where all the dates are filled in, the "latest contact date" does not appear.
Jun 15 '07 #27
NeoPa
Expert Mod 16PB
Please check something out for me.
This (Debugging in VBA) article shows how to trace through the code. I would like you to put a breakpoint (See post #3 point d) on line #2 (Lines up to 9 will be included and go maroon) of the Form_Current procedure, and tell me when this code is triggered. When you've noted when it happens, simply hit F5 (See post #3 point e) to resume.

PS. Is your [latest contact date] control unbound?
Jun 15 '07 #28
JConsulting
Expert 512MB
They are all dates, and yes, some of them are null. For example, if I've only sent a mailing and made one phone call, the other date fields would be blank. Although, even with records where all the dates are filled in, the "latest contact date" does not appear.
also, in the following code, a bracket is missing after the word date in your first if statement. It's causing the if to endwithout anything happening.
J

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2.   If IsNull(Me.[latest contact date) Then _
  3.     Me.[latest contact date] = MaxFromList([first call], _
  4.                                            [second call], _
  5.                                            [mailing], _
  6.                                            [meeting date], _
  7.                                            [thank you], _
  8.                                            [follow up call], _
  9.                                            [postcard])
  10. End Sub
  11.  
Jun 15 '07 #29
JConsulting
Expert 512MB
They are all dates, and yes, some of them are null. For example, if I've only sent a mailing and made one phone call, the other date fields would be blank. Although, even with records where all the dates are filled in, the "latest contact date" does not appear.
change this line out

Expand|Select|Wrap|Line Numbers
  1. If nz(Me.[latest contact date],0)=0 Then _
  2.  
Jun 15 '07 #30
change this line out

Expand|Select|Wrap|Line Numbers
  1. If nz(Me.[latest contact date],0)=0 Then _
  2.  
So, I replace

If IsNull(Me.[latest contact date) Then _

with

If nz(Me.[latest contact date],0)=0 Then _

Is that correct?

Nothing changes after I do that. Will try to run that VBA analysis you mentioned.
Jun 15 '07 #31
JConsulting
Expert 512MB
So, I replace

If IsNull(Me.[latest contact date) Then _

with

If nz(Me.[latest contact date],0)=0 Then _

Is that correct?

Nothing changes after I do that. Will try to run that VBA analysis you mentioned.
I'm thinking we probably need to wrap each one of the date fields that are getting passed in NZ as well. If a null IS passed to the function, it cannot be > or < anything, as you can't evaluate nothing. It should correct the problem.
J
Jun 15 '07 #32
I'm thinking we probably need to wrap each one of the date fields that are getting passed in NZ as well. If a null IS passed to the function, it cannot be > or < anything, as you can't evaluate nothing. It should correct the problem.
J
Sorry, please explain "wrap each one of the date fields that are getting passed in NZ". Thank you.

Becca
Jun 15 '07 #33
JConsulting
Expert 512MB
Sorry, please explain "wrap each one of the date fields that are getting passed in NZ". Thank you.

Becca
Wrapping...sorry, a rather broad term. Putting the value inside the NZ function would be a better way of saying it.

What the NZ function does is sample the field or variable that's put inside it. And if it is Null, it assigns it the value that you tell it to. the default value for NZ is 0

so if I pass the field [xyz] to the nz function nz([xyz]) if it is null, then it returns 0
nz([xyz],"N/A") <---for a text field
nz([xyz],"NoValue") <---for a text field
nz([xyz],0) <--- for a numeric or date field

so wrapping the fields that are being passed to the function in NZ means you want to pass a 0 if it's null...because it's a date field and you can't pass the function a Null.
Hope this helps!
J
Jun 15 '07 #34
Wrapping...sorry, a rather broad term. Putting the value inside the NZ function would be a better way of saying it.

What the NZ function does is sample the field or variable that's put inside it. And if it is Null, it assigns it the value that you tell it to. the default value for NZ is 0

so if I pass the field [xyz] to the nz function nz([xyz]) if it is null, then it returns 0
nz([xyz],"N/A") <---for a text field
nz([xyz],"NoValue") <---for a text field
nz([xyz],0) <--- for a numeric or date field

so wrapping the fields that are being passed to the function in NZ means you want to pass a 0 if it's null...because it's a date field and you can't pass the function a Null.
Hope this helps!
J
Another thing I realized--will the "latest contact date" refresh when dates are changed in the other fields, or will it only fill in if the field is currently null? I need it to update as dates are added/changed in the other date fields.

So, as far as this "nz" thing, do I "wrap" my other date fields in my current code like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Current()
  2. If Nz(Me.[first call], 0) = 0, _
  3. If Nz(Me.[second call], 0) = 0, _
  4. If Nz(Me.[mailing], 0) = 0, _
  5. If Nz(Me.[meeting date], 0) = 0, _
  6. If Nz(Me.[thank you], 0) = 0, _
  7. If Nz(Me.[follow up call], 0) = 0, _
  8. If Nz(Me.[postcard], 0) = 0, _
  9. If Nz(Me.[latest contact date], 0) = 0 Then _
  10.     Me.[latest contact date] = MaxFromList([first call], _
  11.                                            [second call], _
  12.                                            [mailing], _
  13.                                            [meeting date], _
  14.                                            [thank you], _
  15.                                            [follow up call], _
  16.                                            [postcard])
  17. End Sub
This does not work because a popup appears asking for a "then" or "go to" function.
Jun 15 '07 #35
NeoPa
Expert Mod 16PB
Becca,

It appears that JC has taken you off in a different direction from where I was headed. I don't believe that my original idea of simply coding the extra stuff in the Form_Current event procedure is enough to handle the whole issue, but it should have produced different results from what you reported. Hence the questions in my post #28.
Please feel free to continue working with JC (who I'm sure will get to a solution for you. He is really very good), but if you would like further advice from me, then I will need a response to that post. I suspect some AfterUpdate event procedures will also be required here but to know exactly where the best next step should be I need the information. Obviously, you're free to choose not to continue on this track, but please let me know if you do, then I can know to leave you entirely in JC's hands.
Jun 17 '07 #36
Becca,

It appears that JC has taken you off in a different direction from where I was headed. I don't believe that my original idea of simply coding the extra stuff in the Form_Current event procedure is enough to handle the whole issue, but it should have produced different results from what you reported. Hence the questions in my post #28.
Please feel free to continue working with JC (who I'm sure will get to a solution for you. He is really very good), but if you would like further advice from me, then I will need a response to that post. I suspect some AfterUpdate event procedures will also be required here but to know exactly where the best next step should be I need the information. Obviously, you're free to choose not to continue on this track, but please let me know if you do, then I can know to leave you entirely in JC's hands.
Hi NeoPa

I admit I was a little daunted by undertaking the understanding and practice of the debugging...so I was hoping maybe there was a shorter answer. I will need to set aside some time to read that article thoroughly and try it out. Thanks.

Becca
Jun 18 '07 #37
NeoPa
Expert Mod 16PB
Becca, that's perfectly fine and understandable.
If you find that you need to come back to me on this just let me know (with a post in here). I will probably want to revert to where I left off though as I work very logically and only go on to the next step when I'm confident of the current one.

I think the likelihood of your needing my help after JC has finished with you is fairly slight though.

Have a play in the debug window sometime anyway though. You'll be surprised how much fun it can be (or is that just me :/). I'll be here if you need me anyway.
Jun 19 '07 #38

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

23 posts views Thread by Paul Rubin | last post: by
25 posts views Thread by Mike | last post: by
reply views Thread by CHU Run-min | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.