By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,460 Members | 2,215 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,460 IT Pros & Developers. It's quick & easy.

Contact date less than Open date: MS Access 2003

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1. ElseIf Me.TodayDate.Value > Me.ContactDate.Value Then 'Let's do some checking
  2. MsgBox "You cannot enter Contact date prior to Open date, please modify to continue...", vbInformation + vbOKOnly, _
  3.                "Data Central"
  4. Me.cboContactDate.SetFocus
  5. Me.Phoned = False 'report false because user must add contact info...
  6. Me.Phoned.Visible = True 'report true because user must add contact info...
  7.  
  8.  
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!
Jan 10 '08 #1
Share this Question
Share on Google+
23 Replies


puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1. ElseIf Me.TodayDate.Value > Me.ContactDate.Value Then 'Let's do some checking
  2. MsgBox "You cannot enter Contact date prior to Open date, please modify to continue...", vbInformation + vbOKOnly, _
  3.                "Data Central"
  4. Me.cboContactDate.SetFocus
  5. Me.Phoned = False 'report false because user must add contact info...
  6. Me.Phoned.Visible = True 'report true because user must add contact info...
  7.  
  8.  
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])
Jan 10 '08 #2

Dököll
Expert 100+
P: 2,364
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
Jan 10 '08 #3

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 10 '08 #4

Dököll
Expert 100+
P: 2,364
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!
Jan 10 '08 #5

Dököll
Expert 100+
P: 2,364
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:-)

Expand|Select|Wrap|Line Numbers
  1.  
  2. ElseIf DateValue([ContactDate]) < DateValue([TodayDate]) Then 'Let's do some checking
  3. MsgBox "You cannot add Contact date prior to Open date, please modify to continue...", vbInformation + vbOKOnly, _
  4.                "Data Central"
  5. Me.cboContactDate.SetFocus
  6. Me.Phoned = False 'report false because user must add contact info...
  7. Me.Phoned.Visible = True 'report true because user must add contact info...
  8.  
  9.  
See you around:-)
Jan 10 '08 #6

puppydogbuddy
Expert 100+
P: 1,923
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.
Jan 10 '08 #7

Dököll
Expert 100+
P: 2,364
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!
Jan 18 '08 #8

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Len([Comment].Value) < 255 And Me.ContactPerson.Value = "Data Central: 000-000-0000" Then ' do not allow save if true
  3. MsgBox "Please go to Comment, provide info specific to dummy Phone number" & _
  4.                     vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
  5.                "Data Central" ' We need to ensure user is adding enough information in comment if a dummy number must be used...
  6.  
  7.  
  8. ElseIf Len([Comment].Value) < 255 And Me.Email.Value = "Data Central: me@me.com" Then ' do not allow save if true
  9. MsgBox "Please go to Comment, provide info specific to dummy Email address" & _
  10.                     vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
  11.                "Data Central" ' We need to ensure user is adding enough information in comment if a dummy email must be used...
  12.  
  13.  
Your input is appreciated;-)
Jan 29 '08 #9

puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. If Len([Comment].Value) < 255 And Me.ContactPerson.Value = "Data Central: 000-000-0000" Then ' do not allow save if true
  3. MsgBox "Please go to Comment, provide info specific to dummy Phone number" & _
  4.                     vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
  5.                "Data Central" ' We need to ensure user is adding enough information in comment if a dummy number must be used...
  6.  
  7.  
  8. ElseIf Len([Comment].Value) < 255 And Me.Email.Value = "Data Central: me@me.com" Then ' do not allow save if true
  9. MsgBox "Please go to Comment, provide info specific to dummy Email address" & _
  10.                     vbCrLf & vbCrLf & "added as contact information for future reference...", vbInformation + vbOKOnly, _
  11.                "Data Central" ' We need to ensure user is adding enough information in comment if a dummy email must be used...
  12.  
  13.  
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,
Jan 29 '08 #10

Dököll
Expert 100+
P: 2,364
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
Jan 30 '08 #11

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. txtZReadMyChars= Len([Comment].Value) 
  3.  
  4.  
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...
Jan 30 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. txtZReadMyChars= Len([Comment].Value) 
  3.  
  4.  
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.
Jan 30 '08 #13

