Contact date less than Open date: MS Access 2003

[Dököll]

Hey Gang!

I am sure thi is simple and will find answers here. Can't do so as of now, but will when I get home>

It looks like below code is not allowing me to set it so Contact date canot be lesser than open date:

ElseIf Me.TodayDate.Value > Me.ContactDate.Value Then 'Let's do some checking
MsgBox "You cannot enter Contact date prior to Open date, please modify to continue...", vbInformation + vbOKOnly, _
               "Data Central"
Me.cboContactDate.SetFocus
Me.Phoned = False 'report false because user must add contact info...
Me.Phoned.Visible = True 'report true because user must add contact info...
 
 

Even though the date is 01/09/2008 for both field, I still get my pop up 'cannot enter Contact date prior to Open date' what do you see happenning.

See you in a bit, still testing it, will post my findings also:-)

Thanks!

[puppydogbuddy]

Hey Gang!

I am sure thi is simple and will find answers here. Can't do so as of now, but will when I get home>

It looks like below code is not allowing me to set it so Contact date canot be lesser than open date:

ElseIf Me.TodayDate.Value > Me.ContactDate.Value Then 'Let's do some checking
MsgBox "You cannot enter Contact date prior to Open date, please modify to continue...", vbInformation + vbOKOnly, _
               "Data Central"
Me.cboContactDate.SetFocus
Me.Phoned = False 'report false because user must add contact info...
Me.Phoned.Visible = True 'report true because user must add contact info...
 
 

Even though the date is 01/09/2008 for both field, I still get my pop up 'cannot enter Contact date prior to Open date' what do you see happenning.

See you in a bit, still testing it, will post my findings also:-)

Thanks!

Hi Dököll,

Date fields in ms access have a date component and a time component, andd you are feeling the effects of the time component. You need to separate out just the date component and you will be ok. Use the DateValue function to do that, e.g. DateValue([YourDatefield])

[Dököll]

Hi Dököll,

Date fields in ms access have a date component and a time component, andd you are feeling the effects of the time component. You need to separate out just the date component and you will be ok. Use the DateValue function to do that, e.g. DateValue([YourDatefield])

Always a pleasure, puppydogbuddy, how are things?

I was coming over to report I may have found something but it has some weird side affects. The Validation Text, and Rule:

>Date() in V Rule deleted all data in my database copy, what do you make of it?

I missed something no doubt...

Will try your option, thanks much!

Dököll

[puppydogbuddy]

Always a pleasure, puppydogbuddy, how are things?

I was coming over to report I may have found something but it has some weird side affects. The Validation Text, and Rule:

>Date() in V Rule deleted all data in my database copy, what do you make of it?

I missed something no doubt...

Will try your option, thanks much!


Dököll

Hi Dököll,
Keeping busy I see!

Regarding the V Rule...it is saying the date entered must be > today's date.....but I can't see why it would delete all your existing data in your database unless you have a coded delete procedure elsewhere in your code.

Also before I forget to mention it, I believe the function Date() operates only on the date component, so the time component should make no difference where Date() is used.

[Dököll]

Hi Dököll,
Keeping busy I see!

Regarding the V Rule...it is saying the date entered must be > today's date.....but I can't see why it would delete all your existing data in your database unless you have a coded delete procedure elsewhere in your code.

Also before I forget to mention it, I believe the function Date() operates only on the date component, so the time component should make no difference where Date() is used.

Spoken like a true champion...

My date fields are Now(), perhaps that's it. And perhaps even with the original code if I have Date() rather Now() would have worked, would you say?

Anyway, Yeah! It's been rather busy lately, someone is plulling some strings:-)

Thanks for asking...

In a bit!

[Dököll]

Spoken like a true champion...

My date fields are Now(), perhaps that's it. And perhaps even with the original code if I have Date() rather Now() would have worked, would you say?

Anyway, Yeah! It's been rather busy lately, someone is plulling some strings:-)

Thanks for asking...

In a bit!

Please disregard above post, puppydogbuddy!

Like waterfalls, it flows through now:-)

 
ElseIf DateValue([ContactDate]) < DateValue([TodayDate]) Then 'Let's do some checking
MsgBox "You cannot add Contact date prior to Open date, please modify to continue...", vbInformation + vbOKOnly, _
               "Data Central"
Me.cboContactDate.SetFocus
Me.Phoned = False 'report false because user must add contact info...
Me.Phoned.Visible = True 'report true because user must add contact info...
 
 

See you around:-)

[puppydogbuddy]

Spoken like a true champion...

My date fields are Now(), perhaps that's it. And perhaps even with the original code if I have Date() rather Now() would have worked, would you say?

