[Gauze]

i have a database that has first visit date and last visit date. the last visit date only shows a date if the person has been here more than once. The other ones are left blank. Is there a function I can use to put the first date if the only date in that field that was left blank?

[Advertisements]

Hello Gauze,

Welcome to GTG...

Just a couple of questions for you (if you don't mind).

How is this data being entered? Via a form or directly into the table?

So, right now the "Last Visit Date" field can be populated with the "First Visit Date" being empty?

If so, you would prefer that the "Last Visit Date" field cannot have a value unless their is a value in the "First Visit Date" field first?


Sincerely,

dm

[dm27]

Hello Gauze,

Welcome to GTG...

Just a couple of questions for you (if you don't mind).

How is this data being entered? Via a form or directly into the table?

So, right now the "Last Visit Date" field can be populated with the "First Visit Date" being empty?

If so, you would prefer that the "Last Visit Date" field cannot have a value unless their is a value in the "First Visit Date" field first?


Sincerely,

dm

[Gauze]

The data is entered directly into the table.
Data can be entered into the last visit date without data in the first visit date.
I'd like the last visit date field to take the value of the first visit date field if the client only had one visit.
all the clients in the database have at least one visit recorded. This is recorded in first visit date. If that is the only visit they have I would like that date to carry over to the last visit date if possible.

Edited by Gauze, 03 March 2010 - 04:23 PM.

[dm27]

Hello Gauze,

Interesting....just offering some other suggestions for you:

Setting the "First Visit Date" field as a "Required" entry, would prevent the "Last Visit Date" from being entered by itself and would ensure a valid entry would always be in the "First Visit Date" field.

Of course, I'm not sure how many records changing that setting would affect...

You could use the text below in the Validation Rule of the Table Properties window to ensure a Last Visit didn't occur before a First Visit.

• [LastVisit]>=[FirstVisit]

Under what conditions would you want the Last Visit Date to equal the First Visit Date?

Depending on your needs, I believe it would require to enter the data through a Form and use code to run through any conditions.

I'll be glad to continue working through this with you and I hope this helps.


david

[Gauze]

Thanks!
that validation rule seems to be useful.
the fields I have are first visit and last visit.
the only way i'd like to have to enter data in the last visit field is if the client actually visits again.
Otherwise I would just like the data from the first visit to carry over, hence being the first and last visit.

[dm27]

Hi Gauze,

This was an interesting task...I've got to admit I tried using validation and setting default values at the table and record level but was not successful. As I suspected this was best done through a form and I had to search for the proper code to handle it. If your comfortable creating a form, then follow the below steps and give it a try:

You may want to try this with a copy of your existing table first.

• Create the form based on the table with your data items. Select the table and under the Create tab of the Ribbon, click the Form button and Access should automatically create the form for you.
• Now that you have the form open go to Design view and then open up the Form Property Sheet
• Under the Selection Type box, make sure your First Date field is selected. Then choose the All tab.
• Find the On Lost Focus line and double click the (...) to the right.
• In the code box that appears, copy and paste the below code between the Sub and End Sub lines that will pre-fill in the code window:
  

  'Code originally written by Joe4 on 10/29/2008 at http://www.mrexcel.com/forum/showthread.php?t=349959
  'Obtained 3/3/10
  
  'This will set the default last visit date to the first visit.
  	
  	If IsNull(Me.LastDate) Then Me.LastDate = Me.FirstDate

• NOTE: Make sure you change the name of the field names above to match your actual field names.
• Click the Save button and then close the code window and the VBA window.
• Finally Save your form

So, after all that, it's time to test your form. Go to a new record and enter a First Visit value. Once you hit the Tab or Enter key, your Last Visit field will default to the First Visit value.

You can always update the Last Visit date right then at a later time.

Let me know if this works for you and have good evening.

Best regards,

dm


(edit: Hit Post Reply in error the first time)

Edited by dm27, 03 March 2010 - 07:42 PM.

[Gauze]

if i have spaces in my field names should i use spaces or underscores in the code?

[dm27]

Hi Gauze,

My recommendation would be to change the field names by removing the spaces (my preference) or add an underscore to them. However if you have a lot queries tied to the table, then you may want to just leave them be.

Option 1: So if you have a field name of First Date, I would change it to FirstDate or First_Date


Option 2: If you wish to leave the spaces in the field name then the proper syntax would be as below:(Note the square brackets added around the field names.)

If IsNull(Me.[last date]) Then Me.[last date] = Me.[first date]

Hope this helps,

[Gauze]

Alright I started over and it worked!
Awesome thanks a lot I'll try that at work tomorrow, on a copy of course.

[dm27]

Glad to help you...

At your convenience, let me know how this solution worked out for you.


dm

[Gauze]

will do. I'm at home now so when I work on this tomorrow I will try it. I was just practicing here at home.

[Gauze]

this seems to only work with a new database with no data in it yet. When i tried to add this code to the property sheet of the existing form it doesn't work.

[dm27]

Hello Gauze,

This might depend on what data the existing records contain. If you have records that already have a date entered in the Last Visit field then the code would not apply, since the code checks to see if that field is empty.

After you entered the code, did it function correctly when you entered a New record?

How many records in your table?

Sincerely,

dm

[Gauze]

there is a lot of records. I got the code working with new records but existing ones it wouldn't work. I don't need this to work anymore though.

[dm27]

Hi Gauze,

Sorry for my last post, I was feeling quite tired and didn't put as much thought into it as I should have.

If you want to clean up your existing data (since you mentioned that you have some records with a Last Visit Date but no First Visit date) you could run an Update query and then all your records would be consistant.

Let me know if you are interested in persuing that, otherwise I hope I was able to offer some useful assistance.

Enjoy the rest of your day!

Best regards,

dm