Dököll
Expert 100+
P: 2,364
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
Jan 31 '08 #14

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ElseIf Me.IssuesItems Like Me.TodayDate Then
  3. MsgBox "Open date does not match date in Items text box please modify to continue...", vbInformation + vbOKOnly, _
  4.                "Data Central"
  5.  
  6.  
++++++++++++++++

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
Feb 14 '08 #15

puppydogbuddy
Expert 100+
P: 1,923
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. ElseIf Me.IssuesItems Like Me.TodayDate Then
  3. MsgBox "Open date does not match date in Items text box please modify to continue...", vbInformation + vbOKOnly, _
  4.                "Data Central"
  5.  
  6.  
++++++++++++++++

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.
Feb 15 '08 #16

Dököll
Expert 100+
P: 2,364
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!
Feb 15 '08 #17

Dököll
Expert 100+
P: 2,364
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:

Expand|Select|Wrap|Line Numbers
  1. Dim strFindUnmatchedDates As Long
  2. strFindUnmatchedDates = InStr(1, Me.Items, " " & Me.TodayDate & " ", vbTextCompare)
  3. If strFindUnmatchedDates = 0 Then
  4. MsgBox ("Darn!")
  5. Else
  6.  
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!
Feb 15 '08 #18

Dököll
Expert 100+
P: 2,364
Heiya puppydogbuddy!

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

Expand|Select|Wrap|Line Numbers
  1. Dim strFindUnmatchedDates As Long
  2. strFindUnmatchedDates = InStr(1, Me.Items, " " & Me.TodayDate & " ", vbTextCompare)
  3. If strFindUnmatchedDates = 0 Then
  4. MsgBox ("Darn!")
  5. Else
  6.  
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:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strFindUnmatchedDates As Long
  3. strFindUnmatchedDates = InStr(1, " " & Me.StatusDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
  4. If strFindUnmatchedDates = 0 Then
  5. MsgBox ("Darn!")
  6. ElseIf strFindUnmatchedDates <> 0 Then
  7. MsgBox ("Found!")
  8.  
  9.  
See you soon;-)
Feb 15 '08 #19

Dököll
Expert 100+
P: 2,364
Nevermind!!!

I did not do it right:

Expand|Select|Wrap|Line Numbers
  1.  
  2. Dim strFindUnmatchedDates As Long
  3. strFindUnmatchedDates = InStr(1, " " & Me.StatusDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
  4. If strFindUnmatchedDates = 0 Then
  5. MsgBox ("Darn!")
  6. ElseIf strFindUnmatchedDates <> 0 Then
  7. MsgBox ("Found!")
  8.  
  9. See you soon;-)
Expand|Select|Wrap|Line Numbers
  1.  
  2.  
Funny thing:

Expand|Select|Wrap|Line Numbers
  1. Dim strFindUnmatchedDates As Long
  2. strFindUnmatchedDates = InStrB(1, " " & Me.cboContactDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
  3. If strFindUnmatchedDates = 0 Then
  4. MsgBox ("Oh oh!")
  5. Else
  6. MsgBox ("YesSiree!")
  7. Me.Items.SetFocus
  8.  
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!
Feb 19 '08 #20

Dököll
Expert 100+
P: 2,364
[/code]Funny thing:

Expand|Select|Wrap|Line Numbers
  1. Dim strFindUnmatchedDates As Long
  2. strFindUnmatchedDates = InStrB(1, " " & Me.cboContactDate.Value & " ", " " & Me.Items.Value & " ", vbTextCompare)
  3. If strFindUnmatchedDates = 0 Then
  4. MsgBox ("Oh oh!")
  5. Else
  6. MsgBox ("YesSiree!")
  7. Me.Items.SetFocus
  8.  
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
Feb 19 '08 #21

Dököll
Expert 100+
P: 2,364
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
Apr 18 '08 #22

puppydogbuddy
Expert 100+
P: 1,923
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
Apr 19 '08 #23

Dököll
Expert 100+
P: 2,364
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
Apr 26 '08 #24

Post your reply

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