Anyway, Yeah! It's been rather busy lately, someone is plulling some strings:-)

Thanks for asking...

In a bit!

Now() definitely contains the current date AND time. If you replace Now() with Date(), which only operates on the date component, your original code may work, all other things being equal.

[Dököll]

Now() definitely contains the current date AND time. If you replace Now() with Date(), which only operates on the date component, your original code may work, all other things being equal.

Yeah!

Had a hunch that would have also worked...

Have a wonderful week-end!

[Dököll]

Hiya Partners!

I rolled up my sleeves again and modified code once more...

An attempt ensure added info is there in the case users add a dummy phone number or email failed. Well at least it works only when I visit the data again, let me explain:

(1) When data added to Comment box, if data characters are less than 255 and the contact info is a dummy, user must know to add more info

(2) Problem is after hitting my Save button, the data is accepted, but when I return to the data and pressed save again, I get the wonderful pop-up I wanted

What are your thoughts? It's surely simple:

 
If Len([Comment].Value) < 255 And Me.ContactPerson.Value = "Data Central: 000-000-0000" Then ' do not allow save if true
MsgBox "Please go to Comment, provide info specific to dummy Phone number" & _
                    vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
               "Data Central" ' We need to ensure user is adding enough information in comment if a dummy number must be used...
 
 
ElseIf Len([Comment].Value) < 255 And Me.Email.Value = "Data Central: [e-mail]" Then ' do not allow save if true
MsgBox "Please go to Comment, provide info specific to dummy Email address" & _
                    vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
               "Data Central" ' We need to ensure user is adding enough information in comment if a dummy email must be used...
 
 

Your input is appreciated;-)

[puppydogbuddy]

Hiya Partners!

I rolled up my sleeves again and modified code once more...

An attempt ensure added info is there in the case users add a dummy phone number or email failed. Well at least it works only when I visit the data again, let me explain:

(1) When data added to Comment box, if data characters are less than 255 and the contact info is a dummy, user must know to add more info

(2) Problem is after hitting my Save button, the data is accepted, but when I return to the data and pressed save again, I get the wonderful pop-up I wanted

What are your thoughts? It's surely simple:

 
If Len([Comment].Value) < 255 And Me.ContactPerson.Value = "Data Central: 000-000-0000" Then ' do not allow save if true
MsgBox "Please go to Comment, provide info specific to dummy Phone number" & _
                    vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
               "Data Central" ' We need to ensure user is adding enough information in comment if a dummy number must be used...
 
 
ElseIf Len([Comment].Value) < 255 And Me.Email.Value = "Data Central: [e-mail]" Then ' do not allow save if true
MsgBox "Please go to Comment, provide info specific to dummy Email address" & _
                    vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
               "Data Central" ' We need to ensure user is adding enough information in comment if a dummy email must be used...
 
 

Your input is appreciated;-)

I don't know if this will help, but when you use the length function to constrain the number of characters in a memo field or unbound textbox, the trim function is needed to trim leading or trailing spaces. Also, in comparing text strings, it is preferable to use Like instead of equal.

Try changing this:
If Len([Comment].Value) < 255 And Me.ContactPerson.Value = "Data Central: 000-000-0000" Then do not allow save if true.

To this:
If Len(Trim([Comment])) < 255 And Me!ContactPerson.Value Like "Data Central: 000-000-0000" Then ' do not allow save if true

If the above helps, then you need to make these changes to other similar statements where you use the length funciton in your code,

[Dököll]

I don't know if this will help, but when you use the length function to constrain the number of characters in a memo field or unbound textbox, the trim function is needed to trim leading or trailing spaces. Also, in comparing text strings, it is preferable to use Like instead of equal.

Try changing this:
If Len([Comment].Value) < 255 And Me.ContactPerson.Value = "Data Central: 000-000-0000" Then do not allow save if true.

To this:
If Len(Trim([Comment])) < 255 And Me!ContactPerson.Value Like "Data Central: 000-000-0000" Then ' do not allow save if true

If the above helps, then you need to make these changes to other similar statements where you use the length funciton in your code,

You're a Gent and Scholar my friend...

Yes I did the LIKE Operator, I probably had something else going on and it did not like it. Thanks for posting the trim idea, I did not know that. Will give it a whirl...

Later puppydogbuddy!

Dököll

[Dököll]

You're a Gent and Scholar my friend...

Yes I did the LIKE Operator, I probably had something else going on and it did not like it. Thanks for posting the trim idea, I did not know that. Will give it a whirl...

Later puppydogbuddy!

Dököll

Hey puppydogbuddy!

It looks like the same treatement. Though I believe you sort hinted it may not be it;-)

