Contact date less than Open date: MS Access 2003

**puppydogbuddy** · Jan 10 '08, 05:20 PM

Originally posted by 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:

[CODE=VB]
ElseIf Me.TodayDate.Va lue > 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.cboContactDa te.SetFocus
Me.Phoned = False 'report false because user must add contact info...
Me.Phoned.Visib le = True 'report true because user must add contact info...

[/CODE]

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** · Jan 10 '08, 07:55 PM

Originally posted by puppydogbuddy

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** · Jan 10 '08, 08:13 PM

Originally posted by Dököll

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** · Jan 10 '08, 08:47 PM

Originally posted by puppydogbuddy

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** · Jan 10 '08, 09:06 PM

Originally posted by 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:-)

[CODE=VB]

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.cboContactDa te.SetFocus
Me.Phoned = False 'report false because user must add contact info...
Me.Phoned.Visib le = True 'report true because user must add contact info...

[/CODE]

See you around:-)

**puppydogbuddy** · Jan 10 '08, 09:08 PM

Originally posted by 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!

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** · Jan 18 '08, 01:38 AM

Originally posted by puppydogbuddy

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** · Jan 29 '08, 08:25 PM

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:

[CODE=VB]

If Len([Comment].Value) < 255 And Me.ContactPerso n.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: me@me.com" 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...

[/CODE]

Your input is appreciated;-)

**puppydogbuddy** · Jan 29 '08, 11:09 PM

Originally posted by 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:

[CODE=VB]

If Len([Comment].Value) < 255 And Me.ContactPerso n.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: me@me.com" 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...

[/CODE]

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.ContactPerso n.Value = "Data Central: 000-000-0000" Then do not allow save if true.

To this:
If Len(Trim([Comment])) < 255 And Me!ContactPerso n.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** · Jan 30 '08, 06:08 PM

Originally posted by puppydogbuddy

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.ContactPerso n.Value = "Data Central: 000-000-0000" Then do not allow save if true.

To this:
If Len(Trim([Comment])) < 255 And Me!ContactPerso n.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** · Jan 30 '08, 06:24 PM

Originally posted by 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:

[CODE=VB]

txtZReadMyChars = Len([Comment].Value)

[/CODE]

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** · Jan 30 '08, 08:59 PM

Originally posted by 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:

[CODE=VB]

txtZReadMyChars = Len([Comment].Value)

[/CODE]

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** · Jan 31 '08, 04:15 AM

Originally posted by puppydogbuddy

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** · Feb 14 '08, 10:00 PM

Originally posted by 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:

[CODE=VB]

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"

[/CODE]

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

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