How's this?

I previously added a different textbox that read the character length of Comment, but had an On Change event embedded in the Comment box,

Example:

 
txtZReadMyChars= Len([Comment].Value) 
 
 

Then say look at this textbox of mine and tell me what you see if < than 255 and blah blah in other textbox, show pop up.

I can add to an event procedure I know will give me realtime reading of txtZReadMyChars.

Will try this and see. Throw your hat in if you see anything else.

Thanks much Good Buddy!

In a bit...

[puppydogbuddy]

Hey puppydogbuddy!

It looks like the same treatement. Though I believe you sort hinted it may not be it;-)

How's this?

I previously added a different textbox that read the character length of Comment, but had an On Change event embedded in the Comment box,

Example:

 
txtZReadMyChars= Len([Comment].Value) 
 
 

Then say look at this textbox of mine and tell me what you see if < than 255 and blah blah in other textbox, show pop up.

I can add to an event procedure I know will give me realtime reading of txtZReadMyChars.

Will try this and see. Throw your hat in if you see anything else.

Thanks much Good Buddy!

In a bit...

Hi Dököll,
try the syntax belw.
txtZReadMyChars.Value = Len(Trim([Comment]))

also note that on change event only fires when something entered in textbox. It does not fire when textbox changed by code. So, on-change will fire for Comment textbox, but not for txtZReadMyChars textbox. Therefore, your code statement: txtZReadMyChars.Value = Len(Trim([Comment])) shoud be part of the code for the on-change event of the Comment box.

[Dököll]

Hi Dököll,
try the syntax belw.
txtZReadMyChars.Value = Len(Trim([Comment]))

also note that on change event only fires when something entered in textbox. It does not fire when textbox changed by code. So, on-change will fire for Comment textbox, but not for txtZReadMyChars textbox. Therefore, your code statement: txtZReadMyChars.Value = Len(Trim([Comment])) shoud be part of the code for the on-change event of the Comment box.

Thank you for that puppydogbuddy, I would have forgotten to trim there also...

I was coming over to report a new development. Looks like if more than enough info is added, I get the pop up. It's as if it disregards the < completely, could that be true? Perhaps the way the code is written!

I have yet to try the other option so I am also glad I stopped by first hand to see your post, will trim it as suggested.

I'll have to look into that, seems like it lets me add more data then says no way.

Keep you posted puppydogbuddy...

Dököll

[Dököll]

Thank you for that puppydogbuddy, I would have forgotten to trim there also...

I was coming over to report a new development. Looks like if more than enough info is added, I get the pop up. It's as if it disregards the < completely, could that be true? Perhaps the way the code is written!

I have yet to try the other option so I am also glad I stopped by first hand to see your post, will trim it as suggested.

I'll have to look into that, seems like it lets me add more data then says no way.

Keep you posted puppydogbuddy...

Dököll

Hey Gang!

I am able to search using the LIKE operator but I am unable to submit and to pull up an error if I am not happy with what's added in:

 
ElseIf Me.IssuesItems Like Me.TodayDate Then
MsgBox "Open date does not match date in Items text box please modify to continue...", vbInformation + vbOKOnly, _
               "Data Central"
 
 

++++++++++++++++

The idea is there is a bit of text that contains a date at the beginning:

2/7/2008 3:58:29 PM Error: - Not enough storage is available

++++++++++++++++

I want to look at the Items field that'll have the info with the date and throw a POP up if date does not match the Open date. Above code lets me submit and I do not get a pop up.

What do you see happening?

Thanks!

Dököll

[puppydogbuddy]

Hey Gang!

I am able to search using the LIKE operator but I am unable to submit and to pull up an error if I am not happy with what's added in:

 
ElseIf Me.IssuesItems Like Me.TodayDate Then
MsgBox "Open date does not match date in Items text box please modify to continue...", vbInformation + vbOKOnly, _
               "Data Central"
 
 

++++++++++++++++

The idea is there is a bit of text that contains a date at the beginning:

2/7/2008 3:58:29 PM Error: - Not enough storage is available

++++++++++++++++

I want to look at the Items field that'll have the info with the date and throw a POP up if date does not match the Open date. Above code lets me submit and I do not get a pop up.

What do you see happening?

Thanks!

Dököll

Hi Dököll,
It sounds to me like your date strings are formatted differently and will never match on a text based comparision using the like operator. I would convert the date strings to numeric values using CDate function, and use numeric operators such as =, <> instead of like for a numeric comparision between dates.

[Dököll]

Hi Dököll,
It sounds to me like your date strings are formatted differently and will never match on a text based comparision using the like operator. I would convert the date strings to numeric values using CDate function, and use numeric operators such as =, <> instead of like for a numeric comparision between dates.

Thank you for that, I started thinking of that also. I may have to have it read the characters and compare that way. Will go with your idea first then mine... thanks much!

[Dököll]

Thank you for that, I started thinking of that also. I may have to have it read the characters and compare that way. Will go with your idea first then mine... thanks much!

Heiya puppydogbuddy!

What do you see in this code that isn't working:

Dim strFindUnmatchedDates As Long
strFindUnmatchedDates = InStr(1, Me.Items, " " & Me.TodayDate & " ", vbTextCompare)
If strFindUnmatchedDates = 0 Then
MsgBox ("Darn!")
Else
 

A different version of it works in VB and would do the trick for me.

I get a message box even when the date is correct in Me.Items and compares correctly with Me.TodayDate

This one hit me like a ton of brinck and I thought to relay it to you.

Thanks!

[Dököll]

Heiya puppydogbuddy!

What do you see in this code that isn't working:

Dim strFindUnmatchedDates As Long
strFindUnmatchedDates = InStr(1, Me.Items, " " & Me.TodayDate & " ", vbTextCompare)
If strFindUnmatchedDates = 0 Then
MsgBox ("Darn!")
Else
 

A different version of it works in VB and would do the trick for me.

I get a message box even when the date is correct in Me.Items and compares correctly with Me.TodayDate

This one hit me like a ton of brinck and I thought to relay it to you.

Thanks!

Nevermind!!!

I did not do it right:

 
Dim strFindUnmatchedDates As Long
strFindUnmatchedDates = InStr(1, " " & Me.StatusDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
If strFindUnmatchedDates = 0 Then
MsgBox ("Darn!")
ElseIf strFindUnmatchedDates <> 0 Then
MsgBox ("Found!")
 
 

See you soon;-)

[Dököll]

Nevermind!!!

I did not do it right:

 
Dim strFindUnmatchedDates As Long
strFindUnmatchedDates = InStr(1, " " & Me.StatusDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
If strFindUnmatchedDates = 0 Then
MsgBox ("Darn!")
ElseIf strFindUnmatchedDates <> 0 Then
MsgBox ("Found!")
 
See you soon;-)

Funny thing:

Dim strFindUnmatchedDates As Long
strFindUnmatchedDates = InStrB(1, " " & Me.cboContactDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
If strFindUnmatchedDates = 0 Then
MsgBox ("Oh oh!")
Else
MsgBox ("YesSiree!")
Me.Items.SetFocus
 

I am not sure why this not reading the full body of the textbox Items, say for instance:

"Jack phoned and was beside himself, 01/31/2007, I contated him and all is well..."

say that is added to Items the code should be able to look pair cboContactDate again a date whereever found in the text. It does seem to be doing that, any ideas!

Thanks much!

[Dököll]

[/code]Funny thing:

Dim strFindUnmatchedDates As Long
strFindUnmatchedDates = InStrB(1, " " & Me.cboContactDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
If strFindUnmatchedDates = 0 Then
MsgBox ("Oh oh!")
Else
MsgBox ("YesSiree!")
Me.Items.SetFocus
 

I am not sure why this not reading the full body of the textbox Items, say for instance:

"Jack phoned and was beside himself, 01/31/2007, I contated him and all is well..."

say that is added to Items the code should be able to look pair cboContactDate again a date whereever found in the text. It does seem to be doing that, any ideas!

Thanks much!

please disregard, Again!

dates will always be at the beginning, so we're good to go:-)

have a great week!

Dököll

[Dököll]

Hi Dököll,
It sounds to me like your date strings are formatted differently and will never match on a text based comparision using the like operator. I would convert the date strings to numeric values using CDate function, and use numeric operators such as =, <> instead of like for a numeric comparision between dates.

Greetings, puppydogbuddy!

Thank you, and everyone else of course, who have helped solve some issues here...

The database is complete, commented for all to see, credits given, code library rendered. I am happy to announce my former colleagues are pleased and thus far have reported no problems; can't tell you how happy I am having had yours and everyone's help to complete the task, it was necessary because it has reduced a lot of manual work for our group.

Thanks again... Super!

Dököll

[puppydogbuddy]

Hi Dököll,
Thanks for the kind words! I am glad your users are happy with the finished product, and that I was able to help you in some small way. Best wishes for your continued success. See you around.

pDog

[Dököll]

Hi Dököll,
Thanks for the kind words! I am glad your users are happy with the finished product, and that I was able to help you in some small way. Best wishes for your continued success. See you around.

pDog

Indeed, pDog...

Have a wonderful week-end:-)

Dököll